---------------------------------------------------------------------------
第二次查询利用了全表扫描
我们在更直观地来看一下两次sql查询后的硬解析统计情况：
BALLONTT@PROD> select count(*) from t where id='a';
COUNT(*)
----------
10000
BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 2133
parse time elapsed 4463
parse count (total) 54889
parse count (hard) 6579（硬解析数目）
parse count (failures) 52
BALLONTT@PROD> select count(*) from t where id='c';
COUNT(*)
----------
20
BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 2134
parse time elapsed 4464
parse count (total) 54895
parse count (hard) 6580（硬解析数目）
parse count (failures) 52
硬解析数目再次加1
因为数据的巨大差异性，导致了对两次查询有不同的执行计划，这也说明在cursor设置为exact时，两条sql语句如果存在一点不同，就不会共享cursor，而进行两次硬解析。
3.设置为force时
Oracle对输入的SQL值，会将where条件取值自动替换为绑定变量。以后在输入相同的结构SQL语句时，会进行cursor sharing共享游标
BALLONTT@PROD> alter system set cursor_sharing=force;
System altered.
BALLONTT@PROD> show parameter cursor_sharing;
NAME TYPE VALUE
------------------------- ------------ -------
cursor_sharing string FORCE
清除一下share pool中已缓存的cursor
BALLONTT@PROD> alter system flush shared_pool;
System altered.
查看硬解析情况：
BALLONTT@PROD> select count(*) from t where id='b';
COUNT(*)
----------
20000
BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
------------------------------------- ----------
parse time cpu 2163
parse time elapsed 4506
parse count (total) 55097
parse count (hard) 6668
parse count (failures) 52
BALLONTT@PROD> select count(*) from t where id='d';
COUNT(*)
----------
6
BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
----------------------------------------- ----------
parse time cpu 2164
parse time elapsed 4507
parse count (total) 55101
parse count (hard) 6669
parse count (failures) 52
硬解析加1了，这不应该呀！！
BALLONTT@PROD> select sql_text,child_number from v$sql where sql_text like 'select count(*) from t where id%';
SQL_TEXT CHILD_NUMBER
---------------------------------------- ------------
select count(*) from t where id='d' 0
select count(*) from t where id='b' 0
可以看到并没有使用绑定变量，force的设置没有生效。
解决办法：应在设置cursor_sharing前，执行两次下面语句：
alter system flush shared_pool;
BALLONTT@PROD> alter system flush shared_pool;
System altered.
BALLONTT@PROD> alter system flush shared_pool;
System altered.
BALLONTT@PROD> alter system set cursor_sharing=force;
System altered.
设置好了，接着进行sql测试
BALLONTT@PROD> select count(*) from t where id='d';
COUNT(*)
----------
6
BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
-------------------------------------- ----------
parse time cpu 2216
parse time elapsed 4572
parse count (total) 55867
parse count (hard) 6910
parse count (failures) 55
BALLONTT@PROD> select count(*) from t where id='b';
COUNT(*)
----------
20000
BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
--------------------------------------- ----------
parse time cpu 2216
parse time elapsed 4572
parse count (total) 55869
parse count (hard) 691