ORACLE uses STOREDOUTLINE to solidify the execution plan -- CURSOR_SHARING

The oracle execution plan storage outline corresponds to the SQL statement one by one. Therefore, if we change the predicate condition in the SQL statement, the storage outline will be ineffective or we need to create a new storage outline to consolidate the execution plan. To avoid this, we can use variables to replace text information.

Without binding variables:

SQL> show userUSER is "EASY1" SQL> select * from tab; TNAME TABTYPECLUSTERID ------------------------------ ------- ---------- T1 TABLESQL> select ol_name, creator from outln. ol $; the SQL> create outline outline1 on select count (*) from t1 where object_id <100; outline has been created. SQL> set autotrace on explainSQL> alter session set use_stored_outlines = true; the session has been changed. SQL> select count (*) from t1 where object_id <100; COUNT (*) ---------- 98 execution Plan ------------------------------------------------------ Plan hash value: 3900446664 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 13 | 3 (0) | 00:00:01 | 1 | sort aggregate | 1 | 13 | * 2 | index range scan | I1 | 4411 | 57343 | 3 (0) | 00:00:01 | identified by operation id: ----------------------------------------------------------------- 2-access ("OBJECT_ID" <100) note ------outline "OUTLINE1" used for this statementSQL> select count (*) from t1 where object_id <200; COUNT (*) ---------- 192 execution Plan hash value: 3900446664 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 13 | 2 (0) | 00:00:01 | 1 | sort aggregate | 1 | 13 | * 2 | index range scan | I1 | 192 | 2496 | 2 (0) | 00:00:01 | identified Predicate Information (identified by operation id): required 2-access ("OBJECT_ID" <200) Note ------dynamic sampling used for this statement (level = 2)

It can be seen that, if no variable is bound, the storage outline must be exactly matched with SQL text to play a role; 

When binding variables:

SQL> drop outline outline1; the outline has been deleted. SQL> create outline outline2 on select count (*) from t1 where object_id <: var; the outline has been created. SQL> var v number; SQL> exec: v: = 300; PL/SQL process completed successfully. SQL> select count (*) from t1 where object_id <: v; COUNT (*) ---------- 286 execution Plan ------------------------------------------------------ Plan hash value: 3900446664 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | -------------------------------------------------------------------------- | 0 | select statement | 13 | 3 (0) | 00:00:01 | 1 | sort aggregate | 1 | 13 | * 2 | index range scan | I1 | 4580 | 59540 | 3 (0) | 00:00:01 | identified by operation id: --------------------------------------------------------------- 2-access ("OBJECT_ID" <TO_NUMBER (: V )) note ------dynamic sampling used for this statement (level = 2) SQL> Var number; SQL> exec: var: = 300; PL/SQL process completed successfully. SQL> select count (*) from t1 where object_id <: var; COUNT (*) ---------- 286 execution Plan ------------------------------------------------------ Plan hash value: 3900446664 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 13 | 3 (0) | 00:00:01 | 1 | sort aggregate | 1 | 13 | * 2 | index range scan | I1 | 4411 | 57343 | 3 (0) | 00:00:01 | identified by operation id: required 2-access ("OBJECT_ID" <TO_NUMBER (: VAR) Note ------outline "OUTLINE2" used for this statement

SQL> select count (*) from t1 where object_id <200; COUNT (*) ---------- 192 execution Plan ------------------------------------------------------ Plan hash value: 3900446664 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 13 | 2 (0) | 00:00:01 | 1 | sort aggregate | 1 | 13 | * 2 | index range scan | I1 | 192 | 2496 | 2 (0) | 00:00:01 | identified Predicate Information (identified by operation id): required 2-access ("OBJECT_ID" <200) Note ------dynamic sampling used for this statement (level = 2)

When using the bound variables to create a storage outline, to make the storage Outline take effect, the SQL statement must use the bound variables, and the variable name must be consistent with the variable name when the storage outline is created;

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 userUSER is "SYS" SQL> show parameter cursor_sharNAME TYPE VALUE ---------------------------------------- cursor_sharing string EXACTSQL> alter system set cursor_sharing = force; the system has changed.

Session 2

SQL> show userUSER is "EASY1" SQL> select ol_name, creator, SQL _text from outln. ol $; OL_NAME CREATOR ------------------------------ ---------------------------- SQL _TEXT--------------------------------------------------------------------------------OUTLINE2 EASY1select count (*) from t1 where object_id <: varSQL> alter session set create_stored_outlines = true; session changed. SQL> select count (*) from t1 where object_id <500; COUNT (*) -------- 478SQL> 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 extends SQL _TEXT--------------------------------------------------------------------------------SYS_OUTLINE_14010314202705203 EASY1SELECT/* OPT_DYN_SAMP * // * + ALL_ROWS returns NO_PARALLEL (SAMPLESUBOUTLINE2 EASY1select count (*) from t1 where object_id <: varSYS_OUTLIN Revoke EASY1SELECT/* OPT_DYN_SAMP * // * + ALL_ROWS opt_param ('parallel _ execution_enabled ', 'ol _ name creator extends authorization SQL _TEXT--------------------------------------------------------------------------------SYS_OUTLINE_14010314202705001 EASY1select count (*) from t1 where object_id <: "SYS_ B _0" -- the system automatically creates outlineSQL> alter session set use _ Stored_outlines = true; the session has been changed. SQL> set autotrace on explainSQL> select count (*) from t1 where object_id <600; COUNT (*) ---------- 566 execution Plan ---------------------------------------------------------- Plan hash value: 3900446664 Bytes | 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 | identified by operation id: --------------------------------------------------------------- 2-access ("OBJECT_ID" <600) Note ------dynamic sampling used for this stat Ement (level = 2) SQL> set autotrace offSQL> select count (*) from t1 where object_id <600; COUNT (*) ---------- 566SQL> select * from table (dbms_xplan.display_cursor (); PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL _ ID3y9v7qyqns9ws, child number 1 explain select count (*) from t1 where object_id <: "SY S_ B _0 "Plan hash value: 3900446664 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | percent | 0 | select statement | 3 (100) | 1 | sort aggregate | 1 | 13 | PLAN_TABLE_OUTPUT percent ---------------------------------------------------------------- ---------------------------------- | * 2 | index range scan | I1 | 4411 | 57343 | 3 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): when 2-access ("OBJECT_ID" <: SYS_ B _0) Note ------outline "SYS_OUTLINE_14010314202705001" used for this statement -- the automatically generated storage outline is used, However, autotrace or explain cannot indicate this. It may be that 23 rows have been selected for a bugPLAN_TABLE_OUTPUT partition.

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.