SQL Plan Baselines

Here’s a thread from Oracle-L that reminded me of an important reason why you still have to hint SQL sometimes (rather than following the mantra “if you can hint it, baseline it”).

I have a query that takes 77 seconds to optimize (it’s not a production query, fortunately, but one I engineered to make a point). I can enable Sql Plan Baseline Capture and create a SQL Plan Baseline for it and, given the nature of the query, I can be confident that the resulting plan will always be exactly the plan I want. If I have to re-optimize the query at any time  (because it runs once per hour, say, and is constantly being flushed from the library cache) how much of that 77 seconds will be eliminated by the presence of the SQL plan baseline?

The answer is NONE.

The first thing that the optimizer does for a query with an Sql Plan Baseline is to optimize it as if the baseline did not exist.

If I want to get rid of that 77 seconds I’ll have to extract (most of) the hints from the SQL Plan Baseline and write them into the query.  (Or, maybe, create a Stored Outline – except that they’re deprecated in the latest version of Oracle, and I’d have to check whether the optimizer used the same strategy with stored outlines or whether it applied the outline before doing any optimisation). Maybe we could do with a hint which forces the optimizer to attempt to use an existing, accepted SQL Baseline without attempting the initial optimisation pass.

 

Footnote:

I claim ownership of the mantra: “if you can hint it, baseline it” but, since I am a generous, soul I’ve allowed both Maria Colgan and Tom Kyte to make use of it without payment of royalties.