Plan stability

A longer version of this posting, with experimental results, is available on my website.

In PeopleSoft for the Oracle DBA, I wrote a page (p. 291) explaining why stored outlines were not suitable for use in PeopleSoft.  Five years later, my view has not significantly changed.  Essentially, stored outlines work best with shared SQL, and there isn't much shared SQL in PeopleSoft, because a lot of it is dynamically generated.

A longer version of this posting, with experimental results, is available on my website.

In PeopleSoft for the Oracle DBA, I wrote a page (p. 291) explaining why stored outlines were not suitable for use in PeopleSoft.  Five years later, my view has not significantly changed.  Essentially, stored outlines work best with shared SQL, and there isn't much shared SQL in PeopleSoft, because a lot of it is dynamically generated.

Thank you for all those who attended my webcast today on SQL Plan Management for All India Oracle User Group. It was a privilege to present before you and to be able to address your questions. I am sorry I couldn;t read all the questions properly; since it was extremely difficult to see the questions scrolling up in the tiny chat window. Also, as the webcast was designed, I couldn't hear anything the attendees were saying.

The presentation and the associated SQL scripts are available here. The article I referred to can be found here.

If you have a question regarding that specific webcast, please post a comment here and I will address it here. Please, limit your questions to the material discussed in the webcast only.

I just finished the third of my four presentations - SQL Plan Management. Considering it was at 9 AM on the last day of the conference, right after the big Aerosmith concert, I was expecting a lot less crowd. But, to my pleasant surprise about 150 brave souls turned up. Thank you all. I hope you found it useful.

Here is the presentation material. While you are there, feel free to browse around. And, of course, I will highly appreciate if you could send me your comments, either here or via email - whatever you are comfortable with.

If you have the need for plan stability - that is telling the database to use a particular execution plan no matter what the optimizer thinks otherwise - then you might be in the situation that the "good" execution plan is already available in the shared pool or in the AWR, so it would be handy if you could simply tell Oracle to use that particular execution plan to create a Stored Outline.

Note that in 11g this is all possible using the new SQL Plan Management framework (SPM), but that is not available in 10g, so we need to think differently.

In 10g the DBMS_OUTLN package has been enhanced with the CREATE_OUTLINE procedure to create an outline from an existing child cursor in the shared pool.

Please note that in releases prior to 10.2.0.4 there was a severe bug that caused your session to crash when using DBMS_OUTLN.CREATE_OUTLINE (Bug 5454975 which has been fixed in 10.2.0.4). The workaround is to enable the creation of stored outlines by issuing "alter session set create_stored_outlines = true;" before using DBMS_OUTLN.CREATE_OUTLINE. For more information see the Metalink Notes 463288.1 and 445126.1.

Note that from 10g on the hints required to create an outline are stored as part of the plan table in the OTHER_XML column as part of the XML detail information.

You can use the ADVANCED or OUTLINE option of the DBMS_XPLAN.DISPLAY* functions to display that OUTLINE information. For more information see e.g. here.

So let's try DBMS_OUTLN.CREATE_OUTLINE in 10.2.0.4: