Skip to Main Content
  • Questions
  • how SPM works with cursor_sharing=force?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, lin.

Asked: November 07, 2017 - 11:43 pm UTC

Last updated: January 25, 2021 - 2:57 pm UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hello Tom

The post https://blogs.oracle.com/optimizer/how-do-adaptive-cursor-sharing-and-sql-plan-management-interact explains the interaction between cursor sharing and SPM quite clear. But I met some unexpected results if I set the cursor_sharing parameter to "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?

and we said...

Hi

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.

Rating

  (7 ratings)

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

Comments

on 12c with cursor_sharing=force

Rajeshwaran Jeyabal, November 20, 2017 - 6:13 am UTC

<i>....
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. 
....</i>

Team,

Here is my output from Oracle 11g(11.2.0.4) and Oracle 12c(12.2.0.1) database.
 
Oracle 11g database got this:

rajesh@ORA11G> alter session set cursor_sharing=force;

Session altered.

rajesh@ORA11G>
rajesh@ORA11G> create table t as select owner,object_name,object_type,object_id,created from all_objects;

Table created.

rajesh@ORA11G> set serveroutput off
rajesh@ORA11G>
rajesh@ORA11G> select * from t where object_id = 100;

OWNER                     OBJECT_NAME               OBJECT_TYPE                OBJECT_ID CREATED
------------------------- ------------------------- ------------------------- ---------- -----------
SYS                       ORA$BASE                  EDITION                          100 09-OCT-2013

rajesh@ORA11G> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
SQL_ID  7nvpfr9hnpfw4, child number 0
-------------------------------------
select * from t where object_id = :"SYS_B_0"

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   190 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     8 |   536 |   190   (1)| 00:00:03 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=:SYS_B_0)

Note
-----
   - dynamic sampling used for this statement (level=2)


22 rows selected.

rajesh@ORA11G> column sql_id new_value sqlid
rajesh@ORA11G> select sql_id from v$sql where lower(sql_text) like 'select * from t where object_id%';

SQL_ID
-------------
7nvpfr9hnpfw4

rajesh@ORA11G> variable x number
rajesh@ORA11G> exec :x := dbms_spm.load_plans_from_cursor_cache(sql_id=>'&sqlid');

PL/SQL procedure successfully completed.

rajesh@ORA11G> print x;

         X
----------
         1

rajesh@ORA11G> select sql_handle,sql_text,plan_name,origin,parsing_schema_name
  2  from dba_sql_plan_baselines;

SQL_HANDLE                SQL_TEXT             PLAN_NAME                      ORIGIN         PARSING_SC
------------------------- -------------------- ------------------------------ -------------- ----------
SQL_dcb7086960a25579      select * from t wher SQL_PLAN_dtds8d5ha4pbt94ecae5c MANUAL-LOAD    RAJESH
                          e object_id = :"SYS_
                          B_0"


rajesh@ORA11G> create index t_idx on t(object_id);

Index created.

rajesh@ORA11G> select * from t where object_id = 100;

OWNER                     OBJECT_NAME               OBJECT_TYPE                OBJECT_ID CREATED
------------------------- ------------------------- ------------------------- ---------- -----------
SYS                       ORA$BASE                  EDITION                          100 09-OCT-2013

rajesh@ORA11G> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
SQL_ID  7nvpfr9hnpfw4, child number 0
-------------------------------------
select * from t where object_id = :"SYS_B_0"

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   190 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |   564 | 37788 |   190   (1)| 00:00:03 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=:SYS_B_0)

Note
-----
   - SQL plan baseline SQL_PLAN_dtds8d5ha4pbt94ecae5c used for this statement


22 rows selected.

rajesh@ORA11G> select sql_handle,sql_text,plan_name,origin,parsing_schema_name
  2  from dba_sql_plan_baselines;

