Sep 11 2008
Long Parses and SQL Plan Baselines
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?