How to create sql baseline from cursor cache in Oracle

SQL Plan Baselines in Oracle Database are a method for maintaining the consistency of SQL statements’ performance.SQL baseline can be created from cursor cache, AWR, Sql tuning set etc. In this post, We will be looking at How to create sql baseline from the cursor cache in Oracle

If you want to create a SQL Plan Baseline directly from the SQL that resides in the cursor cache (shared pool), you will need the SQL_ID of the query you’re interested in

How to find the sql id

We can find the sql id using the below query

SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text  
FROM  v$sql 
WHERE sql_text like 'SELECT /* TARGET SQL */%'

You can also find this using the below query

SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&An_Identifiable_String%';

How to get the details of the sql id

We should check all the details of the sql id for which we want to create the sql baseline

select SQL_ID,HASH_VALUE,CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS from v$sql where sql_id='&sql_id';

How to create sql baseline from cursor cache

Once you are aware of the sql id and plan hash value, we can load the sql baseline from the cursor cache using the below plsql procedure

set serveroutput on 
DECLARE 
x NUMBER; 
BEGIN 
x := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&sql_id', plan_hash_value => &plan_hash_value ); 
dbms_output.put_line(to_char(x) || ' plan baselines loaded'); 
END; 
/

How to check the sql baseline created

Once the sql baseline is created, we should check if this is by using the below sql statement

SELECT sql_handle, plan_name, origin, enabled, accepted FROM dba_sql_plan_baselines WHERE sql_text LIKE '%YOUR_SQL_STATEMENT%';

or

col sql_id         format a14
col sql_handle     format a22
col plan_name      format a32
col sql_text       format a40
col enabled        format a8
col accepted       format a8
select
        DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) as sql_id,
        sql_handle                                    ,
        plan_name                                     ,
        enabled                                       ,
        accepted
from
        dba_sql_plan_baselines
where
        DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) = '&sql_id';

The baseline will be ENABLED and ACCEPTED.

How to check the explain plan for the Sql baseline

We can check the explain plan using the below sql statements

select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>
'&sql_plan_name'));

Here sql_plan_name is the plan_name from the above query

or

select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=> '&sql_handle'));

Here sql_handle is the sql_handle from the above query

Remember that using the DBMS_SPM package requires the ADMINISTER SQL MANAGEMENT OBJECT privilege.

Additionally, keep in mind that while creating SQL Plan Baselines can help ensure consistent performance, it doesn’t guarantee the best performance. A DBA should always monitor the performance and manage the SQL Plan Baselines as needed, for example by evolving new plans and accepting or rejecting them based on their performance.

Related Articles

SQL Tuning Set in Oracle
how to create sql baseline in Oracle 19c
how to create sql baseline from Sql tuning set in Oracle
How to drop the sql baseline in Oracle
how to create sql baseline from AWR repository without STS
Sql plan Management in Oracle