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;

 

 

[Post Views: 754]

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

2 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!

Leave a Reply

Required fields are marked *