09 07

How to use v sql_plan

The best-cost plan found by the optimizer that does not match any plans in the plan history for the SQL statement represents a new plan, and is added as a non-accepted plan to the plan history. The new plan is not used until it is verified to not cause a performance regression. However, if a change in the system (such as a dropped index) causes all accepted plans to become non-reproducible, the optimizer will select the best-cost plan. Thus, the presence of a SQL plan baseline causes the optimizer to use conservative plan selection strategy for the SQL statement.

Clear as mud?

Now my description:

Baselines are the latest evolution in Oracle’s efforts to allow a plan to be locked.

That gives us the ability to manipulate the text of the SQL statement in order to get the plan we want. Outlines can be tricked into doing something similar by swapping hints between statements. SQL Profiles also can do something similar by using a trick to load the hints from one statement into another (see my previous post for an example). But Baselines have the built-in ability to do this. No tricks, no sneaky stuff, just call the procedure directly and it’s done. Of course the optimizer will have to verify that the plan will work for the statement you attach it to. If it fails this validation step, then the optimizer will ignore it and go on about it’s normal business.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>