sql plan management – how to fix an execution plan

In the past stored outlines were used to fix execution plans
In 11g the next level to control execution plans is implemented
its possible to force the optimizer to use one or more execution plans for a statement, uncontrolled plan changes are disabled.

take a closer look at the following examples – test it!

more details can be found in “ORACLE documentation -Oracle® Database Performance Tuning Guide – Chapter 15 Using SQL Plan Management”
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/optplanmgmt.htm#BABEAFGG

Note in http://support.oracle.com SQL PLAN MANAGEMENT [ID 456518.1]


— sqlplan management

— Roland Graeff, Germany
— tested on 11.2

— test table

create table tst (col1 number, col2 varchar2(20));
insert into tst values (1,’A’);
insert into tst values (2,’B’);
commit;

select /* RGFTST */ col2 from tst where col2 = ‘A’;

— to test/show the used execution plan
explain plan for
select /* RGFTST */ col2 from tst where col2 = ‘A’;
select * from table(dbms_xplan.display);

— sample output
/*
PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 4148258400

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 2 | 24 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TST | 2 | 24 | 3 (0)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“COL2″=’A’)

Note
—–
– dynamic sampling used for this statement
*/

— select sql_id
select sql_id, plan_hash_value, sql_text
from v$sql
where sql_text like ‘%RGFTST%’
/

— use the accordingly sql_id in the next plsql block
— to put the execution plan to the sqlplan management

SET SERVEROUTPUT ON
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => ‘8d6dshztauuct’,fixed => ‘YES’);
DBMS_OUTPUT.put_line(‘Plans Loaded: ‘|| my_plans);
END;
/

— list the sqlplan baselines
SELECT sql_handle, plan_name, enabled, accepted,fixed
FROM dba_sql_plan_baselines
/

— create index
create index i1 on tst (col2)
/

— in normal circumstances the index i1 should be used
— to check the used execution plan

explain plan for
select /* RGFTST */ col2 from tst where col2 = ‘A’;
select * from table(dbms_xplan.display);

— sample output
/*
PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 4148258400

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TST | 1 | 12 | 2 (0)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“COL2″=’A’)

Note
—–
– dynamic sampling used for this statement
– SQL plan baseline “SYS_SQL_PLAN_a5df9d5502e4a954″ used for this statement
*/

— to change attributes for this sqlplan

SELECT sql_handle, plan_name, enabled, accepted,fixed
FROM dba_sql_plan_baselines
/

DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>’SYS_SQL_67a764aaa5df9d55′,
attribute_name=>’ENABLED’,
attribute_value=>’NO’);
DBMS_OUTPUT.PUT_LINE(‘Plans altered: ‘ || v_cnt);
END;
/

SELECT sql_handle, plan_name, enabled, accepted,fixed
FROM dba_sql_plan_baselines
/

— to show the changes
explain plan for
select /* RGFTST */ col2 from tst where col2 = ‘A’;
select * from table(dbms_xplan.display);

— sample output
/*
PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1816312439

————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I1 | 1 | 12 | 1 (0)| 00:00:01 |
————————————————————————-

Predicate Information (identified by operation id):
—————————————————

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – access(“COL2″=’A’)

Note
—–
– dynamic sampling used for this statement
*/

–to remove sqlplan
declare
drop_plan PLS_INTEGER;
BEGIN
drop_plan :=dbms_spm.drop_sql_plan_baseline(sql_handle=>’SYS_SQL_67a764aaa5df9d55′,plan_name=>’SYS_SQL_PLAN_a5df9d5502e4a954′);
END;
/

declare
drop_plan PLS_INTEGER;
BEGIN
drop_plan :=dbms_spm.drop_sql_plan_baseline(sql_handle=>’SYS_SQL_67a764aaa5df9d55′,plan_name=>null);
END;
/

— export / transfer baselines to a different instance
— create a staging table
execute dbms_spm.CREATE_STGTAB_BASELINE(table_name=>’RGRAEFF_STAGE_BL’,table_owner=>’RGRAEFF’);

— export them to the staging table
variable var varchar2(30);
execute :var:=dbms_spm.PACK_STGTAB_BASELINE(table_name=>’RGRAEFF_STAGE_BL’ ,table_owner=>’RGRAEFF’);
execute dbms_output.put_line(‘Baselines exportiert: ‘||:var);

— now this table can be exported/imported via datapump to the target instance
— with dbms_spm.UNPACK_STG_TAB_BASELINE the exported baselines can be loaded

— remove test tables
drop table tst purge;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s