SQL Plan Management (SPM) & cursor_sharing=force

SQL plan management is a preventative mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only known or verified plans.

Purpose of SQL Plan Management:

The primary goal of SQL plan management is to prevent performance regressions caused by plan changes. A secondary goal is to gracefully adapt to changes such as new optimizer statistics or indexes by verifying and accepting only plan changes that improve performance.

Benefits of SQL Plan Management:

SQL plan management can improve or preserve SQL performance in database upgrades and system and data changes.

Specifically, benefits include:

• A database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements.
• Most plan changes result in either improvement or no performance change. However, some plan changes may cause performance regressions. SQL plan baselines significantly minimize potential regressions resulting from an upgrade.
• When you upgrade, the database only uses plans from the plan baseline. The database puts new plans that are not in the current baseline into a holding area, and later evaluates them to determine whether they use fewer resources than the current plan in the baseline. If the plans perform better, then the database promotes them into the baseline; otherwise, the database does not promote them.
• Ongoing system and data changes can affect plans for some SQL statements, potentially causing performance regressions.
• SQL plan baselines help minimize performance regressions and stabilize SQL performance.
• Deployment of new application modules introduces new SQL statements into the database.
• The application software may use appropriate SQL execution plans developed in a standard test configuration for the new statements. If the system configuration is significantly different from the test configuration, then the database can evolve SQL plan baselines over time to produce better performance.

Differences Between SQL Plan Baselines and SQL Profiles:

Both SQL profiles and SQL plan baselines help improve the performance of SQL statements by ensuring that the optimizer uses only optimal plans.

Both profiles and baselines are internally implemented using hints. However, these mechanisms have significant differences, including the following:

In general, SQL plan baselines are proactive, whereas SQL profiles are reactive.

How SPM works with cursor_sharing=force?

Here is an simplified example, but it is sufficient to show the problem.

SQL> alter system set cursor_sharing=force;
SQL> show parameter optimizer_capture_sql_plan_baselines

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
optimizer_capture_sql_plan_baselines boolean     FALSE


SQL> create table my_objects as select * from dba_objects;
SQL> set autotrace trace exp
SQL> select object_name from my_objects where object_id=100;

Execution Plan
----------------------------------------------------------
Plan hash value: 880823944

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |    14 |  1106 |   336   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| MY_OBJECTS |    14 |  1106 |   336   (1)| 00:00:05 |
--------------------------------------------------------------------------------

-- full table scan as it is expected

SQL> set autotrace off

-- I will load this plan into baseline 

SQL> select sql_id, sql_text from v$sql 
where sql_text like 'select object_name from my_objects where object_id%';

SQL_ID                  SQL_TEXT
--------------------------------------------------------------------------------
fu2bja6dgn1yg           select object_name from my_objects where object_id=:"SYS_B_0"

SQL> var num_plans number;
SQL> exec :num_plans := dbms_spm.load_plans_from_cursor_cache('fu2bja6dgn1yg');

-- check the plan history, the full table scan plan is now an accepted plan baseline

SQL> select sql_handle, plan_name, enabled, accepted
from dba_sql_plan_baselines
where sql_text like 'select object_name from my_objects where object_id%'; 

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SQL_31a282bdb4642130           SQL_PLAN_338n2rqu6889h89405218 YES YES


-- and then I created an index on the column

SQL> create index my_objects_idx on my_objects(object_id);

-- since there is an accepted plan in baseline, 

I expect optimizer will use full table scan. But optimizer uses a new index scan plan

SQL> set autotrace trace exp
SQL> select object_name from my_objects where object_id=100;


Execution Plan
----------------------------------------------------------
Plan hash value: 3644674915

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU) | Time    |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    79 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_OBJECTS     |     1 |    79 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MY_OBJECTS_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


-- but the index scan plan is not accepted yet!

SQL> select sql_handle, plan_name, enabled, accepted
from dba_sql_plan_baselines
where sql_text like 'select object_name from my_objects where object_id%';  

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SQL_31a282bdb4642130           SQL_PLAN_338n2rqu6889h89405218 YES YES
SQL_31a282bdb4642130           SQL_PLAN_338n2rqu6889hec7825a4 YES NO

So why does optimizer choose the plan that is not accepted?

Answers:

In your test case, your statement did use the baseline plan or full table scan plan. 

