How to create a SQL baseline in oracle

 Hi Guy’s,

Please check the below steps to create SQL baseline.

Find Good has value from AWR.

Mentioned the hash value with sql-id in below procedure to create SQL handler and plan name.

var v_num number;

exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => '8fc16qy3tqd3y',plan_hash_value => 862757774);

PL/SQL procedure successfully completed.

 

Check the sqlhandler and plan created

set line 999

col CREATED format a30

select CREATOR,CREATED,sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where to_char(created,'dd/mm/yyyy') = '07/03/2024'

order by created desc;

 

CREATOR                          CREATED                    SQL_HANDLE                      PLAN_NAME                   ENA    ACC    FIX

SYS     07-MAR-24 05.31.36.046408 PM    SQL_a31118028547b7bb   SQL_PLAN_a648s0a2ngdxv5648d7fa   YES    YES    NO

Fix Baseline

var v_num number;

 exec :v_num:=dbms_spm.ALTER_SQL_PLAN_BASELINE (sql_handle =>'SQL_a31118028547b7bb',plan_name => 'SQL_PLAN_a648s0a2ngdxv5648d7fa', attribute_name=> 'FIXED',attribute_value  => 'YES');

 PL/SQL procedure successfully completed.

Check SQL-ID fixed or not.

SQL> select CREATOR,CREATED,sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where to_char(created,'dd/mm/yyyy') = '07/03/2024' order by created desc; 

CREATOR                          CREATED                          SQL_HANDLE                               PLAN_NAME                                  ENA   ACC   FIX

SYS               07-MAR-24 05.31.36.046408 PM   SQL_a31118028547b7bb    SQL_PLAN_a648s0a2ngdxv5648d7fa    YES YES YES <<--Fixed

 Ø    Go the OEM in SQL SEARCH mention the sql-id and check baseline is showing or not.

************ Happy Learning **************