游标共享cursor sharing是指 shared cursor间的共享，就是重用存储在child cursor中的解析树和执行计划，无需硬解析。
一些OLTP系统在开发阶段未考虑使用绑定变量，系统上线后出现问题，此时改代码代价较大，这种情况下，从8I开始引入了常规游标共享：仅SQL文本中WHERE条件或INSERT时VALUES子句中的具体输入值不同的目标SQL之间共享解析树和执行计划。 可以有效降低硬解析数量同时对应用透明。
当开启常规游标共享时，ORACLE在实际解析目标SQL之间时会使用系统产生的绑定变量来替换目标SQL的SQL文本中WHERE条件或INSERT时VALUES子句中的具体输入值，替换后实际执行的SQL就已经使用绑定变量的入写后的等价SQL，这个替换过程ORACLE自动完成。
目前，ORACLE数据库系统产生的绑定变量命名规则是SYS_B_n.
常规游标共享受参数cursor_sharing控制，此参数的值为EXACT,SIMILAR,FORCE,含义为：
EXACT
是默认值，此时不使用常规游标共享。
SIMILAR
在9I引入，在12C后不被支持，MOS文档116901.1
此时，ORACLE在实际解析目标SQL之间时会使用系统产生的绑定变量来替换目标SQL的SQL文本中WHERE条件或INSERT时VALUES子句中的具体输入值，但是替换后并不一定使用，ORACLE会对认为安全的谓词条件重用执行计划，不安装的则硬解析。所以会出现同一个父游标下多个子游标且子游标中存储的解析树和执行计划可能一样(11G自适应游标共享引入此种情况不适用)。
安全的谓词条件：目标SQL的执行计划不随谓词条件输入变化而变化就是安全的，如主键的等值查询
而对于谓词条件中范围查询，则是不安全的如> >= < <= % between及目标列有直方图的等值查询等
在10G及以后，ORACLE会自动收集直方图统计信息，意味着出现不安全谓词概率大为增加，所以不建议设置为SIMILAR(此参数还可能导致BUG)。
FORCE
此时，ORACLE在实际解析目标SQL之间时会使用系统产生的绑定变量来替换目标SQL的SQL文本中WHERE条件或INSERT时VALUES子句中的具体输入值，并且无条件重用之前硬解析时的解析树和执行计划。(11G自适应游标共享引入后此种情况不适用)。
实验如下：–ORACLE 10.2.0.1版本
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> create index idx_t1 on t1(object_id);
Index created.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS ( ownname=>’BYS’,tabname =>’t1′,estimate_percent=>100,method_opt=>’for all columns size 1′,cascade=>TRUE,no_invalidate=>false);
PL/SQL procedure successfully completed.
SQL> show parameter cursor_sh
NAME TYPE VALUE
———————————— ———– ——————————
cursor_sharing string EXACT
SQL> select object_name from t1 where object_id=1;
no rows selected
SQL> select object_name from t1 where object_id=0;
no rows selected
col sql_text for a80
set linesize 150
select sql_text,sql_id,version_count from v$sqlarea where sql_text like ‘select object_name from t1 where object_id%’;
SQL_TEXT SQL_ID VERSION_COUNT
——————————————————————————– ————- ————-
select object_name from t1 where object_id=1 gkfzppmmk1aca 1
select object_name from t1 where object_id=0 ggk51gg6hmnq8 1
SQL> alter session set cursor_sharing=’SIMILAR’;
Session altered.
SQL> select object_id,object_name from t1 where object_id=0;
no rows selected
SQL> select object_id,object_name from t1 where object_id=1;
no rows selected
SQL> select sql_text,sql_id,version_count,EXECUTIONS from v$sqlarea where sql_text like ‘select object_id,object_name from t1 where object_id%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
——————————————————————————– ————- ————- ———-
select object_id,object_name from t1 where object_id=:”SYS_B_0″ gyyd6hs3hjjry 1 2
此时就均使用硬解析，就出现出现同一个父游标下多个子游标且子游标中存储的解析树和执行计划可能一样
SQL> select count(*) from t1 where object_id between 0 and 3;
COUNT(*)
———-
2
SQL> select count(*) from t1 where object_id between 2 and 4;
COUNT(*)
———-
3
SQL> select sql_text,sql_id,version_count,EXECUTIONS from v$sqlarea where sql_text like ‘select count(*) from t1 where object_id between%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
——————————————————————————– ————- ————- ———-
select count(*) from t1 where object_id between :”SYS_B_0″ and :”SYS_B_1″ cprsstt8gyg9m 2 2
SQL> select plan_hash_value,child_number from v$sql where sql_id=’cprsstt8gyg9m’;
PLAN_HASH_VALUE CHILD_NUMBER
————— ————
2351893609 0
2351893609 1
col plan_table_output for a100
set long 900
set pagesize 100
select * from table(dbms_xplan.display_cursor(‘cprsstt8gyg9m’,0,’advanced’));
PLAN_TABLE_OUTPUT
—————————————————————————————————-
SQL_ID cprsstt8gyg9m, child number 0
————————————-
select count(*) from t1 where object_id between :”SYS_B_0″ and
:”SYS_B_1″
Plan hash value: 2351893609
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_T1 | 2 | 8 | 2 (0)| 00:00:01 |
—————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
3 – SEL$1 / T1@SEL$1
Outline Data
Peeked Binds (identified by position):
————————————–
1 – :SYS_B_0 (NUMBER): 0
2 – :SYS_B_1 (NUMBER): 3
Predicate Information (identified by operation id):
—————————————————
2 – filter(:SYS_B_0<=:SYS_B_1)
3 – access(“OBJECT_ID”>=:SYS_B_0 AND “OBJECT_ID”<=:SYS_B_1)
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=0) COUNT(*)[22]
SQL> select * from table(dbms_xplan.display_cursor(‘cprsstt8gyg9m’,1,’advanced’));
PLAN_TABLE_OUTPUT
—————————————————————————————————-
SQL_ID cprsstt8gyg9m, child number 1
————————————-
select count(*) from t1 where object_id between :”SYS_B_0″ and
:”SYS_B_1”
Plan hash value: 2351893609
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_T1 | 3 | 12 | 2 (0)| 00:00:01 |
—————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
3 – SEL$1 / T1@SEL$1
Outline Data
Peeked Binds (identified by position):
————————————–
1 – :SYS_B_0 (NUMBER): 2
2 – :SYS_B_1 (NUMBER): 4
Predicate Information (identified by operation id):
—————————————————
2 – filter(:SYS_B_0<=:SYS_B_1)
3 – access(“OBJECT_ID”>=:SYS_B_0 AND “OBJECT_ID”<=:SYS_B_1)
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=0) COUNT(*)[22]
52 rows selected.
SQL> alter session set cursor_sharing=’FORCE’;
Session altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from t1 where object_id between 2 and 4;
COUNT(*)
———-
3
SQL> select count(*) from t1 where object_id between 0 and 2;
COUNT(*)
———-
1
SQL> select sql_text,sql_id,version_count,EXECUTIONS from v$sqlarea where sql_text like ‘select count(*) from t1 where object_id between%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
——————————————————————————– ————- ————- ———-
select count(*) from t1 where object_id between :”SYS_B_0″ and :”SYS_B_1″ cprsstt8gyg9m 1 2