The problem is the plan shown to you by “autotrace trace exp” is not the plan actually 

used by your statement.   

Let me show what I mean by executing your test case but displaying the plan by 

querying v$SQL_PLAN using the DBMS_XPLAN package. 

SQL> alter system set cursor_sharing=force;
System altered.

SQL>
SQL> show parameter optimizer_capture_sql_plan_baselines
NAME                                 TYPE                             VALUE                                                                                                    
------------------------------------ -------------------------------- -----------                                                                          
optimizer_capture_sql_plan_baselines boolean                          FALSE                                                                                                    
SQL>
SQL> --Create new table
SQL>
SQL> create table my_objects as select * from dba_objects;

Table created.

SQL>
SQL> -- Run a simple query against the new table and check the plan
SQL>
SQL> select object_name from my_objects where object_id=100;

OBJECT_NAME
----------------------------------------------------
SEQ$                                                                                                                                                                           

SQL>
SQL> select * from table(dbms_xplan.display_cursor(format=>'Typical'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  fu2bja6dgn1yg, child number 0
-------------------------------------
select object_name from my_objects where object_id=:"SYS_B_0"

Plan hash value: 880823944
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   397 (100)|          |
|*  1 |  TABLE ACCESS FULL| MY_OBJECTS |     1 |    40 |   397   (1)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=:SYS_B_0)                                                                                                                                            
18 rows selected.

SQL>
SQL> -- Get the SQL_ID
SQL>
SQL> select sql_id, sql_text from v$sql where sql_text like 'select object_name 
from my_objects where object_id%';
SQL_ID           SQL_TEXT
-------------   -------------------------------------------------------------------
fu2bja6dgn1yg   select object_name from my_objects where object_id=:"SYS_B_0"                                                                                                  
SQL>
SQL> -- Create a baseline for the statement
SQL>
SQL> var num_plans number;
SQL>
SQL> exec :num_plans := dbms_spm.load_plans_from_cursor_cache('fu2bja6dgn1yg');

PL/SQL procedure successfully completed.

SQL>
SQL> select sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                            ENA ACC
------------------------------ ------------------------------------ --- ---
SQL_31a282bdb4642130           SQL_PLAN_338n2rqu6889h89405218       YES YES

SQL>
SQL> -- Created an index on the column
SQL>
SQL> create index my_objects_idx on my_objects(object_id);

Index created.

SQL>
SQL> -- Rerun the query and check the plan
SQL>
SQL> select object_name from my_objects where object_id=100;

OBJECT_NAME
------------------------------------------------------------
SEQ$                                                                                                                                                                           
SQL> select * from table(dbms_xplan.display_cursor(format=>'Typical'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  fu2bja6dgn1yg, child number 0
-------------------------------------
select object_name from my_objects where object_id=:"SYS_B_0"

Plan hash value: 880823944

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   135 (100)|          |
|*  1 |  TABLE ACCESS FULL| MY_OBJECTS |   399 | 31521 |   135   (1)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=:SYS_B_0)
Note
-----
   - SQL plan baseline SQL_PLAN_338n2rqu6889h89405218 used for this statement


22 rows selected.

SQL>
SQL> select sql_handle, plan_name, enabled, accepted
  2  from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                            ENA ACC
------------------------------ ------------------------------------ --- ---
SQL_31a282bdb4642130           SQL_PLAN_338n2rqu6889h89405218       YES YES
SQL_31a282bdb4642130           SQL_PLAN_338n2rqu6889heb45da5a       YES NO


So why did  “autotrace trace exp” show you an index plan? 

The “autotrace trace exp” command executes an explain plan command for your 

SQL statement immediately after the statement has executed. 

It doesn’t actually show you the plan that was used during the previous execute. 

When you run an explain plan command for the same statement text 

(same everything including the literals) it will trigger a hard parse, 

because the cursors generated by an explain plan command are not shareable.  

Since the cursor isn’t shared there is no point in doing the literal replacement that 

would allow the cursor to be shared. 

Therefore the explain plan command does not replace the literals. 

Since literal replace did not occur, there is no SQL plan baseline for the statement. 

Remember, your baseline is for the SQL statement 

SELECT object_name FROM my_objects WHERE object_id=:"SYS_B_0". 

Therefore the explain plan command shows you the index access plan. 

You can demonstrate this behavior in your environment by simple running an 

explain plan command for you statement.