A friend in need is a friend to avoid: should I capture sql plan baselines?

A friend in need is a friend indeed. I am talking about SPM, SQL Plan Management. Or should I rather say that a friend in need is a friend to avoid. Let us see.

Maxym Kharchenko wrote a very interesting note entitled How to screw up your neighbor. He says: Unbelievable! and considers his findings as a major bug.

So, the direct question is: Should we set optimizer_capture_sql_plan_baselines=true?

My answer is NO. Unless you run Oracle 12.1 and above where you could consider it. Or at least heavily patched 11.2.0.2 Oracle on Microsoft OS (Oracle on Windows is another absurd but that is another story). And I would like to explain why by giving you two reasons on why not to touch the default value.

If you would like to recall what the parameter optimizer_capture_sql_plan_baselines is all about, please check the following article from Pythian called Wouldn’t “Automatic SQL Plan Management” be the 11g “killer app”. In short, let me quote the documentation: “SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.”

As of now, June 2011, by just looking at the bugs above (if running below 12g), I would say that the words locks, slowness and excessive space would be a very, very good indicators to back off and not to play the role of a cowboy DBA.

If the information above is still not enough convincing why you should stay with the default of optimizer_capture_sql_plan_baselines=false, then go on reading.

Reason Two: When doing manual SQL tuning, not all people might be aware that optimizer_capture_sql_plan_baselines has been set to true. In fact, some may be but not totally comprehend the behavior of Oracle in this special case. Their frustration can escalate rather quickly when they notice that recalculating statistics, adding an index, etc. does not change the execution plan and has no effect whatsoever. Not until when Oracle eventually evolves the execution plans and accepts them. Oracle calls this a “conservative plan selection strategy” as the CBO preferentially uses a tried and tested execution plan even if the new plan looks like it might perform much better.

This might have quite of an impact on the people doing manual tuning across the organization. If you have not yet read Maxym Kharchenko’s article How to screw up your neighbor, please have a look.

Note here that if a SQL profile recommendation made by the automatic SQL tuning task is implemented, the execution plan found by the SQL Tuning Task will be added as an accepted SQL plan baseline.

Interesting enough, only when the newer plan is proved to perform well will it be accepted for use. However, is cost the only criteria for an accepted plan? Have a look, this is output from

Why wasn’t the one with lowest cost 31 evolved to an accepted one? Note that this is after running the Automatic SQL Tuning task. Obviously, the plan did not pass the performance criterion. Oracle needs a compound improvement ratio bigger than 1.5 as a new plan must be at least 1.5 times better than the current baseline to be evolved with verification.

Now, how is this compound improvement ratio calculated? I do not know the exact formula but what is taken into account are the ratios of Elapsed Time, CPU Time, Buffer Gets, Disk Reads, Direct Writes and Fetches. In his paper SQL Plan Managementand Your Oracle 11g Upgrade, Mike Tefft supports SQL Plan Management. But looking at his slide 17, I wonder why Oracle fails the performance criterion:

For me this is not a stable and most of all not a simple mechanism. Fixed plan, evolved plan, accepted plan, enabled plan, disabled plan… If it is up to me I will make it more simple and clear. And what is the story behind the way the compound improvement ratio is calculated? I have looked into 100s of “Evolve SQL Plan Baseline Report”s and all looks so ambiguous to me.

But if you are still not convinced about the usage of optimizer_capture_sql_plan_baselines (true or false), then use the SQL Performance Analyzer, just like this:

And view the report:

The Overall Impact is 60% plus. Uups.. It is very much up to what you have in the SQL Tuning Set! This is a very important thing to remember.

One last thing. Oracle suggests to migrate stored outlines to SQL plan baselines! Oracle enables you to safely migrate from stored outlines to SQL plan baselines. After the migration, you can maintain the same plan stability you had using stored outlines while being able to utilize the more advanced features provided by the SQL Plan Management framework. For more information, see “Migrating Stored Outlines to SQL Plan Baselines”.

Thanks for the interesting and useful post. I’ve shied away from SPM simply because I try to avoid using version one of anything, and also because manual sql profiles as per Osborne/Geist/Sierra usually are sufficient for stabilization in my shop. Re “I wonder why Oracle fails the performance criterion” — The number of buffer gets for the test plan was about the same as the baseline, the number of disk reads for the test plan was less than 1% of the baseline, which likely explains the difference in elapsed time. “due to caching” seems the right call in this case. I wonder how well SPM works with long-running db-link queries that spend most of their time waiting on the remote database.

Julian is the Global Database Lead of Accenture. His primary responsibility is managing and leading the Global Oracle Technology Practice which includes Autonomous Cloud, IaaS, PaaS, Database Services, Engineered Systems, Java, Middleware, Security and all other areas falling under Oracle Technology. He is also the Accenture-Enkitec Group Managing Director for ... Continue reading →