Oracle AWR

Cercare piani di esecuzione storici

--
select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('&1'));

Cercare sqlid in snapshot

set pagesize 200
set linesize 200
col BEGIN_INTERVAL_TIME for a25
SELECT SS.SNAP_ID,
--     SS.INSTANCE_NUMBER,
    BEGIN_INTERVAL_TIME,
--     SQL_ID,
    PLAN_HASH_VALUE,OPTIMIZER_COST,
    DISK_READS_TOTAL,
    BUFFER_GETS_TOTAL,
--     ROWS_PROCESSED_TOTAL,
    CPU_TIME_TOTAL,
    ELAPSED_TIME_TOTAL,
    IOWAIT_TOTAL,
    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 = '&1'
    AND SS.SNAP_ID = S.SNAP_ID
    AND SS.INSTANCE_NUMBER = S.INSTANCE_NUMBER
    AND EXECUTIONS_DELTA > 0
ORDER BY 1, 2, 3;

forzare piano di esecuzione

--
-- https://rajiboracle.blogspot.com/2017/05/how-to-load-sql-plan-baseline-from-awr.html
-- sql_id hash_plan_value snap_begin snap_end

exec DBMS_SQLTUNE.DROP_SQLSET (sqlset_name => 'STS_&1');
exec DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'STS_&1', description => 'sts from awr emilio &1 &2 &3 &4');

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM TABLE(
       dbms_sqltune.select_workload_repository(begin_snap=>&3, end_snap=>&4,basic_filter=>'sql_id = ''&1''',attribute_list=>'ALL')
              ) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_&1', populate_cursor=>cur);
  CLOSE cur;
END;
/

DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'STS_&1', 
    basic_filter=>'plan_hash_value = ''&2''',
    sqlset_owner => 'SYS',
    fixed => 'YES',
    enabled => 'YES'
    );
END;
/