Create/update sql profile/ sql baseline in Oracle Database

Oracle Performance Tuning

How to create SQL baseline in Oracle database from the backend. 

1. Check for baseline if available, from the SQL-ID

SELECT sql_handle, plan_name, enabled, ACCEPTED,FIXED,created FROM dba_sql_plan_baselines WHERE signature IN ( SELECT exact_matching_signature FROM gv$sql WHERE sql_id='&SQL_ID');

or

SELECT b.sql_handle, b.sql_text, b.plan_name, b.enabled

 FROM   dba_sql_plan_baselines b, v$sql s

 WHERE  s.sql_id='&SQL_ID'

 AND    s.exact_matching_signature = b.signature;

2. Now load the baselines from cursor (If nothing suitable is available on the Query 1):

DECLARE

    ezplan NUMBER;

 BEGIN

    ezplan := sys.dbms_spm.load_plans_from_cursor_cache(sql_id=>'&SQL_ID');

 END;

 /

3. Next, run the select again to see how many are imported and which one is good plan:

SELECT sql_handle, plan_name, enabled, ACCEPTED,FIXED,created FROM dba_sql_plan_baselines WHERE signature IN ( SELECT exact_matching_signature FROM gv$sql WHERE sql_id='&SQL_ID');

If it is returning multiple rows, means multiple plans are available for this SQL-ID.

To check the cost and plans for individual plans run the below script:

from Cursor:

select plan_table_output from table(dbms_xplan.display_cursor('<sql_id>',null,'ADVANCED'));

--If this returns no plan, run the next one which will extract the plan from awr.

select * from table(dbms_xplan.display_awr('<sqlid>'));

4. Once you identify the bad plan disable and drop the bad plan :(it can be done through OEM as well):

To disable the plan run the below command:

DECLARE

  ezdisable NUMBER;

BEGIN

  ezdisable := sys.dbms_spm.alter_sql_plan_baseline(

               sql_handle=>'<sql handle value, op from query 3>', 

               plan_name=>'<bad plan_name value , op from query 3>', 

               attribute_name=>'enabled', attribute_value=>'NO');

END;

/

to drop the plan run the below command:

declare

ezdisable pls_integer;

begin

ezdisable := DBMS_SPM.DROP_SQL_PLAN_BASELINE(

sql_handle => '<sql handle value, op from query 3>',

plan_name => '<bad plan_name value , op from query 3>');

dbms_output.put_line(ezdisable);

end;

/

Now check again for the status of the plans:

SELECT sql_handle, plan_name, enabled, ACCEPTED,FIXED,created FROM dba_sql_plan_baselines WHERE signature IN ( SELECT exact_matching_signature FROM gv$sql WHERE sql_id='&SQL_ID');

5. Now force the good plan for the sqlid and fix it with fixed options.

declare

ez_plans pls_integer;

begin

ez_plans := dbms_spm.alter_sql_plan_baseline (

sql_handle => '<sql_handle of the good plan>',

plan_name => '<plan_name of the good plan>',

attribute_name => 'fixed',

attribute_value => 'YES'

);

end;

/

6. Again run the program or sql and check the sqlid is going through the good plan or not.

SELECT sql_handle, plan_name, enabled, ACCEPTED,FIXED,created FROM dba_sql_plan_baselines WHERE signature IN ( SELECT exact_matching_signature FROM gv$sql WHERE sql_id='&SQL_ID');

Alternatively, if the plan is not available in Cursor, it can be loaded from AWR using sqlset.

--Load sqlid from AWR

BEGIN

DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => '<sqlset_name>');

END;

/

--Verify the execution Plan of a SQL_ID in the STS

select * from table(dbms_xplan.display_sqlset('<sqlset_name>','<sqlset_name>'));

--Load all plans from SQL Tuning Set

DECLARE

ez_plans PLS_INTEGER;

BEGIN

ez_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => '<sqlset_name>');

END;

/

Hope this helps you to create the baseline, if you find any difficulties or any issues in the query, comment in the below section and the query will be revalidated. Thank you for your time.

Best Wishes!!