how to create sql baseline from AWR repository without STS

We have seen that sql baseline can be created with AWR using sql tuning set. With 12c, Oracle provided a way to directly create sql baseline using AWR without creating the tuning set. It is through procedure  DBMS_SPM.LOAD_PLANS_FROM_AWR. In this post, we will check how to create sql baseline from the AWR repository without STS ie using  DBMS_SPM.LOAD_PLANS_FROM_AWR

How to find the snap id and plan hash value of the sql id

Now we need to know which plan hash value executed faster and which snap it is present

select
        s.snap_id         ,
        q.sql_id          ,
        q.plan_hash_value ,
        round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000),0) || 'ms' "EXECUTION TIME (ms)"
from
        dba_hist_sqlstat q,
        dba_hist_snapshot s
where
        q.SQL_ID          =trim('0ykff17q5arjd')
and     s.snap_id         = q.snap_id
and     s.dbid            = q.dbid
and     s.instance_number = q.instance_number
group by
        s.snap_id ,
        q.sql_id  ,
        q.plan_hash_value
order by 4, 1 desc;

Load the SQL Plan Baselines from the AWR

Once we know the begin and end snap id, and plan_hash_value , we can add using the below procedure

variable x number
BEGIN
    :x := dbms_spm.load_plans_from_awr(
             begin_snap   => 292,
             end_snap     => 294,
             basic_filter => q'# sql_id='&sql_id' and plan_hash_value='&plan_hash_value' #');
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 create sql baseline from Sql tuning set in Oracle
How to drop the sql baseline in Oracle