Create sql profile and move sql profile from one database to another(part 2)

By parwez on October 20, 2020
LinkedIn0

In part 1http://parwezexa.com/create-sql-profile-and-move-sql-profile-from-one-database-to-anotherpart-1/ , we have created a sql profile and demonstrated how the query was taking the effective plan path after using the sql profile .

In this part we will create a staging table and move the profile to other database , which will run the same query, will then try to import the sql profile in the target database to make sure it also uses the same profile .

  1. Create a staging table which will hold the sql profile information 
SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'PAWEZ_STAGE',schema_name=>'PARWEZ');

 PL/SQL procedure successfully completed.

2. Pack the staging table with the particular profile information , which we need to move to target database , in our case we will transfer the sql profile which we have created in part 1.

SQL> exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'PAWEZ_STAGE',profile_name=>'my_sql_profile',STAGING_SCHEMA_OWNER=>'PARWEZ');

 PL/SQL procedure successfully completed.

3. Verify the content of staging table we have created , is it populated with the correct information or not 

SQL> Select VERSION, SIGNATURE, SQL_HANDLE, OBJ_NAME, OBJ_TYPE, SQL_TEXT, CREATED, STATUS, OPTIMIZER_COST, MODULE, ACTION from PARWEZ.PAWEZ_STAGE;


VERSION  SIGNATURE SQL_HANDLE                     OBJ_NAME                       OBJ_TYPE                       SQL_TEXT                                                                         CREATED                             STATUS OPTIMIZER_COST MODULE                                                           ACTION
 
      4 5.1980E+17 SQL_0736b6584d755623           my_sql_profile                 SQL_PROFILE                    select /*+ no_index(test test_idx) */ * from test where id=1                     19-OCT-20 01.11.57.046226000 PM        129                                      4 5.1980E+17 SQL_0736b6584d755623           my_sql_profile                 SQL_PROFILE                    select /*+ no_index(test test_idx) */ * from test where id=1                     19-OCT-20 01.11.57.046226000 PM        129

4. Create necessary directory for export of the staging table and run expdp to export the staging table 

SQL> alter session set container=pdb1 ;
 Session altered.
 SQL>
 SQL>
 SQL> create directory PDB_EXPDIR as '/home/oracle' ;
 Directory created.


[oracle@mysqltest ~]$ expdp parwez/welcome1@pdb1 dumpfile=stage.dmp logfile=stage.log directory=PDB_EXPDIR tables=PARWEZ.PAWEZ_STAGE

Export: Release 19.0.0.0.0 - Production on Tue Oct 20 07:15:45 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 Starting "PARWEZ"."SYS_EXPORT_TABLE_01":  parwez/@pdb1 dumpfile=stage.dmp logfile=stage.log directory=PDB_EXPDIR tables=PARWEZ.PAWEZ_STAGE
 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
 Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
 Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
 Processing object type TABLE_EXPORT/TABLE/TABLE
 . . exported "PARWEZ"."PAWEZ_STAGE"                      41.64 KB       2 rows
 Master table "PARWEZ"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
 
 Dump file set for PARWEZ.SYS_EXPORT_TABLE_01 is:
   /home/oracle/stage.dmp
 Job "PARWEZ"."SYS_EXPORT_TABLE_01" successfully completed at Tue Oct 20 07:16:23 2020 elapsed 0 00:00:34

5. Create a target database (pdb2) and create all tables and index to simulate the same query for which the sql profile export we have taken in staging table 

Connect to target database 

SQL> conn parwez/welcome1@pdb2
 Connected.
 SQL>
 create table and index

 SQL> create table test (id number );
 Table created.
 SQL> declare
   2            begin
   3             for i in 1 .. 10000 loop
   4                 insert into test values(i);
   5                 commit;
   6             end loop;
   7            end;
   8  /
 PL/SQL procedure successfully completed.


 SQL> create index test_idx on test(id);
 Index created.


 SQL>  exec dbms_stats.gather_table_stats('','TEST');
 PL/SQL procedure successfully completed.

(Run the same query which we have ran with the help of sql profile in source ,here it can be seen since there is no profile applied it is taking full table scan (as per hint) )

SQL> set autotrace on
 Autotrace Enabled
 Shows the execution plan as well as statistics of the statement.
 SQL> select /*+ no_index(test test_idx) */ * from test where id=1;
         ID
      1
 Explain Plan
 PLAN_TABLE_OUTPUT
 Plan hash value: 1357081020

 
 | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 |   0 | SELECT STATEMENT  |      |     1 |     4 |     7   (0)| 00:00:01 |
 |*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     7   (0)| 00:00:01 |


 SQL> select * from dba_sql_profiles;
 no rows selected

6. Import the staging table in the target database (pdb2)

[oracle@mysqltest admin]$ impdp parwez/welcome1@pdb2 dumpfile=stage.dmp logfile=imp.log directory=PDB_EXPDIR tables=PARWEZ.PAWEZ_STAGE

 Import: Release 19.0.0.0.0 - Production on Tue Oct 20 07:20:29 2020
 Version 19.3.0.0.0
 Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 Master table "PARWEZ"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
 Starting "PARWEZ"."SYS_IMPORT_TABLE_01":  parwez/@pdb2 dumpfile=stage.dmp logfile=imp.log directory=PDB_EXPDIR tables=PARWEZ.PAWEZ_STAGE
 Processing object type TABLE_EXPORT/TABLE/TABLE
 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 . . imported "PARWEZ"."PAWEZ_STAGE"                      41.64 KB       2 rows
 Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
 Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
 Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
 Job "PARWEZ"."SYS_IMPORT_TABLE_01" successfully completed at Tue Oct 20 07:20:53 2020 elapsed 0 00:00:20

7. Unpack the staging table in the target database (pdb2)

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'PAWEZ_STAGE');

 PL/SQL procedure successfully completed.

After unpacking, Check if the profile is visible in target database


SQL> select NAME,SQL_TEXT,LAST_MODIFIED,STATUS from dba_sql_profiles ;
 NAME                                                      SQL_TEXT                                                                         LAST_MODIFIED                   STATUS
 
 my_sql_profile                                          select /*+ no_index(test test_idx) */ * from test where id=1                     20-OCT-20 07.28.14.000000000 AM ENABLED

Make sure the status should be enabled to make the sql profile work, if is not enabled, enable it.

Yes, we can see it is now present in target database

8. Now try to run the same sql which we had run in step 5 , which was resulting in full table scan the, lets see if it is taking the profile or not .

SQL> set autotrace on
 Autotrace Enabled
 Shows the execution plan as well as statistics of the statement.
 SQL> select /*+ no_index(test test_idx) */ * from test where id=1;
         ID
      1
 Explain Plan
 
 | Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
 |   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 |
 |*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |
 SQL profile "my_sql_profile" used for this statement ( it is taking the sql profile

 790 total views,  1 views today

LinkedIn0

Leave a Reply

Your email address will not be published. Required fields are marked *