How to Create an SQL Baseline for a Specific SQL_ID

Learn how to create an SQL baseline for a specific SQL_ID to stabilize execution plans and ensure consistent query performance in Oracle databases. This guide walks you through the necessary steps to create, manage, and use SQL baselines for optimal query optimization and improved database performance.

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.