Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

Stored Outlines and Plan Stability - Use stored outlines to maintain consistent execution plans regardless of changes in the system environment or associated statistics.



Arindam Mukherjee said...

It's really worth reading and it has helped me a lot get a clear picture of this topic. I highly appreciate this effort.

Rich said...

Think you should consider removing the reference to query_rewrite_enabled. This is not specifically required to create or use a stored outline and, at 9i at least, can cause real problems with other queries.

Tim... said...

Hi.

According to the 10g manual it is necessary:

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/outlines.htm#15583

Cheers

Tim...

rich said...

Tim,
Thanks for replying. I believe this is what the doc you refer to says:
"Settings for several parameters, especially those ending with the suffix _ENABLED, must be consistent across execution environments for outlines to function properly. These parameters are:

QUERY_REWRITE_ENABLED
STAR_TRANSFORMATION_ENABLED
OPTIMIZER_FEATURES_ENABLE"

Sorry to be pedantic but I recently fell into this trap and had to raise a tar to confirm I did not need query_rewrite.

Richard said...

You have this:
-- Switch on automatic creation of stored outlines.
ALTER SYSTEM SET create_stored_outlines=TRUE;
ALTER SESSION SET create_stored_outlines=TRUE;

-- Switch on automatic creation of stored outlines.
ALTER SYSTEM SET create_stored_outlines=FALSE;
ALTER SESSION SET create_stored_outlines=FALSE;

Second one needs to say "Switch off ..."
.

Nice article!

Ashish Kohli said...

Excellent stuff...

sunil said...

How do i enable a session X to use the outline...

For example I want BLAKE session to use the sored oultline. But, i cannot use alter session from his session....

thx

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

Add your comments here.
Name
Comment
(max 400 chars - plain text)