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

How to check the SQL Statement Plan?

When you do an explain plan, then the appropriate DBMS_XPLAN api is
DBMS_XPLAN.DISPLAY.
When you do an actual execution - i.e. not with explain plan - then
DBMS_XPLAN.DISPLAY_CURSOR is the appropriate api.

Troubleshooting for DBMS_XPLAN.DISPLAY_CURSOR
Error:-
NOTE: cannot fetch plan for SQL_ID: 3pb80q4uks11n, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)

grant select ON v_$session TO &&1;
grant select on v_$sql_plan to &&1;
grant select on v_$sql to &&1;

set serverouput off;

ASH Report – sql_id

SELECT
  trunc(begin_interval_time, 'WW'),
  ROUND(sum(elapsed_time_delta)/sum(executions_delta)/1000)
FROM dba_hist_sqlstat a
JOIN dba_hist_snapshot b
ON a.snap_id = b.snap_id
WHERE sql_id        = '&sql_id'
AND executions_delta>0
GROUP BY trunc(begin_interval_time, 'WW')
ORDER by trunc(begin_interval_time, 'WW') DESC

http://portrix-systems.de/blog/brost/tracking-historic-sql-execution-time-with-ash/