Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








  11g SQL Baseline Plan Management

Oracle 11g New Features Tips by Donald BurlesonJune 28, 2015

Oracle 11g New Features Tips

So the optimizer has created a new best cost plan for the statement and now uses a full table scan instead of an index access. The original plan has been invalidated because it is non-reproducible since the index used is gone.

Add a primary key again and run the statements again. There are now three plans in the plan baseline, the new one which uses the new primary key and the two historical plans. Note that only one plan is ACCEPTED now the others are only ENABLED. The old plan with the index access was invalidated because it was not reproducible and now the server has remembered that there was a better plan sometime in the past, from the plan history, which used an index and performed much better. It puts this plan in place for further executions automatically:

LUTZ AS SYSDBA @ racdb11g1 SQL> ALTER TABLE hr.employees ADD CONSTRAINT employees_pk PRIMARY KEY (employee_id);

Table altered.

LUTZ AS SYSDBA @ racdb11g1 SQL> SELECT last_name, salary FROM hr.employees WHERE employee_id=100;

LAST_NAME                     SALARY
------------------------- ----------
King                           24000

                               FROM table
(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_818c1879b000a439', format=>

SQL handle: SYS_SQL_818c1879b000a439
SQL text: select last_name, salary from hr.employees where employee_id=100

Plan name: SYS_SQL_PLAN_b000a4397d478871
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE

Plan hash value: 3640292141 

| Id  | Operation                   | Name         |
|   0 | SELECT STATEMENT            |              |
|   2 |   INDEX UNIQUE SCAN         | EMPLOYEES_PK |
Source ----------------------------------------------------

Plan name: SYS_SQL_PLAN_b000a439c0e983c6
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE

Plan hash value: 3640292141

| Id  | Operation                   | Name         |
|   0 | SELECT STATEMENT            |              |
|   2 |   INDEX UNIQUE SCAN         | EMPLOYEES_PK |


Plan name: SYS_SQL_PLAN_b000a439cf314e9e
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE

Plan hash value: 1445457117 

| Id  | Operation         | Name      |
|   0 | SELECT STATEMENT  |           |

49 rows selected.

Here are the details from the data dictionary of the plan baseline:

LUTZ AS SYSDBA @ racdb11g1 SQL> SELECT sql_handle,
                                 FROM  dba_sql_plan_baselines
                                 WHERE LOWER(sql_text) LIKE '%hr.employees%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_818c1879b000a439       SYS_SQL_PLAN_b000a4397d478871  YES NO  NO
SYS_SQL_818c1879b000a439       SYS_SQL_PLAN_b000a439c0e983c6  YES YES NO
SYS_SQL_818c1879b000a439       SYS_SQL_PLAN_b000a439cf314e9e  YES NO  NO

This example was not really fair because dropping an index forces the optimizer to come up with something new.  So what happens if a plan gets irreproducible?

If a SELECT has been parsed before an index ever existed and then reparsed after an index has been created, the optimizer would create a new best cost plan but leave it marked as NON ACCEPTED but ENABLED. This is because the original plan is still reproducible since an index is added and it also works without it. In the case of an index drop, the original plan which would use the index is not reproducible and cannot be used as such.

So in the case of an index creation after the SELECT was parsed the first time, the index will not be used for execution until the plan baseline is evolved manually. In this case, it is the DBA's job to manually evolve the plan baseline. A good way to deal with such a case would be to capture the full scan first and then stop auto capture in order not to get too many different plans. After adding the index, wait for the auto SQL tuning task job to run the next time or trigger SQL tuning manually and look at the results and possibly load new plans manually into the plan baseline.

Also, new optimizer statistics gathered overnight would not lead to an automatic evolution of a SQL plan baseline unless the auto-task job start evaluating the statement and permission has been given to automatically implement SQL Profiles. The SQL plan management with the SMB is a conservative method to guarantee plan stability in the first place.

So the next question to ask is?.


This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr.

You can buy it direct from the publisher for 30% off.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.