RE: Survey: 11g plan management (DBMS_SPM)

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: