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

LUTZ AS SYSDBA @ racdb11g1 SQL> SELECT *
                               FROM table
(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_818c1879b000a439', format=>
'basic'));

PLAN_TABLE_OUTPUT
------------------------------------
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            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |
|   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            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |
|   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  |           |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |
---------------------------------------

49 rows selected.

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

LUTZ AS SYSDBA @ racdb11g1 SQL> SELECT sql_handle,
                                       plan_name,
                                       enabled,
                                       accepted,
                                       fixed
                                 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?.