Dear Readers,
In this article, we will see the Transport Plan from One Database to Another Database in Oracle .
Transport Good Execution Plan(Hash Plan) from one database to another database
Requirement:
ETL was taking longer period of time in Prod database where as in UAT it was completing within 25 minutes.
Optimizer also could not help to recommend a better plan for particular sql id so we found an approach to transport the good execution plan from UAT to Prod.
1. Create Empty Tuning Set
|
1 2 3 4 5 6 |
BEGIN DBMS_SQLTUNE.CREATE_SQLSET ( sqlset_name => 'SQLT_WKLD_STS', description => 'STS to store SQL from the private SQL area' ); END; |
2. Load the SQL Information for the SQL ID into this Tuning Set
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN OPEN c_sqlarea_cursor FOR SELECT VALUE(p) FROM TABLE( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( ' sql_id = ''2t8gdjy0pbs57'' AND plan_hash_value = ''618955923'' ') ) p; -- load the tuning set DBMS_SQLTUNE.LOAD_SQLSET ( sqlset_name => 'SQLT_WKLD_STS' , populate_cursor => c_sqlarea_cursor ); END; / |
3. Display the content of Sql Tuning set
|
1 2 3 4 5 6 7 |
SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT, ELAPSED_TIME AS "ELAPSED", BUFFER_GETS FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_WKLD_STS' ) ); SQL_ID SCH SQL_TEXT ELAPSED BUFFER_GETS ------------- ------------------------- -------------------------------------------------------------------------------- ---------- ----------- 2t8gdjy0pbs57 TESTUSER INSERT /*+ APPEND */ INTO W_L_T.PROJECT as SELECT….. 24844903 2577896 |
|
1 2 3 4 5 |
SELECT sql_id, parsing_schema_name as "SCH", sql_text,buffer_gets as "B_GETS",disk_reads, ROUND(disk_reads/buffer_gets*100,2) "%_DISK" FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_WKLD_STS', '(disk_reads/buffer_gets) >= 0.50' ) ); SQL_ID SCH SQL_TEXT B_GETS DISK_READS %_DISK ------------- ------------------------- -------------------------------------------------------------------------------- ---------- ---------- ---------- 2t8gdjy0pbs57 TESTUSER INSERT /*+ APPEND */ INTO W_L_T.PROJECT as SELECT….. 2577896 2435837 94.49 |
4. Create a Staging table to hold the exported SQL Tuning set
|
1 2 3 4 |
BEGIN DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (table_name => 'MY_TUNING_SET', schema_name => 'PDBADMIN'); END; / |
5. Load the SQL Tuning Set information to the Staging Table
|
1 2 3 4 5 6 7 8 9 |
BEGIN DBMS_SQLTUNE.PACK_STGTAB_SQLSET ( sqlset_name => 'SQLT_WKLD_STS', sqlset_owner => 'PDBADMIN', staging_table_name => 'MY_TUNING_SET', staging_schema_owner => 'PDBADMIN' ); END; / |
6. Export of The table
|
1 |
source ~/.bash_profile; nohup expdp userid="USERNAME/PASSWORD@lnx01.oraclevcn.com:1521/PDB1S001.oraclevcn.com" parfile/u01/dumps/mytunigset_table.par & |
7. Import of the Staging table
|
1 |
source ~/.bash_profile; nohup impdp userid="USERNAME/PASSWORD@lnx02.oraclevcn.com:1521/PDB1P001.oraclevcn.com" parfile/u01/dumps/mytunigset_table.par & |
8. Unpack the SQL Tuning set from the staging table to the destination server
|
1 2 3 4 5 6 7 8 9 |
BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET ( sqlset_name => '%' , sqlset_owner => 'PDBADMIN' , replace => true , staging_table_name => 'MY_TUNING_SET' , staging_schema_owner => 'PDBADMIN'); END; / |
9. Load the plan from SQL Tuning Set to SQL Plan Baseline
|
1 2 3 4 5 |
VARIABLE v_plan_cnt NUMBER EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( - sqlset_name => 'SQLT_WKLD_STS', - sqlset_owner => 'PDBADMIN', - basic_filter => 'sql_id = ''2t8gdjy0pbs57'' AND plan_hash_value = 618955923' ); |
10. Flush the two SQLs from the shared pool, so that the optimizer will pick the new plan
|
1 2 |
select 'exec DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '||HASH_VALUE||''', ''C'');' from V$SQLAREA where SQL_ID in ('2t8gdjy0pbs57'); exec SYS.DBMS_SHARED_POOL.PURGE ('0000001006B396C8, 2113289046', 'C'); |
11. To verify Baseline created in the database
|
1 |
select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines; |
12. Drop SQL tunung Set from Source
|
1 2 3 |
BEGIN DBMS_SQLTUNE.drop_sqlset (sqlset_name => '2t8gdjy0pbs57_STS'); END; |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Note: Please test scripts in Non Prod before trying in Production.
Loading...
