ORACLE uses STOREDOUTLINE to solidify the execution plan

Welcome to the Oracle community forum and interact with 2 million technical staff. Of course, we can also use the curosr_sharing parameter to enhance the applicability of the storage outline, but there is a limitation here, that is, the cursor_sharing parameter only affects the storage outline created through the CREATE_STORED_OUTLINES parameter. The official documents are described as follows: SeeA

Welcome to the Oracle community forum and interact with 2 million technical staff> of course, we can also use the curosr_sharing parameter to enhance the applicability of the storage outline, but there is a limitation here, that is, the cursor_sharing parameter only affects the storage outline created through the CREATE_STORED_OUTLINES parameter. The official documents are described as follows: See

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

Of course, we can also use the curosr_sharing parameter to enhance the applicability of the storage outline, but there is a restriction here, that is, the cursor_sharing parameter only affects the storage outline created through the CREATE_STORED_OUTLINES parameter. The official documents are described as follows:
See Also: Oracle Database can allow similar statements to share SQL by replacing literals with system-generated bind variables. this works with plan stability if the outline was generated using the CREATE_STORED_OUTLINES parameter, not the create outline statement. also, the outline must have been created with the CURSOR_SHARING parameter set to FORCE or SIMILAR, and the parameter must also set to FORCE or SIMILAR when attempting to use the outline.
First, in session1:
SQL> show user
USER is "SYS"
SQL> show parameter cursor_shar
NAME TYPE VALUE
-----------------------------------------------------------------------------
Cursor_sharing string EXACT
SQL> alter system set cursor_sharing = force;
The system has been changed.
Session 2
SQL> show user
The USER is EASY1"
SQL> select ol_name, creator, SQL _text from outln. ol $;
OL_NAME CREATOR
------------------------------------------------------------
SQL _TEXT
--------------------------------------------------------------------------------
OUTLINE2 EASY1
Select count (*) from t1 where object_id <: var
SQL> alter session set create_stored_outlines = true;
The session has been changed.
SQL> select count (*) from t1 where object_id <500;
COUNT (*)
----------
478
SQL> alter session set create_stored_outlines = false;
The session has been changed.
SQL> select ol_name, creator, SQL _text from outln. ol $;
OL_NAME CREATOR
------------------------------------------------------------
SQL _TEXT
--------------------------------------------------------------------------------
SYS_OUTLINE_14010314202705203 EASY1
SELECT/* OPT_DYN_SAMP * // * + ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL (SAMPLESUB
OUTLINE2 EASY1
Select count (*) from t1 where object_id <: var
SYS_OUTLINE_14010314202706005 EASY1
SELECT/* OPT_DYN_SAMP * // * + ALL_ROWS opt_param ('parallel _ execution_enabled ','
OL_NAME CREATOR
------------------------------------------------------------
SQL _TEXT
--------------------------------------------------------------------------------
SYS_OUTLINE_14010314202705001 EASY1
Select count (*) from t1 where object_id <: "SYS_ B _0" -- the system automatically creates an outline
SQL> alter session set use_stored_outlines = true;
The session has been changed.
SQL> set autotrace on explain
SQL> select count (*) from t1 where object_id <600;
COUNT (*)
----------
566
Execution Plan
----------------------------------------------------------
Plan hash value: 3900446664
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 1 | 13 | 3 (0) | 00:00:01 |
| 1 | sort aggregate | 1 | 13 |
| * 2 | index range scan | I1 | 566 | 7358 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("OBJECT_ID" <600)
Note
-----
-Dynamic sampling used for this statement (level = 2)
SQL> set autotrace off
SQL> select count (*) from t1 where object_id <600;
COUNT (*)
----------
566
SQL> select * from table (dbms_xplan.display_cursor ());
PLAN_TABLE_OUTPUT
Bytes ----------------------------------------------------------------------------------------------------
SQL _ID3y9v7qyqns9ws, child number 1
-------------------------------------
Select count (*) from t1 where object_id <: "SYS_ B _0"
Plan hash value: 3900446664
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 3 (100) |
| 1 | sort aggregate | 1 | 13 |
PLAN_TABLE_OUTPUT
Bytes ----------------------------------------------------------------------------------------------------
| * 2 | index range scan | I1 | 4411 | 57343 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("OBJECT_ID" <: SYS_ B _0)
Note
-----
-Outline "SYS_OUTLINE_14010314202705001" used for this statement -- an automatically generated storage outline has been used, but autotrace or explain cannot indicate this, which may be a bug.
PLAN_TABLE_OUTPUT
Bytes ----------------------------------------------------------------------------------------------------
23 rows have been selected.
Note: When Oracle Database creates an outline, plan stability examines the optimization results using the same data used to generate the execution plan. that is, Oracle Database uses the input to the execution plan to generate an outline, and not the execution plan itself.

[1] [2] [3]