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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/751051/viewspace-731724/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/751051/viewspace-731724/