Create a Tuning Set with All Plan Hash Values from All AWR Snaps for a Single SQL ID
--
-- Create a Tuning Set with All Plan Hash Values from All AWR Snaps for a Single SQL ID
-- 
 
exec DBMS_SQLTUNE.CREATE_SQLSET('STS_8d65f5452jkjr');
 
declare
  baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
  bid NUMBER;
  eid NUMBER;
 
begin
 
 select min(snap_id) into bid from dba_hist_snapshot;
 select max(snap_id) into eid from dba_hist_snapshot;
 open baseline_ref_cursor for
   select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(bid, eid,
  'sql_id='||CHR(39)||'8d65f5452jkjr'||CHR(39)||'',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
 
DBMS_SQLTUNE.LOAD_SQLSET('STS_8d65f5452jkjr', baseline_ref_cursor);
 
end;
/

Published 1st November 2021

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License