Loading SQL Plans into SPM using AWR

SPM (SQL Plan Management) has been an useful solution for performance issues due to change in execution plan.As mentioned in Oracle Optimizer team’s blog —

SQL Plan Management (SPM) allows database users to maintain stable yet optimal performance for a set of SQL statements. SPM incorporates the positive attributes of plan adaptability and plan stability, while simultaneously avoiding their shortcomings. It has two main objectives:

prevent performance regressions in the face of database system changes
offer performance improvements by gracefully adapting to database system changes

SQL Plan baselines can be loaded in multiple ways

1. Using SQL Tuning set (STS_
2. From Cursor Cache
3. Export and Import using Staging table
4. Automatically

Here we would be see how to manually load the plans from AWR to SQL Plan Baseline.To load from AWR we need to create sql tuning set

SQL> exec dbms_sqltune.create_sqlset(sqlset_name => 'b8rc6j0krxwdc_sqlset_test',description => 'sqlset descriptions');

Identify the snap_id in which the sql belongs using dba_hist_sqlstat view.Once identified

declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,'sql_id='||CHR(39)||'&sql_id'||CHR(39)||'',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('b8rc6j0krxwdc_sqlset_test', baseline_ref_cur);
end;
/

It would prompt for the begin_snapid , end_snapid and the sql_id for which you want to load the sqlset with. Along with sql_id we can give the specific plan_hash_value in DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY.

declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,'sql_id='||CHR(39)||'&sql_id'||CHR(39)||' and plan_hash_value=1421641795',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('b8rc6j0krxwdc_sqlset_test', baseline_ref_cur);
end;
/

By default, select_workload_repository does not include the SQL Plan so we pass TYPICAL or ALL as the ‘attribute_list’ parameter to get the plan. The default value of BASIC does not capture the plan.

attribute_list

List of SQL statement attributes to return in the result. The possible values are:

TYPICAL – BASIC + SQL plan (without row source statistics) and without object reference list (default)

BASIC – all attributes (such as execution statistics and binds) are returned except the plans. The execution context is always part of the result.

ALL – return all attributes

Comma-separated list of attribute names this allows to return only a subset of SQL attributes: EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN_STATISTICS (similar to SQL_PLAN + row source statistics).

To load all the sqls captured in AWR snapshot use “NULL” in basic_filter.

declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('all_sqlset_test', baseline_ref_cur);
end;
/

View the sqlset using DBA_SQLSET

05:58:44 SYS >  SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='b8rc6j0krxwdc_sqlset_test';

NAME                                     OWNER      CREATED                     STATEMENT_COUNT
---------------------------------------- ---------- --------------------------- ---------------
b8rc6j0krxwdc_sqlset_test                SYS        04-JUL-12-05:58:44                        1

To verify the execution Plan of a SQL_ID in the STS

select * from table(dbms_xplan.display_sqlset('b8rc6j0krxwdc_sqlset_test','&sql_id'));

The baseline has not yet been loaded

SYS > select count(*) from dba_sql_plan_baselines;

  COUNT(*)
----------
         0

To load SQL execution plans from SQL set into SQL baseline —

set serveroutput on
declare
my_int pls_integer;
begin
my_int := dbms_spm.load_plans_from_sqlset (
sqlset_name => 'b8rc6j0krxwdc_sqlset_test',
basic_filter => 'sql_id="b8rc6j0krxwdc",
sqlset_owner => 'SYS',
fixed => 'NO',
enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_int);
end;
/

SYS >  select count(*) from dba_sql_plan_baselines;

  COUNT(*)
----------
         2

To load sql_id with specific plan_has_value use basic_filter => ‘sql_id=”b8rc6j0krxwdc” and plan_hash_value =1306981985’,

To verify the plan baseline

06:41:23 SYS@ornct1 > SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE FROM   DBA_SQL_PLAN_BASELINES;

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX MODULE
------------------------------ ------------------------------ -------------- --- --- --- ----------------------------------------------------------------
SQL_bc74153ac0be1578           SQL_PLAN_bsx0p7b0bw5bs02dad9b2 MANUAL-LOAD    YES YES NO  emagent_SQL_rac_database
SQL_bc74153ac0be1578           SQL_PLAN_bsx0p7b0bw5bsfcc37a57 MANUAL-LOAD    YES YES NO  emagent_SQL_rac_database

If you want a particular plan_hash_value to be fixed use ‘sql_id=”b8rc6j0krxwdc” and plan_hash_value =1306981985’, fixed => ‘YES’ in dbms_spm.load_plans_from_sqlset.

References and interesting reads –

http://avdeo.com/2012/06/20/fixing-sql-plans-the-hard-way-part-1/
http://avdeo.com/2012/07/04/fixing-sql-plans-the-hard-way-part-2/
http://oracle-randolf.blogspot.in/2009/03/plan-stability-in-10g-using-existing.html
https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4_creating_sql_plan_baselines
HOW TO LOAD SQL PLANS INTO SPM FROM AWR (Doc ID 789888.1)

Advertisements