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

Creating sql baseline to fix query with better execution plan

            

Hello everyone, hope you all doing good. It’s been too long I haven’t updated new post on my blog due to my busy schedule.

So here I will be sharing very interesting post of creating sql baseline and force query to use better execution plan (plan hash value).

Most of us have come across this scenario where query which was running fine till yesterday now suddenly running long.

So there could be many reasons why query performance has been changed suddenly, so one of the reason is change in query plan.

In such situation we need to find out best execution plan (Plan_hash_value) and force query to use that plan.

Below are the steps to create and fix bad query by creating sql baseline.

STEP 1: GENERATE ALL PREVIOUS HISTORY RUN DETAILS OF SQL_ID FROM AWR

break off sdate

set lines 2000

set linesize 2000

col SDATE format a10

col STIME format a10

select to_char(begin_interval_time,'YYYY/MM/DD') SDATE,to_char(begin_interval_time,'HH24:MI')  STIME,s.snap_id,

        sql_id, plan_hash_value PLAN,

        ROUND(elapsed_time_delta/1000000,2) ET_SECS,

        nvl(executions_delta,0) execs,

        ROUND((elapsed_time_delta/decode(executions_delta,null,1,0,1,executions_delta))/1000000,2) ET_PER_EXEC,

        ROUND((buffer_gets_delta/decode(executions_delta,null,1,0,1,executions_delta)), 2) avg_lio,

        ROUND((CPU_TIME_DELTA/decode(executions_delta,null,1,0,1,executions_delta))/1000, 2) avg_cpu_ms,

        ROUND((IOWAIT_DELTA/decode(executions_delta,null,1,0,1,executions_delta))/1000, 2) avg_iow_ms,

        ROUND((DISK_READS_DELTA/decode(executions_delta,null,1,0,1,executions_delta)), 2) avg_pio,

        ROWS_PROCESSED_DELTA num_rows

from DBA_HIST_SQLSTAT S,  DBA_HIST_SNAPSHOT SS

where s.sql_id = '&sql_id'

and ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number

order by sdate,stime;

Enter value for sql_id: 7hgwdax4mn20v

SDATE      STIME         SNAP_ID SQL_ID              PLAN    ET_SECS        EXECS ET_PER_EXEC        AVG_LIO AVG_CPU_MS AVG_IOW_MS    AVG_PIO   NUM_ROWS

---------- ---------- ---------- ------------- ---------- ---------- ------------ ----------- -------------- ---------- ---------- ---------- ----------

2016/09/13 11:00           23468 7hgwdax4mn20v 2844841640    1421.36            1     1421.36        4,301.0  152720.78     312.25        269        336

2016/09/13 15:00           23472 7hgwdax4mn20v 2844841640    1070.12            1     1070.12        4,291.0  126987.69     348.34        270        336

2016/09/13 17:00           23474 7hgwdax4mn20v 2844841640    1211.98            1     1211.98        4,374.0  183347.13     157.32        270        336

2016/09/13 18:00           23475 7hgwdax4mn20v 2844841640     596.91            0      596.91          116.0   45123.14          0          0          0

2016/09/14 01:00           23482 7hgwdax4mn20v 2844841640    1038.41            1     1038.41        4,309.0  123455.23     413.94        272        336

2016/09/14 02:00           23483 7hgwdax4mn20v 1355798266     193.82            1      193.82        2,748.0   76799.32      28.95         16        168

2016/09/14 03:00           23484 7hgwdax4mn20v 2844841640        .04            0         .04             .0         38          0          0          0

2016/09/14 03:00           23484 7hgwdax4mn20v          0        .04            0         .04             .0      38.99          0          0          0

2016/09/14 10:00           23491 7hgwdax4mn20v 2844841640     626.06            0      626.06           64.0   54658.69          0          0          0

/*In this scenario sql_id=7hgwdax4mn20v and plan_hash_value for good plan that we want to force is 1355798266.*/

Follow the below steps to create sql baseline for sql_id

 STEP 2: DROP SQL TUNING SET (STS) IF EXISTS

BEGIN

  DBMS_SQLTUNE.DROP_SQLSET(

    sqlset_name => 'SQL_FOR_7hgwdax4mn20v');

END;

STEP 3: CREATE SQL TUNING SET

BEGIN

  DBMS_SQLTUNE.create_sqlset (

    sqlset_name  => 'SQL_FOR_7hgwdax4mn20v',

    description  => 'SQL tuning set for 7hgwdax4mn20v');

END;

/

/* Populate STS from AWR by specifying snapshot for desired plan which we found using above query.

In this scenario snap id's are 23483 and 23484 and change plan_hash_value accordingly.*/

DECLARE

  l_cursor  DBMS_SQLTUNE.sqlset_cursor;

BEGIN

  OPEN l_cursor FOR

    SELECT VALUE(p)

    FROM   TABLE (DBMS_SQLTUNE.select_workload_repository (

                    23483,  -- begin_snap

                    23484,  -- end_snap

                    q'<sql_id in ('7hgwdax4mn20v') and plan_hash_value in (1355798266)>',  -- basic_filter 

                    NULL, -- object_filter

                    NULL, -- ranking_measure1

                    NULL, -- ranking_measure2

                    NULL, -- ranking_measure3

                    NULL, -- result_percentage

                    100)   -- result_limit

                  ) p;

  DBMS_SQLTUNE.load_sqlset (

    sqlset_name     => 'SQL_FOR_7hgwdax4mn20v',

    populate_cursor => l_cursor);

END;

/

STEP 4: CHECK SQL SET DETAILS 

column text format a20

select sqlset_name, sqlset_owner, sqlset_id, sql_id,substr(sql_text,1,20) text,elapsed_time,buffer_gets,

parsing_schema_name, plan_hash_value, bind_data from dba_sqlset_statements where sqlset_name ='SQL_FOR_7hgwdax4mn20v';

STEP 5: LOAD THE DESIRED PLAN FROM STS AS SQL PLAN BASELINE

DECLARE

  L_PLANS_LOADED  PLS_INTEGER;

BEGIN

  L_PLANS_LOADED := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(

    SQLSET_NAME => 'SQL_FOR_7hgwdax4mn20v');

END;

STEP 6: CHECK SQL PLAN BASELINE INFORMATION

SELECT sql_handle, plan_name,enabled,accepted,fixed FROM dba_sql_plan_baselines

WHERE signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID')

order by accepted,enabled;

STEP 7: ENABLE FIXED=YES

var pbsts varchar2(30);

exec :pbsts := dbms_spm.alter_sql_plan_baseline('SQL_64e76e773c55f1c4','SQL_PLAN_69tvffwy5bwf480a6275e','FIXED','YES');

STEP 8: Check the SQL baseline details

Use the plan name received from the STEP 6 output to get the baseline details

SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_69tvffwy5bwf480a6275e'));

STEP 9: PURGE OLD EXECUTION PLAN FROM SHARED POOL 

Find below two parameters required to purge specific sql from the shared pool.

select address||','||hash_value from gv$sqlarea where sql_id = '7hgwdax4mn20v';

ADDRESS||','||HASH_VALUE

----------------------------------------------------------------------------------------

000000058B4B7E40,1879818331

Now use the below command to purge sql from the shared pool.

exec sys.dbms_shared_pool.purge('000000058B4B7E40,1879818331','C',1);

                

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