Skip to content

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;

 

 

Brijesh Gogia

7 Comments

  1. Mark Mark

    No use if we can not copy …looking for another blog ….i guess u copied from Oracle too.

    • Brijesh Gogia Brijesh Gogia

      Thank you for pointing out. All article where there are re-usable scripts have been white-listed so that scripts can be copied out. Somehow this article was missed out. Added it now to the list and content can be copied now!

  2. prashant prashant

    This really work for me.

    Thanks Brijesh.

  3. Pawel Adamski Pawel Adamski

    Very good article which explains the matter in simple words with examples. Thanks for that.

  4. Mandar P. Mandar P.

    Hi Brijesh ,

    For the step 4 : Locate Snap ID , which SNAP ID’s we are talking here ?
    Without passing anything in where condition how we have decided the SNAP ID are 1939 and 1940 ?

  5. Anonymous Anonymous

    you need to put some option to enable or fixed

    ENABLED=>’YES’,FIXED=>’NO’

Leave a Reply