Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Long Parses and SQL Plan Baselines

11 September 2008 Leave a Comment Written by Christian Antognini

Main steps carried out during the selection of a SQL plan baseline

Few days ago, in the post entitled Long Parses, I pointed out that stored outlines can be used to shorten the parse time by restricting the number of execution plans considered by the query optimizer.

As of Oracle Database 11g, stored outlines are deprecated in favor of SQL plan baselines. Therefore, someone might think that SQL plan baselines can also be used to workaround long parses.

Actually, this is not the case :-(

The figure on the right (which is taken from page 292 of TOP) shows why…

Do you spot the problem?

The essential thing to notice is that the query optimizer generates execution plans before considering SQL plan baselines. And, therefore, a long parse would be performed even if a matching SQL plan baseline exists.

Also note that SQL plan baselines are only available in Enterprise Edition. Therefore, when stored outlines will be desupported, in Standard Edition a similar feature will no longer be available. Or do you thing that Oracle will change the licensing policy?

11gR1, 11gR2, 12cR1, 12cR2, Query Optimizer
No Space Left on Device
Upcoming Speaking Engagements

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.