RE: Survey: 11g plan management (DBMS_SPM)

From: Austin Hackett <>
Date: Sun, 19 Jun 2011 11:32:19 +0100
Message-ID: <66149726C3877543A763EECEDC7FC39B042C4BE2_at_MOR-VEX01.Sbet-EMEA.ADS>


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: t-sql-plan-baselines.  

Issues I've come across:  

  • Some baselines no longer being reproducible after application of critical patch update 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 rite-Scripts-2010.pdf presentation. Happy to share those if you want to pass on your details...  


Received on Sun Jun 19 2011 - 05:32:19 CDT

Original text of this message