SQL_HANDLE                SQL_TEXT             PLAN_NAME                      ORIGIN         PARSING_SC
------------------------- -------------------- ------------------------------ -------------- ----------
SQL_dcb7086960a25579      select * from t wher SQL_PLAN_dtds8d5ha4pbt94ecae5c MANUAL-LOAD    RAJESH
                          e object_id = :"SYS_
                          B_0"

SQL_dcb7086960a25579      select * from t wher SQL_PLAN_dtds8d5ha4pbtae82cf72 AUTO-CAPTURE   RAJESH
                          e object_id = :"SYS_
                          B_0"


rajesh@ORA11G> explain plan for
  2  select *
  3  from t
  4  where object_id = 100;

Explained.

rajesh@ORA11G> @xplan

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

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

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

   2 - access("OBJECT_ID"=100)

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

rajesh@ORA11G>

Oracle 12c database got this:

rajesh@ORA12C> alter session set cursor_sharing=force;

Session altered.

rajesh@ORA12C>
rajesh@ORA12C> create table t as select owner,object_name,object_type,object_id,created from all_objects;

Table created.

rajesh@ORA12C> set serveroutput off
rajesh@ORA12C> select * from t where object_id = 100;

OWNER                     OBJECT_NAME               OBJECT_TYPE                OBJECT_ID CREATED
------------------------- ------------------------- ------------------------- ---------- -----------
SYS                       SEQ$                      TABLE                            100 08-MAR-2017

rajesh@ORA12C> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
SQL_ID  4ashpqnhk8u0u, child number 0
-------------------------------------
select * from t where object_id = 100

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   191 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    62 |   191   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100)


18 rows selected.

rajesh@ORA12C> column sql_id new_value sqlid
rajesh@ORA12C> select sql_id from v$sql where lower(sql_text) like 'select * from t where object_id%';

SQL_ID
-------------
4ashpqnhk8u0u

rajesh@ORA12C> variable x number
rajesh@ORA12C> exec :x := dbms_spm.load_plans_from_cursor_cache(sql_id=>'&sqlid');

PL/SQL procedure successfully completed.

rajesh@ORA12C> print x;

         X
----------
         1

rajesh@ORA12C> select sql_handle,sql_text,plan_name,origin,parsing_schema_name
  2  from dba_sql_plan_baselines;

SQL_HANDLE                SQL_TEXT             PLAN_NAME                      ORIGIN                     PARSING_SC
------------------------- -------------------- ------------------------------ ----------------------------- ----------
SQL_4b0104423935af55      select * from t wher SQL_PLAN_4q08488wmbbup94ecae5c MANUAL-LOAD-FROM-CURSOR-CACHE RAJESH
                          e object_id = 100


rajesh@ORA12C> create index t_idx on t(object_id);

Index created.

rajesh@ORA12C> select * from t where object_id = 100;

OWNER                     OBJECT_NAME               OBJECT_TYPE                OBJECT_ID CREATED
------------------------- ------------------------- ------------------------- ---------- -----------
SYS                       SEQ$                      TABLE                            100 08-MAR-2017

rajesh@ORA12C> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
SQL_ID  4ashpqnhk8u0u, child number 1
-------------------------------------
select * from t where object_id = 100

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   191 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    62 |   191   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100)

Note
-----
   - SQL plan baseline SQL_PLAN_4q08488wmbbup94ecae5c used for this statement


22 rows selected.

rajesh@ORA12C> select sql_handle,sql_text,plan_name,origin,parsing_schema_name
  2  from dba_sql_plan_baselines;

SQL_HANDLE                SQL_TEXT             PLAN_NAME                      ORIGIN                     PARSING_SC
------------------------- -------------------- ------------------------------ ----------------------------- ----------
SQL_4b0104423935af55      select * from t wher SQL_PLAN_4q08488wmbbup94ecae5c MANUAL-LOAD-FROM-CURSOR-CACHE RAJESH
                          e object_id = 100

SQL_4b0104423935af55      select * from t wher SQL_PLAN_4q08488wmbbupa0b930be AUTO-CAPTURE               RAJESH
                          e object_id = 100


