Home > administration > stored outlines – fixed execution plan

stored outlines – fixed execution plan

stored outlines are designed to force a specific sql-statement to use a fixed execution plan.
This execution plan will be used even the table or the environment changes.
Using stored outlines is dependend on the hash_value (in 10g) / sql_id (in 11g).

In 11g a new feature was implemented – the sql plan managment.
This is a great extension to guarantee save plans.
See more in sql plan managment

show explain plan from an existing cursor

select * from table (dbms_xplan.display_cursor(‘<sql_id>,<child_number,’ADVANCED’));

create a stored outline based on an existing cursor

--list existing outlines
select name from dba_outlines;

in 10g
select hash_value, sql_text ,child_number
from v$sql
where sql_text like ‘%select /* RGFTST */%’
/

in 11g
select sql_id ,hash_value, sql_text ,child_number
from v$sql
where sql_text like ‘%select /* RGFTST */%’
/

–to avoid a bug releases <10.2.0.4
alter session set create_stored_outlines=true;

in 10g
exec dbms_outln.create_outline(<hashvalue>,<child_number);

in 11g
exec dbms_outln.create_outline(<sql_id>,<child_number>);

— list outlines
select name, category, used, sql_text, enabled
from dba_outlines
/

enable stored outlines

alter system set use_stored_outlines=true;

you can also define different categories of stored outlines, just name them as you like and enable the desired category as above (category-name instead of true).
true means the usage of “default”-category.

to enable stored outlines permanent a startup trigger is necessary

create or replace trigger enable_outlines_trig
after startup on database
begin
execute immediate(‘alter system set use_stored_outlines=true’);
end;
/

verify usage of outlines

select name, category, used, sql_text, enabled
from dba_outlines
/

used shows the outline has been used or not

To reset the used colum :
exec dbms_outln.clear_used(‘<outline-name>’);

When creating an explain plan at the end of the plan a section is shown, when a stored outline is used.
Using event 10046 the usage of a stored outline WILL NOT BE SHOWN!!

disable stored outlines

alter system set use_stored_outlines=false;

drop stored outlines

drop a specific outline:
drop outline <outline_name>;

drop unused outlines:
exec dbms_outln.drop_unused;

Views

all_|dba_|all_outlines
all_|dba_|all_outline_hints

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: