cursor_sharing=simiar导致child cursor过多继而导致mutex等待的问题

概述

考虑到cursor_sharing=similar带来的诸多问题，Oracle在11g中废弃改选项(11.1.06和11.2.02以上)，并再12c中彻底删除

废弃原因child cursor下面2种情况，只有文字变量完全一样才共享child cursor尽管执行计划一样

1 谓词字段上有histogram

2 谓词此段无histogram,但用的非等值查询(>,<=,!=, between)

测试验证 1：有histogram

SQL> show parameter cursor_sharing
NAME TYPE VALUE
--------------- -------- ---------
cursor_sharing  string   SIMILAR


SQL> begin
     for r1 in 0..200 loop
        for r2 in 150..200 loop
          execute immediate 'select count(*) from hwz where id='||r1||' and name='''||r2||'''';
        end loop;
     end loop;
end;
/
Elapsed: 00:00:22.16
SQL> /

Elapsed: 00:00:01.72

select SQL_ID,version_count,SQL_TEXT
from v$sqlarea where sql_id='btyb5w7xzrpzf'
SQL_ID        VERSION_COUNT SQL_TEXT
------------- ------------- ----------------------------------------
btyb5w7xzrpzf         13556 select count(*) from hwz where id=:"SYS_
                            B_0" and name=:"SYS_B_1"


SQL> l
  1 SELECT IS_SHAREABLe,count(*)
  2 from v$sql where sql_id ='btyb5w7xzrpzf'
  3* group by IS_SHAREABLe
SQL> /

14-APR-13               Shared Pool Sizing Advice Report

I COUNT(*)
- ----------
Y      10251

1 row selected.

生成了过万的child cursor,内存够用还是很快的

begin
     for r1 in 0..200 loop
        for r2 in 100..200 loop
          execute immediate 'select count(*) from hwz where id='||r1||' and name='''||r2||'''';
        end loop;
     end loop;
end;
/
ORA-01013: user requested cancel of current operation
ORA-06512: at line 4
Elapsed: 00:45:20.87

45分钟没执行完我中断了

SQL> select sid,event,p1,p2,wait_time from v$session where wait_time>0;

 SID EVENT                                  P1         P2  WAIT_TIME
---- ------------------------------ ---------- ---------- ----------
   1 library cache: mutex X         4227586030 1.5032E+11        239


SELECT IS_SHAREABLe,count(*)
from v$sql where sql_id ='btyb5w7xzrpzf'
group by IS_SHAREABLe;
14-APR-13
I COUNT(*)
- ----------
Y      11764
N       1703

I COUNT(*)
- ----------
Y      10194
N       3283

I COUNT(*)
- ----------
Y       8012
N       5474

I COUNT(*)
- ----------
Y        236
N      11819

library cache: mutex X正是硬解析得标识，在不停的添加child cursor
Child cursor太多，过1万的时候，内存不够，ege out了

无histogram的测试

exec dbms_stats.DELETE_TABLE_STATS(user,'HWZ');
exec dbms_stats.gather_table_stats('ORACLE_OCM','HWZ');

begin
     for r1 in 0..200 loop
        for r2 in 100..200 loop
          execute immediate 'select count(*) from hwz where id='||r1||' and name='''||r2||'''';
        end loop;
     end loop;
end;
/
Elapsed: 00:00:01.79


SELECT IS_SHAREABLe,count(*)
from v$sql where sql_id ='btyb5w7xzrpzf'
group by IS_SHAREABLe;

I COUNT(*)
- ----------
Y          1

非等值的查询

begin
     for r1 in 0..200 loop
        for r2 in 100..200 loop
          execute immediate 'select count(*) from hwz where id>'||r1||' and name like '''||r2||'''';
        end loop;
     end loop;
end;
/
时间太长，我中断了...

SELECT IS_SHAREABLe,count(*)
from v$sql where sql_id ='5psxu5unhhm2j'
group by IS_SHAREABLe;

I COUNT(*)
- ----------
Y       4768
N       8400