RE: Survey: 11g plan management (DBMS_SPM)

I've been using some SQL Plan Baselines in a production system for a few months now. For me there are 2 really useful features. First, the ability to get the plan you want with hints and then generate a baseline for the problem statement using that plan. Secondly, being able to generate a baseline from a plan contained in the AWR.  

 I found Kerry Osborne's blog post on the subject to be really useful in getting me started:
http://kerryosborne.oracle-guy.com/2009/04/oracle-11g-sql-plan-managemen t-sql-plan-baselines.  

Issues I've come across:  

• Bug 10222321 ORA-38141: SQL PLAN BASELINE SQL_PLAN_XXXXXXXX DOES NOT EXIST in 11.2.0.2.2
• Some baselines no longer being reproducible after application of critical patch update 2 (11.2.0.2.2). Still not got to the bottom of this one, but I'm working with support...
• Evolving a baseline (commit => no, verify => yes) led to a plan being accepted that I had already seen was suboptimal for certain bind values. Not a big deal, since I was aware of this and was able to just evolve just the plan I wanted (verify => no, commit => yes). However, the experience makes me wary of creating an automated routine that blindly evolves all baselines. On the other hand, this leads you into a position where you have to micro manage any new plans that are auto-captured to the baseline. I would have liked to have analysed the issue more closely at the time, but was in fire fighting mode!
• The first execution of a statement that uses a baseline appears to be discarded from the shared pool. Kerry also observed this in his blog. This can be confusing if you're not aware of it. You query v$sql to check that the plan is using the baseline but it doesn't appear to be and you assume you've done something wrong...

Actually, I put together some procedure notes and scripts for creating new baselines, many of them inspired by or taken from Kerry's blog post or his
http://blog.enkitec.com/wp-content/uploads/2010/06/Kerry-Osborne-My-Favo rite-Scripts-2010.pdf presentation. Happy to share those if you want to pass on your details...  

Austin

--
http://www.freelists.org/webpage/oracle-l