RE: Survey: 11g plan management (DBMS_SPM)
- From: "Austin Hackett" <Austin.Hackett@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 19 Jun 2011 11:32:19 +0100
Laimis
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
Other related posts:
