how to create sql baseline from Sql tuning set in Oracle

Sql baseline can be created from cursor cache, AWR and Sql tuning set. In this post, we will check out how to create sql baseline from Sql tuning set in Oracle

How to create the Sql tuning set

This can be done using the DBMS_SQLTUNE.CREATE_SQLSET procedure. In the following example, we’re creating a SQL Tuning Set named ‘my_sqlset’:

BEGIN 
DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'my_sqlset', description => 'Sql baseline set'); END; 
/

Load SQL statements into the STS

Now, we load the SQL statements that we’re interested in into the SQL Tuning Set. This could be a selection from v$sql, for example. In this code, we’re populating the STS ‘my_sqlset’ with the top 50 SQL statements order by elapsed time from AWR repository

DECLARE
baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN baseline_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
1116, 
1119,
NULL, 
NULL, 
elapsed_time, 
NULL,
NULL,
NULL,
50)
)) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'sql_tuning_set_1',
populate_cursor => baseline_cursor);
END;
/

Load the SQL Plan Baselines from the STS:

Next, we load the SQL Plan Baselines from the SQL Tuning Set. We do this using the DBMS_SPM.LOAD_PLANS_FROM_SQLSET function:

we can restrict the loading to a particular sql id and plan hash also

set serveroutput on
DECLARE
x PLS_INTEGER;
BEGIN
x:= DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name=>'&sts_name', sqlset_owner=>'&sts_owner', basic_filter => q'# sql_id='&sql_id' and plan_hash_value=&plan_hash_value #');
dbms_output.put_line(to_char(x) || ' plan baselines loaded');
END;
/

or just the Sql_id

set serveroutput on
DECLARE
x PLS_INTEGER;
BEGIN
x:= DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name=>'&sts_name', sqlset_owner=>'&sts_owner', basic_filter =>'sql_id=''&sql_id''');
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

Related Articles

SQL Tuning Set in Oracle
how to create sql baseline in Oracle 19c
How to create sql baseline from cursor cache 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