rajesh@ORA12C> explain plan for
  2  select *
  3  from t
  4  where object_id = 100;

Explained.

rajesh@ORA12C> @xplan

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    62 |   191   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    62 |   191   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100)

Note
-----
   - SQL plan baseline "SQL_PLAN_4q08488wmbbup94ecae5c" used for this statement

17 rows selected.

rajesh@ORA12C>
Questions:
1) Why setting cursor_sharing=force in 12c doesn't replace literals with bind variables?

2) The explain plan command output from 11g and 12c were different after the SPM configuration. 
 this seems to be that from 12c and above the cursors generated by the explain plan command are **Sharable**? is that correct?

3) we have set the "optimizer_capture_sql_plan_baselines" = FALSE in both 11g and 12c. 
 could you help me to understand why there is an entry in dba_sql_plan_baselines with ORIGIN = 'AUTO-CAPTURE' after the second execution of the cursor?
 I was able to understand the ORIGIN = 'MANUAL-LOAD' part, but not the 'AUTO-CAPTURE' part. Please explain on this


Maria Colgan
November 20, 2017 - 9:13 pm UTC

Hi Rajeshwaran,

Let me address each of your questions.

1) "Why setting cursor_sharing=force in 12c doesn't replace literals with bind variables?"

There is no known issues with setting cursor_sharing=force in 12c. In my 12.2.0.1 environment it works just as expected:
SQL> show parameter cursor_sharing

NAME         TYPE  VALUE
------------------------------------ ----------- -------
cursor_sharing        string  EXACT

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> show parameter cursor_sharing

NAME         TYPE  VALUE
------------------------------------ ----------- -------
cursor_sharing        string  FORCE

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)


2) "The explain plan command output from 11g and 12c were different after the SPM configuration. This seems to be that from 12c and above the cursors generated by the explain plan command are **Sharable**? is that correct?"

No, the cursors generated by the explain plan command are not **sharable** in 12c. They are still marked unsharable. If you execute your explain plan command 3 times and then query v$sql what you will see is the follows:

SQL> SELECT sql_id, sql_text, executions, child_number
  2  FROM   v$sql
  3  WHERE sql_text like '%explain plan for  select * %';

SQL_ID       SQL_TEXT         EXECUTIONS CHILD_NUMBER
------------- -------------------------------------- ---------- ------------
bhnjby76zp21m explain plan for select * from t wher       1     0
       e object_id = 100

bhnjby76zp21m explain plan for select * from t wher       1     1
       e object_id = 100

bhnjby76zp21m explain plan for select * from t wher       1     2
       e object_id = 100


There distinct cursors one for each execution.

The only reason you see the explain plan output showing the SQL plan baseline was used in your 12c environment is because you didn't do the literal replacement. So, your SQL plan baseline was created for the SQL statement with the literal value "select * from t where object_id = 100". Hence the explain plan found a match.

3) we have set the "optimizer_capture_sql_plan_baselines" = FALSE in both 11g and 12c. could you help me to understand why there is an entry in dba_sql_plan_baselines with ORIGIN = 'AUTO-CAPTURE' after the second execution of the cursor?

Once a SQL plan baseline exists for a SQL statement, any new plan found for that SQL statement during a hard parse will be automatically added to the SQL plan baseline with the ORIGIN of "AUTO-CAPTURE" and the ACCEPTED attribute set to NO. The automatic additional of new plans to an existing SQL plan baseline is not controlled by the parameter "optimizer_capture_sql_plan_baselines".

The parameter "optimizer_capture_sql_plan_baselines" only controls the automatic creation of a SQL plan baseline for any repeatable SQL statement.

on 12c with cursor_sharing=force

Rajeshwaran Jeyabal, November 21, 2017 - 2:56 am UTC

Thanks for answering all the questions.

