Creating sqlplan Baselines
Create sql tuning set
————————-
begin
DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => ‘&&sql_id’, DESCRIPTION => ‘SQL TUNE SET2’);
END;
/
——-SELECT DBMS_SQLTUNE.report_sql_monitor(type => ‘TEXT’, report_level=>’ALL’, SQL_ID=>’f2nydgpwc252s’) AS REPORT FROM DUAL;
— Run this in toad and pick latest range where the sql_id was running well
–select snap_id,begin_interval_time, end_interval_time from dba_hist_snapshot order by end_interval_time desc;
select min(snap_id),max(snap_id) from dba_hist_snapshot;
Load the tuning set
————————-
DECLARE
CUR SYS_REFCURSOR;
BEGIN
OPEN CUR FOR
SELECT VALUE(P) FROM TABLE ( DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(BEGIN_SNAP=>&&min_snap, END_SNAP=>&&max_snap, BASIC_FILTER=> ‘sql_id = ”&&sql_id”’, ATTRIBUTE_LIST=>’ALL’ )) p;
DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME=> ‘&&sql_id’, POPULATE_CURSOR=>CUR);
CLOSE CUR;
END;
/
——SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(SQLSET_NAME => ‘f2nydgpwc252s_TUNE’));
Load the plan hash for the sql tuning set
——————————————-
DECLARE
MY_PLANS PLS_INTEGER;
BEGIN
MY_PLANS := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(SQLSET_NAME=>’&&sql_id’, BASIC_FILTER=> ‘PLAN_HASH_VALUE= ”&&plan_hash_value”’ );
END;
/
Verification
—-select * from dba_sql_plan_baselines where created > sysdate -1;
