Manual SQL execution plan loading into SQL baseline – part 1 – from AWR or SQL Tuning Set

Manual SQL execution plan loading into SQL baseline from AWR or SQL Tuning Set

In 11g, manually loading existing SQL plans as SQL plan baseline can help stabilize sql query performance. Existing plan can be loaded from AWR/SQL tuning set or from Cursor cache. Here let us load plans from AWR and SQL tuning set; later, we visit how to load from cursor cache.

If a SQL tuning set with a good execution plan already exists, we can skip step 1 and load plans into SQL baseline using DBMS_SPM.LOAD_PLANS_FROM_SQLSET function. If we have to load a execution plan in AWR, we need first create a SQL tuning set for the query and then load it into SQL baseline.

Step 1: Create SQL tuning set (SQLSET) from AWR.
SQL> exec dbms_sqltune.create_sqlset(
sqlset_name => ‘sql_tuning_set_name’,
description => ‘sqlset descriptions’);

SQL>
declare
ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( &begin_snap_id, &end_snap_id,
basic_filter=>’sql_id=”&sql_id”’,
attribute_list =>’ALL’)) p;
DBMS_SQLTUNE.LOAD_SQLSET(‘sql_tuning_set_name’, ref_cur);
end;
/

basic_filter is a SQL predicate to filter the SQL from the workload repository defined on attributes of the SQLSET_ROW.
We use sql_id and/or plan_hash_value here.

SQL>
declare
ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( &begin_snap_id, &end_snap_id,
basic_filter=>’sql_id=”&sql_id” and plan_hash_value=”&sql_plan_hash_value”’,
attribute_list =>’ALL’)) p;
DBMS_SQLTUNE.LOAD_SQLSET(‘sql_tuning_set_name’, ref_cur);
end;
/

Check out in SQLSET
SQL>
select * from table(dbms_xplan.display_sqlset(‘sql_tuning_set_name’,'&sql_id’));

Step 2: Load SQL execution plans from SQL set into SQL baseline.

set serveroutput on
declare
v_int pls_integer;
begin
v_int := dbms_spm.load_plans_from_sqlset (
sqlset_name => ‘sql_tuning_set_name’,
sqlset_owner => ‘SYS’,
basic_filter => ‘sql_id=”&sql_id” and plan_hash_value =”&sql_plan_hash_value”’,
fixed => ‘NO’,
enabled => ‘YES’);
DBMS_OUTPUT.PUT_line(v_int);
end;
/

basic_filter is used here to filter out only qualifying plans. It can take the form of any WHERE clause predicate that can specified against the view DBA_SQLSET_STATEMENTS.

For details, please visit

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sqltun.htm#CACJHDDC

http://download-west.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_spm.htm#CACBCAGD