#3 - once we posted that question realized that, the initially loaded plans from the library cache is added as "Accepted" plan and part of the baselines, during the SQL execution if CBO produces new plans, they will be added to plan history as "Un-Accepted" plans and will be evolved later and if it seems to be performing better than the existing baseline plans - they those "Un-Accepted" plans will be moved into plan baselines.

#1 - when cursor_sharing = force, dont see the literal replaced to system generated bind variables.
demo@ORA12C> @script.sql
demo@ORA12C> column con_id noprint
demo@ORA12C> column banner format a40 trunc
demo@ORA12C> select * from v$version;

BANNER
----------------------------------------
Oracle Database 12c Enterprise Edition R
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for 64-bit Windows: Version 12.2.0.1
NLSRTL Version 12.2.0.1.0 - Production

demo@ORA12C> set serveroutput off
demo@ORA12C> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
demo@ORA12C> select count(*) from emp where deptno = 10;

  COUNT(*)
----------
         3

demo@ORA12C> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  693n0au6j0g4g, child number 0
-------------------------------------
select count(*) from emp where deptno = 10

Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     5 |    15 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("DEPTNO"=10)


19 rows selected.

demo@ORA12C> alter session set cursor_sharing=force;

Session altered.

demo@ORA12C> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      FORCE
demo@ORA12C> select count(*) from emp where deptno = 10;

  COUNT(*)
----------
         3

demo@ORA12C> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  693n0au6j0g4g, child number 0
-------------------------------------
select count(*) from emp where deptno = 10

Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     5 |    15 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("DEPTNO"=10)


19 rows selected.

demo@ORA12C>
demo@ORA12C>
demo@ORA12C>

Maria Colgan
November 22, 2017 - 3:07 am UTC

Hi Rajeshwaran,

I believe the behavior you have encountered is a bug because the parameter CURSOR_SHARING is part of the sharing conditions, so setting it to a different value should prevent an existing cursor from being shared and force us to build a new cursor with a system generated bind.

I've filed bug 27155413 for this case.

However, it is possible that one of the developers has out smarted me here. By identifying an existing cursor with an exact text match in the shared pool, we are saving a hard parse, so it's possible to explain this behavior as an optimization!

I'll update this post once I get a verdict from development on which it is, bug or optimization.

on 12c with cursor_sharing=force

Rajeshwaran Jeyabal, November 21, 2017 - 3:01 am UTC

#1 - on cursor_sharing=force, if the second execution of the SQL is different from the first sql (sql_id different from the first execution), then literals replaced with system generated bind variables. kindly advice.

demo@ORA12C> @script.sql
demo@ORA12C> set echo on
demo@ORA12C> column con_id noprint
demo@ORA12C> column banner format a40 trunc
demo@ORA12C> select * from v$version;

BANNER
----------------------------------------
Oracle Database 12c Enterprise Edition R
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for 64-bit Windows: Version 12.2.0.1
NLSRTL Version 12.2.0.1.0 - Production

demo@ORA12C> set serveroutput off
demo@ORA12C> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
demo@ORA12C> select count(*) from emp where deptno = 10;

  COUNT(*)
----------
         3

demo@ORA12C> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  693n0au6j0g4g, child number 0
-------------------------------------
select count(*) from emp where deptno = 10

Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     5 |    15 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("DEPTNO"=10)


19 rows selected.

demo@ORA12C> alter session set cursor_sharing=force;

Session altered.

demo@ORA12C> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      FORCE
demo@ORA12C> select count(*) from emp where deptno = 10;

  COUNT(*)
----------
         3

demo@ORA12C> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  693n0au6j0g4g, child number 0
-------------------------------------
select count(*) from emp where deptno = 10

Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     5 |    15 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("DEPTNO"=10)


19 rows selected.

demo@ORA12C>
demo@ORA12C> select count(*) from emp e2 where deptno = 10;

  COUNT(*)
----------
         3

demo@ORA12C> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  09fv1mz8phact, child number 0
-------------------------------------
select count(*) from emp e2 where deptno = :"SYS_B_0"

Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     5 |    15 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("DEPTNO"=:SYS_B_0)


