Oracle SQL Plan baseline

Use plan baseline to stabilize query plan.

I use the following two links as reference.
http://rnm1978.wordpress.com/2011/06/28/oracle-11g-how-to-force-a-sql_id-to-use-a-plan_hash_value-using-sql-baselines/

http://kerryosborne.oracle-guy.com/2009/04/oracle-11g-sql-plan-management-sql-plan-baselines/

step 1: find the problem query which use bad query plan rather than the good plan.

step 2: find the snapshot id which has has the good plan plan for this sql_id.

VARIABLE cnt NUMBER
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( -
                    sql_id => '0yv4z9c24ywm6');

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','0yv4z9c24ywm6')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3

    SNAP_ID   NODE BEGIN_INTERVAL_TIME            SQL_ID        PLAN_HASH_VALUE        EXECS    AVG_ETIME        AVG_LIO
---------- ------ ------------------------------ ------------- --------------- ------------ ------------ --------------
       101      1 07-NOV-14 03.00.33.289 AM      0yv4z9c24ywm6      4002210582            1      120.021      678,900.0
       140      1 08-NOV-14 06.00.04.247 PM      0yv4z9c24ywm6                            1      261.454    1,461,532.0
       199      1 11-NOV-14 05.00.34.552 AM      0yv4z9c24ywm6                            1      228.954    1,465,972.0
       229      1 12-NOV-14 11.00.04.839 AM      0yv4z9c24ywm6                            1      109.027      688,335.0
       246      1 13-NOV-14 04.00.03.837 AM      0yv4z9c24ywm6                            1      134.105      690,626.0

step 3: create a tuning set.

BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'MySTS01');
END;

-- Create SQL Tuning Set (STS)
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'MySTS01',
    description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;

step 4:

populate the tning set with the snapshot, try to use a broader range of snapshot id from step 2
-- Populate STS from AWR, using a time duration when the desired plan was used
--  List out snapshot times using :
SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
FROM dba_hist_snapshot ORDER BY END_INTERVAL_TIME DESC;
--  Specify the sql_id in the basic_filter (other predicates are available, see documentation)
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM TABLE(
       dbms_sqltune.select_workload_repository(begin_snap=>92440, end_snap=>92460,basic_filter=>'sql_id = ''0mdxgv8ks2pbc''',attribute_list=>'ALL')) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'MySTS01', populate_cursor=>cur);
  CLOSE cur;
END;
/

step 5: verify that the tuning set has the sql_id
-- List out SQL Tuning Set contents to check we got what we wanted
SELECT
  first_load_time          ,
  executions as execs              ,
  parsing_schema_name      ,
  elapsed_time  / 1000000 as elapsed_time_secs  ,
  cpu_time / 1000000 as cpu_time_secs           ,
  buffer_gets              ,
  disk_reads               ,
  direct_writes            ,
  rows_processed           ,
  fetches                  ,
  optimizer_cost           ,
  sql_plan                ,
  plan_hash_value          ,
  sql_id                   ,
  sql_text
   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'MySTS01')
             );

step 6:  verify that the good plan_hash_value is in the tuning set.

select sql_id,plan_hash_value FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'MySTS01'));
12:09:12 RMS81> select sql_id,plan_hash_value FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'MySTS01'));

SQL_ID        PLAN_HASH_VALUE
------------- ---------------
0yv4z9c24ywm6      4002210582

step 7: load the plan.

-- List out the Baselines to see what's there
SELECT * FROM dba_sql_plan_baselines ;

-- Load desired plan from STS as SQL Plan Baseline
-- Filter explicitly for the plan_hash_value here if you want
DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'MySTS01',
    basic_filter=>'plan_hash_value = ''87655762'''
    );
END;
/

step 8: fix the plan.

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle  => NULL,
plan_name   => ‘SQL_PLAN_8f56b2ffswhrpb8e5e62d’,
attribute_name => ‘fixed’,
attribute_value => ‘YES’);
END;
/

step 9: verify the plan is fixed.

12:24:31 RMS81>  exec print_table('select * from dba_sql_plan_baselines where sql_handle=''SQL_8714cb139d8e42f5''')
SIGNATURE                     : 6340198085419112112
SQL_HANDLE                    : SQL_57fce8911f0496b0
SQL_TEXT                      : INSERT /*+ APPEND */ INTO WOSTAGE.WO_STAGING_REV_SHARE SELECT B.FISCAL_PERIOD , B.STATION_INT , B.STATION_CALL_LETTERS
 ,
B.CHANNEL_INT , B.STATION_CALL_LETTERS||' '||A.REPORTING_NAME , B. REVTYPE_CODE
PLAN_NAME                     : SQL_PLAN_5gz78k4gh95ph66848240
CREATOR                       : CTVDBA
ORIGIN                        : MANUAL-LOAD
PARSING_SCHEMA_NAME           : WOSTAGE
DESCRIPTION                   :
VERSION                       : 11.2.0.3.0
CREATED                       : 15-NOV-14 12.10.10.000000 PM
LAST_MODIFIED                 : 15-NOV-14 12.24.31.000000 PM
LAST_EXECUTED                 :
LAST_VERIFIED                 :
ENABLED                       : YES
ACCEPTED                      : YES
FIXED                         : YES
REPRODUCED                    : YES
AUTOPURGE                     : YES
OPTIMIZER_COST                : 1934711
MODULE                        : JDBC Thin Client
ACTION                        :
EXECUTIONS                    : 1
ELAPSED_TIME                  : 120021019
CPU_TIME                      : 15880520
BUFFER_GETS                   : 678900
DISK_READS                    : 721160
DIRECT_WRITES                 : 55781
ROWS_PROCESSED                : 9196
FETCHES                       : 0
END_OF_FETCH_COUNT            : 1
-----------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
12:24:47 RMS81>

The following scripts can be used to migrate plan baseline.

DECLARE
  v_dropped_plans number;
BEGIN
  v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
     sql_handle => 'SQL_0cf31d9f7b33b119'
);
  DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
END;
/

    DECLARE
       i number;
    BEGIN
       DBMS_SPM.create_stgtab_baseline
       ( table_name  => 'XXFND_SQL_PLAN_BASELINES'
       , table_owner => 'WOSTAGE'
       ) ;
    END;

 DECLARE
       l_ret_value NUMBER;
   BEGIN
       l_ret_value := DBMS_SPM.pack_stgtab_baseline
                      ( table_name  => 'XXFND_SQL_PLAN_BASELINES'
                      , table_owner => 'WOSTAGE'
                      , enabled     => 'YES'
                      , accepted    => 'YES'
                      ) ;
   END;

   declare
      i number ;
   begin
      i := dbms_spm.unpack_stgtab_baseline
           ( table_name => 'xxfnd_sql_plan_baselines'  
           , table_owner => 'WOSTAGE'
           ) ;
      dbms_output.put_line('Plans Loaded : ' || i);
   end;