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