8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

Comments for Stored Outlines and Plan Stability


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!

If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.