Oracle uses stored outline 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 user
The USER is EASY1"
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
-----------------------------------------------
T1 TABLE

SQL> select ol_name, creator from outln. ol $;

Unselected row

SQL> create outline outline1 on select count (*) from t1 where object_id <100;

The outline has been created.

SQL> set autotrace on explain
SQL> 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

--------------------------------------------------------------------------
| 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 | 4411 | 57343 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

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

2-access ("OBJECT_ID" <100)

Note
-----
-Outline "OUTLINE1" used for this statement

SQL> select count (*) from t1 where object_id <200;

COUNT (*)
----------
192

Execution Plan
----------------------------------------------------------
Plan hash value: 3900446664

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------

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

2-access ("OBJECT_ID" <200)

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

1 2 3 Next Page