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;

• Author
• Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Latest posts by Brijesh Gogia (see all)

• Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
• Details of running concurrent requests in Oracle EBS - May 26, 2020
• Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020