Oracle中为了提高sql的执行效率，需要减少硬解析，实现shared cursor共享，最常见的方法是使用绑定变量，但很多时候由于各种原因未能在开发初期使用绑定变量，对于减少硬解析的目的，退而求其次地方法是设置cursor_sharing.
1.准备环境
实验环境
BALLON@PROD> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production
使用脚本插入数据后：
BALLONTT@PROD> desc t;
Name Null Type
----------------------- -------- ----------------
ID VARCHAR2(5)
NAME NUMBER(38)
BALLONTT@PROD> select id,count(*) from t group by id;
ID COUNT(*)
----- ----------
d 6
a 10000
b 20000
c 20
BALLONTT@PROD> create index ind_id on t(id);
Index created.
2.取值为exact时（默认）：
BALLONTT@PROD> show parameter cursor_sharing;
NAME TYPE VALUE
------------------------------------ ----------- -----------------
cursor_sharing string EXACT
BALLONTT@PROD> select count(*) from t where id='b';
COUNT(*)
----------
20000
BALLONTT@PROD> select count(*) from t where id='d';
COUNT(*)
----------
6
BALLONTT@PROD> select sql_text,sql_id,version_count,executions from v$sqlarea
where sql_text like 'select count(*) from t where id=%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
---------------------------------------- ------------- ------------- ----------
select count(*) from t where id='d' b0gfs7u9r55rv 1 1
select count(*) from t where id='b' fqurbumy7bsg6 1 1
可以看到两条查询语句没有使用绑定变量，有各自对应的sql_id，子游标数均为1个。两个sql查询没有任何关系。
查看两次sql的执行计划：
BALLONTT@PROD>select * from table(dbms_xplan.
display_cursor('b0gfs7u9r55rv',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID b0gfs7u9r55rv, child number 0
-------------------------------------
select count(*) from t where id='d'
Plan hash value: 3666266488
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IND_ID | 14 | 28 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
第一次查询利用了索引。
BALLONTT@PROD> select * from table(dbms_xplan.display_cursor('fqurbumy7bsg6',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID fqurbumy7bsg6, child number 0
-------------------------------------
select count(*) from t where id='b'
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| T | 19783 |