Create a baseline for sql_id


-- Get plan active by snapshots

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','2rta7mazkhc6g')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1 desc , 2, 3 desc
/

-- Create SQL Tuning Set (STS)


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

-- 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=>18724, end_snap=>18729,basic_filter=>'sql_id = ''2rta7mazkhc6g''',attribute_list=>'ALL')
              ) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS:2rta7mazkhc6g', populate_cursor=>cur);
  CLOSE cur;
END;
/
 

-- 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 => 'STS:2rta7mazkhc6g')
             );

-- List out the Baselines to see what's there


SELECT * FROM dba_sql_plan_baselines order by 2;

-- Sequence to drop baseline (optional)

SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN,
         ENABLED, ACCEPTED
  FROM   DBA_SQL_PLAN_BASELINES order by 1;

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


-- 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 => 'STS:2rta7mazkhc6g',
    basic_filter=>'plan_hash_value = ''3781717410'''
    );
END;
/


-- List out the Baselines

SELECT * FROM dba_sql_plan_baselines ;


-- Remove sql_id from cursor cache

select address,hash_value
from v$sql where sql_id = '2rta7mazkhc6g';

exec DBMS_SHARED_POOL.PURGE( '070001069407FD48,3207082191' , 'C' );

-- Drop SQL Tunning Set


BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'STS:2rta7mazkhc6g');
END;

Comments: Leave Comment

* The email will not be published on the website.