oracle 12c自适应游标,11G自适应游标共享的不足

自适应游标共享Adaptive Cursor Sharing是Oracle 11g的新特性之一，主要用来解决以前版本中由于绑定变量窥探导致SQL语句无法获得最佳执行计划的缺陷，即能够对效率低下的游标(子游标)进行自动识别而选择最佳的执行计划，对于它的有点本文不再描述，这里主要讲下它的缺点，或者说改进下自适应游标共享的算法。

表数据简介：

--父表记录数,id列是唯一的

SELECT COUNT(id) FROM m504--703714

--子表记录

SELECT COUNT(*) FROM m505

--32445778

--根据linkid看子表数据分布

SELECT linkid,COUNT(*)

FROM m505

GROUP BY linkid--m504.id=m505.linkid

ORDER BY Count(1) DESC

--得到的结果，最多有20000条记录，最少几条记录，总体而言，各个linkid的数据量确实有较大差异，但不可否则，通过linkid所在的索引得到需要的记录数是最优的执行计划，不可能走全表扫描，因为m505的记录量过大，即便是最多的20000多条记录相对于3000多万的总记录数，走索引还是最优选择

--M505在linkid上建有索引，被执行的SQL是：

select linkid,index_value from m505 t2 where linkid=:"SYS_B_0"

通过v$sqlarea可以得到v$sqlarea.version_count达到3245，写个过程做个试验做个对比：

CREATE OR REPLACE PROCEDURE p_tmp_m505 IS

TYPE arr_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;

TYPE arr_num IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

a_date    arr_str;

a_ind_val arr_num;

v_cnt     PLS_INTEGER := 0;

BEGIN

FOR c IN (SELECT id

FROM (SELECT id

FROM m504

WHERE rownum < 100000

ORDER BY dbms_random.VALUE())--随机有助于实验更接近于真实环境

WHERE rownum <= 1001) LOOP

EXECUTE IMMEDIATE 'select count(*) from (select linkid,index_value from m505 t2 where linkid=' ||

c.id || ')'

INTO v_cnt;

END LOOP;

END p_tmp_m505;

SQL_TEXT

SHARABLE_MEM

VERSION_COUNT

LOADS

EXECUTIONS

PARSE_CALLS

CPU_TIME

ELAPSED_TIME

CURSOR_SHARING

select/*force*/count(*) from (select linkid,index_value from m505 t2 where linkid=:"SYS_B_0" )

22885

1

1

2803

2803

2099682

200872321

force

select/*similar*/count(*) from (select linkid,index_value from m505 t2 where linkid=:"SYS_B_0")

57264455

2770

2770

2803

2803

7054947

169317810

similar

statistics_name

exact

force

CPU used when call started

739

248

CPU used by this session

739

248

session cursor cache count

12

31

parse time cpu

126

18

parse time elapsed

108

32

parse count (hard)

2770

4

execute count

2816

2816

结果非常明显，用绑定变量窥视大部分时间都会hard parse！但是硬解析的结果都是一样的，走linkid所在列的索引，所以这个时候其实根本没必要做绑定变量窥视，这样可以避免大量的硬解析，解决办法就是对这个session设置cursor_sharing='force'即可。

refrenced by:http://hi.baidu.com/fly_ch/blog/item/d5c1307e27eadb260dd7da38.html