19 rows selected.

demo@ORA12C>

thanks !

Rajeshwaran Jeyabal, November 22, 2017 - 12:44 pm UTC

Thanks Maria.

on the other note, is this developer blog ( http://blogs.oracle.com/Developer ) got removed?
Maria Colgan
November 22, 2017 - 6:22 pm UTC

Hi Rajeshwaran,

Yes, the original developer blog that Gerald Vendzl and I wrote has been removed and replaced with the developers blog. The correct URL for the developers blog is http://blogs.oracle.com/Developers Here you will find blog posts from an arrange of Oracle authors.

If you want to follow just Gerald and I, you should check out our independent blogs at https://geraldonit.com/ and https://sqlmaria.com/ .

Thanks,
Maria

on blogs

Rajeshwaran Jeyabal, November 23, 2017 - 2:40 am UTC

thanks, I have been following you on your individual blogs.

However can you update the correct blog entry in the

https://sqlmaria.com/about-maria/

as blogs.oracle.com/developers rather than blogs.oracle.com/developer ?

on 12c with cursor_sharing=force

Rajeshwaran Jeyabal, November 23, 2017 - 6:41 am UTC

....
I believe the behavior you have encountered is a bug because the parameter CURSOR_SHARING is part of the sharing conditions, so setting it to a different value should prevent an existing cursor from being shared and force us to build a new cursor with a system generated bind.
....


Thanks Maria - Agreed. this sounds like a bug rather than optimization.

Here is my test case to demonstrate this as a bug!

Between the sql execution, we changed the value of HASH_AREA_SIZE parameter and that forced the optimizer to generate a new child cursor for the next execution.

demo@ORA12C> set serveroutput off
demo@ORA12C> select count(*) from emp where deptno = 10;

  COUNT(*)
----------
         3

demo@ORA12C> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  693n0au6j0g4g, child number 0
-------------------------------------
select count(*) from emp where deptno = 10

Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     5 |    15 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("DEPTNO"=10)


19 rows selected.

demo@ORA12C> alter session set hash_area_size=15000;

Session altered.

demo@ORA12C> select count(*) from emp where deptno = 10;

  COUNT(*)
----------
         3

demo@ORA12C> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  693n0au6j0g4g, child number 1
-------------------------------------
select count(*) from emp where deptno = 10

Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     5 |    15 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("DEPTNO"=10)


19 rows selected.

demo@ORA12C>


However now if we change the value of CURSOR_SHARING parameter between the sql execution, we dont see any new child cursor generated.

demo@ORA12C> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
demo@ORA12C> set serveroutput off
demo@ORA12C> select count(*) from emp where deptno = 10;

  COUNT(*)
----------
         3

demo@ORA12C> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  693n0au6j0g4g, child number 0
-------------------------------------
select count(*) from emp where deptno = 10

Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     5 |    15 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("DEPTNO"=10)


19 rows selected.

demo@ORA12C> alter session set cursor_sharing=force;

Session altered.

demo@ORA12C> select count(*) from emp where deptno = 10;

  COUNT(*)
----------
         3

demo@ORA12C> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  693n0au6j0g4g, child number 0
-------------------------------------
select count(*) from emp where deptno = 10

Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     5 |    15 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("DEPTNO"=10)


19 rows selected.

demo@ORA12C>


so this seems to be a bug for me, kindly advice - if any flaw/misunderstanding with my test case and results.

flush?

F, January 22, 2021 - 3:30 pm UTC

"However now if we change the value of CURSOR_SHARING parameter between the sql execution, we dont see any new child cursor generated."

Please flush the shared pool before changing the cursor_sharing parameter to get rid of old plans/cursors.
Chris Saxon
January 25, 2021 - 2:57 pm UTC

As Maria said, changing the value of CURSOR_SHARING should generate a new child cursor.

More to Explore

Performance

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