How to Create an SQL Baseline for a Specific SQL_ID

Sometimes, DBAs need to create a SQL baseline for a particular SQL_ID to stabilize the execution plan. Here’s a step-by-step guide on how to do this:

1) Check Existing SQL Plan Baselines

To verify if any SQL plan baselines already exist:

SELECT * FROM dba_sql_plan_baselines;

2) Create a SQL Tuning Set

A SQL Tuning Set (STS) is a database object that contains SQL statements along with their execution statistics and context, which could include a user-defined priority. It can be populated from sources like AWR, the shared SQL area, or custom SQL queries.

An STS includes:

• SQL statements
• Execution context (e.g., user schema, application module name, action, bind values, cursor compilation environment)
• Execution statistics (e.g., elapsed time, CPU time, buffer gets, disk reads, rows processed, optimizer cost)
• Execution plans and row source statistics (optional)

To create an STS:

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    SQLSET_NAME => 'atfz4c63at1k1_STS01',
    DESCRIPTION => 'Test SQL Tuning Set'
  );
END;
/

This will create the SQL tuning set successfully.

3) Verify SQL Monitoring is Enabled for the SQL Statement

Check if SQL monitoring is enabled for the specific SQL statement:

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

4) Locate the AWR Snapshot Needed to Populate the SQL Tuning Set

To find the relevant AWR snapshots:

SELECT snap_id, begin_interval_time, end_interval_time 
FROM dba_hist_snapshot 
ORDER BY end_interval_time DESC;

5) Populate the SQL Tuning Set

There are two options for populating the 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;
/

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) Verify Contents of the SQL Tuning Set

To confirm the contents of your SQL tuning set, run:

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

7) Load the Desired Plan as a SQL Plan Baseline

To load the plan from the SQL tuning set into a 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 SQL Plan Baseline Creation

To verify that the SQL plan baseline was successfully created:

SELECT * FROM dba_sql_plan_baselines;

This guide provides all the necessary steps for creating an SQL baseline for a specific SQL_ID and ensuring plan stabilization.