Create SQL Baseline for SQL_ID

Sometime DBAs need to create SQL Baseline for particular SQL_ID for plan stabilization

1) VERIFY CURRENTLY EXISTING SQL PLAN BASELINES

select * from dba_sql_plan_baselines;

2) CREATE ‘SQL TUNING SET’

A SQL tuning set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking. You can load SQL statements into a SQL tuning set from different SQL sources, such as AWR, the shared SQL area, or customized SQL provided by the user. An STS includes:

– A set of SQL statements
– Associated execution context, such as user schema, application module name and action, list of bind values, and the cursor compilation environment
– Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type.
– Associated execution plans and row source statistics for each SQL statement (optional).

SQL> begin
 DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => 'atfz4c63at1k1_STS01', DESCRIPTION => 'TEST SQL TUNE SET');
 END;
 /

PL/SQL procedure successfully completed.

3) VERIFY SQL MONITORING IS ENABLED FOR SQL STATEMENTS

SQL> SELECT DBMS_SQLTUNE.report_sql_monitor(type => 'TEXT', report_level=>'ALL', SQL_ID=>'atfz4c63at1k1') AS REPORT FROM DUAL;

REPORT
 --------------------------------------------------------------------------------
 SQL Monitoring Report

4) LOCATE AWR SNAPSHOT REQUIRED TO POPULATE THE ‘SQL TUNING SET’

select snap_id,begin_interval_time, end_interval_time from dba_hist_snapshot order by end_interval_time desc;

5) POPULATE ‘SQL TUNING SET’

A) FROM AWR

DECLARE
 CUR SYS_REFCURSOR;
 BEGIN
 OPEN CUR FOR
 SELECT VALUE(P) FROM TABLE ( DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(BEGIN_SNAP=>1939, END_SNAP=>1940, BASIC_FILTER=> 'sql_id = ''atfz4c63at1k1''', ATTRIBUTE_LIST=>'ALL' )) p;
 DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME=> 'atfz4c63at1k1_STS01', POPULATE_CURSOR=>CUR);
 CLOSE CUR;
 END;
 /

*** OR **

B) FROM SHARED SQL AREA

DECLARE
 CUR SYS_REFCURSOR;
 BEGIN
 OPEN CUR FOR
 SELECT VALUE(P) FROM TABLE (DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''atfz4c63at1k1''')) p;
 DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME=> 'atfz4c63at1k1_STS01', POPULATE_CURSOR=>CUR);
 CLOSE CUR;
 END;
 /

6) LIST OUT SQL TUNING SET TO VERIFY CONTENT

SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(SQLSET_NAME => 'atfz4c63at1k1_STS01'));

7) LOAD DESIRED PLAN FROM ‘SQL TUNING SET’ AS SQL PLAN BASELINE

DECLARE
 MY_PLANS PLS_INTEGER;
 BEGIN
 MY_PLANS := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(SQLSET_NAME=>'atfz4c63at1k1_STS01', BASIC_FILTER=> 'PLAN_HASH_VALUE= ''3811430562''' );
 END;
 /

8) VERIFY IF SQL PLAN BASELINE GOT CREATED SUCCESSFULLY

select * from dba_sql_plan_baselines;