You Asked

Hello, Tom.

SQL Plan Management sounds like a really good way to get optimizer to choose the right execution plans. (Actually, I expect Oracle to choose the right execution plan 99% of the time.) Anyway, the addition of the SQL Plan Management looks like Oracle has made a big step into one day having a fully-baked optimizer.

Performance Overhead:
-------------------------------
One of the biggest concerns is the performance overhead of using it. Should we all set optimizer_capture_sql_plan=true and optimizer_use_sql_plan=true by default? Will the performance improvements outweigh the performance overhead? I am pretty sure that you will say yes because the role that SPM plays, is what a mature optimizer should do.

Evolve:
---------
Regarding evolving a baseline, if the optimizer processes a SQL statement that has a sql plan baseline, is it possible for it to evolve that baseline if the value for optimizer_capture_sql_plan = false?

What if the value for optimizer_use_sql_plan = false, can it evolve it?

Fixed:
-----
Why is there even a ‘fixed’ option? Oracle states the following: “During the SQL plan baseline evolution phase, the database evaluates the performance of new plans and integrates plans with better performance into SQL plan baselines.”

Why would anyone want to prevent the optimizer from giving a SQL statement a better execution plan?

BTW: I am disappointed that the online documentation doesn’t have more information regarding this complex addition. Also, I wish that they made a bigger effort to implement this ‘SQL Plan Management’ as a ‘behind the scene’ optimizer task. The optimizer should just auto implement better plans for SQL statements. By putting this complex responsibility (capturing sql plan baselines, turning it on, evolve or fix execution plans) in the hands of DBAs, the applications will suffer unnecessarily.

and Tom said...

You would use capture sql plan only during the period of time you are "happy" with plans. For example - before an upgrade.

The performance hit will only come during a hard parse - if you have a 'bad' system - that hard parses like mad - it will be large. If you have a well developed system with all soft parses (or even better - NO parses) it will be small.

Same with use sql plan - the hit is on the hard parse. If you don't hard parse, you don't have a hit. If you do, the hit is proportional to the amount of hard parsing you do.

That is, if you have a busted system that hard parses like mad, there isn't too much that will help you in general.

If you have a correctly developed system - it will have minimal performance impact at runtime parsing, and great benefit overall.

if you have use sql plan set to false, evolving doesn't have to happen, we'll just use it right away, you have disabled the feature basically.

Why would anyone want to prevent the optimizer from giving a SQL statement a better execution plan?

because in many cases, people prefer stability, consistency, predictability over
*everything* else. It is a choice.

what is missing from the documentation?
http://docs.oracle.com/cd/E11882_01/server.112/e16638/optplanmgmt.htm

Have you left a detailed comment outlining what you think it missing there (bottom of page..) They read those and act on them...

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Session parameters disabling baseline?

Tom McCarthy, September 11, 2013 - 7:54 pm UTC

Hi Tom,

We are new to SPM and are seeing the following.  Essentially, the same SQL will alternately use or not-use its SQL baseline depending on the session-level setting of optimizer_dynamic_sampling.  We thought that for a given identical piece of sql, an accepted plan from the baseline would always be used.  Could you possibly clarify?  

Thanks for your help over the years, the number of times your examples and advice have helped us is innumerable.

Example below, with plan details redacted.  Note change in plan hash value. Thanks again.  



SQL> show parameter dyn

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
SQL>
SQL>
SQL>
SQL> @explain_statement

Explained.

SQL> SELECT * FROM table(dbms_xplan.display)
/

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3095750719

--------------------------------------------------------------------------------
-------------------------------------------------------------------------

....

Note
-----
   - dynamic sampling used for this statement (level=4)
   - SQL plan baseline "SQL_PLAN_0x2ab82q13k0pd4e4ab0c" used for this statement

93 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> alter session set optimizer_dynamic_sampling = 0;

Session altered.

SQL>
SQL> show parameter dyn

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     0
SQL>
SQL>
SQL>
SQL> truncate table plan_table;


Table truncated.

SQL> SQL>
SQL>
SQL> @explain_statement

Explained.

SQL> SELECT * FROM table(dbms_xplan.display)
/

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1099325281

--------------------------------------------------------------------------------
------------------------------------------------------------------------

....

92 rows selected.

Qestion on SPM set on DB level

JohnM, March 30, 2014 - 11:57 am UTC

Hello Tom,
we are a DB on 11.2.0.3.4, and have enabled SPM since last August. The db has been performing well.
Looked up
select count(SIGNATURE) from dba_sql_plan_baselines
COUNT(SIGNATURE)
----------------
212968

we have over 200 thousands plans in the db.

I wonder that what should we be concerned any in regrad?
Should we reset the SPM?

March 31, 2014 - 10:05 am UTC

have you been capturing plans since last August? Is that what you are saying???

A reader, April 01, 2014 - 1:13 am UTC

tom's asking : have you been capturing plans since last August? Is that what you are saying???

Yes.

April 01, 2014 - 5:18 am UTC

then you have sort of missed the point of SQL Plan Management.

you go through three phases:

1) You capture plans. You do this for a finite period of time. You do this just long enough to capture the baselines (the plans you want to use by default, the set of plans you are happy with) for the SQL you execute frequently. You STOP capturing plans (since every single plan you capture right now is "accepted" - is assumed to be good).

2) you live normally in a phase where the baseline is used - but any new plans are added to the plan history - NOT as an accepted plan but as an untested plan. The optimizer will use an ACCEPTED plan from the baseline. So, new plans can get generated but they will NOT BE USED right away. They will be filed away - and one of the accepted plans (generated in (1)) will be used for the query.

3) you either schedule a job or manually run the plan evolution routines. These will TEST the plans that are in the plan history but not accepted. They will actually execute the query against your data and measure the amount of resources the new plans consume. IF the new plans are significantly better than the existing plans in the baseline, we will mark the new plan as accepted and start allowing that plan to be used to execute the query.

If you are always running in capture mode, sql plan management isn't doing anything for you other than adding to the amount of work we are doing!! You are automagically accepting every single plan!

Capturing plans

Mike, April 01, 2014 - 12:30 pm UTC

If optimizer_capture_sql_plan_baselines = TRUE then new plans will be captured and added to the baselines. But a captured baseline is not created in the ACCEPTED state unless it is the first plan captured for a given SQL. Other plans would be created as 'not accepted' and would only be 'evolved' if there was some specific action to do so.

Referring to 'phases' of baseline use, I could see a phase where you farm AWR and/or the cursor cache for a 'starting set' of baselines. But I believe the intention is to operate with optimizer_capture_sql_plan_baselines = TRUE , and to have a regular process to evaluate and evolve the new plans that the optimizer identifies and adds to the baselines.

If you think you have a horribly large number of baselines, then the problem is most likely that you have a horribly large number of SQL statements. Is the SQL properly using bind variables?

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Session parameters disabling baseline?

Qestion on SPM set on DB level

Capturing plans

More to Explore

Performance

Shed some light on SQL Plan Management

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Session parameters disabling baseline?

Qestion on SPM set on DB level

Capturing plans

More to Explore

Performance