Transport Good Execution Plan(Hash Plan) from one database to another database

1. Create Empty Tuning Set

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

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

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

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

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

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 The table

nohup expdp userid="USERNAME/PASSWORD@lnx01.oraclevcn.com:1521/PDB1S001.oraclevcn.com" parfile/u01/dumps/mytunigset_table.par &

7. Import of the Staging table

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

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

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

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 the Baseline created in the database

select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;

12. Drop SQL tuning Set from Source

BEGIN DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'SQLT_WKLD_STS'); END;

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011

Datapump FILESIZE parameter

FILESIZE :

• It specifies the maximum size of each dump file. If the size is reached for any member of the dump file set, that file is closed and an attempt is made to create a new file, if the file specification contains a substitution variable.
• You can set filesize parameter limits for the dumpfile size same as below,[FILESIZE=integer[B | K | M | G] where B is bytes, K is KB, M is MB and G is GB]
• The below command will create each dumpfile of size 50ok.If the export data is more than 500k then you specify dumpfile as wildcard “%U” to create multiple files.

[oracle@srv1 ~]$ expdp amit/oracle directory=test_dir dumpfile=amit_data%U.dmp logfile=amit_data.log schemas=amit filesize=500k Export: Release 18.0.0.0.0 - Production on Fri Aug 10 11:54:40 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Starting "AMIT"."SYS_EXPORT_SCHEMA_03": amit/******** directory=test_dir dumpfile=amit_data%U.dmp logfile=amit_data.log schemas=amit filesize=500k Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT . . exported "AMIT"."EMP" 873.1 KB 100000 rows . . exported "AMIT"."SYS_EXPORT_SCHEMA_01" 0 KB 0 rows . . exported "AMIT"."SYS_EXPORT_SCHEMA_02" 434.3 KB 1445 rows Master table "AMIT"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded ****************************************************************************** Dump file set for amit.SYS_EXPORT_SCHEMA_03 is: /u01/shared_datapump/amit_data01.dmp /u01/shared_datapump/amit_data02.dmp /u01/shared_datapump/amit_data03.dmp /u01/shared_datapump/amit_data04.dmp Job "amit"."SYS_EXPORT_SCHEMA_03" successfully completed at Fri Nov 10 11:55:21 2018 elapsed 0 00:00:41

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011