bind variable在11g 中出现Cursor: mutex S

今天看到一个案例，从10G升级到11GR2后cpu负载迅速上升，很快达到100%,通过OWI显示出现大量“Cursor: mutex S”事件，mutex S是发生在cursor cache上的序列化mutex,它的发生一般出现在两个或以上session尝试parse(hard/soft)相同的sql命令（相同sql_id），可以确认存在大量的解析，后来很快从v$sql发现了一个sql_id包含1000以上的child cursor(也可以通过v$sqlarea)，并县随着时间的流逝，child cursor还在不停的增加，因此sql查询正确的cursor version时间也就越来越长，导致latch contention 对于library cache latches
有一种临时解决方法
DECLARE
SQ_ADD VARCHAR2(100) := '';
BEGIN
execute immediate 'select address from v$sqlarea where sql_id = ''someSQLIDfoo''' into SQ_ADD;
dbms_shared_pool.purge (SQ_ADD||',123454321','C');
END;
用dbms_shared_pool.purge每1分钟flush out from libary cache一次,建立了一个job，sql的address因为一些原因有可能改变(db重启,手动flush等等)，但是sql_id 是根据sql text的hash value 另一种表现形式，在一个数据库通常不会改变，但是DB版本升级会带来sql_id的变化如10G升级11G，正像这个案例一样。
sql_id 转换hash_value Tanel Poder's note

select
    trunc(mod(sum((
        instr('0123456789abcdfghjkmnpqrstuvwxyz',substr(lower(trim('&1')),level,1))-1)*power(32,length(trim('&1'))-level)),power(2,32))) hash_value
    , lower(trim('&1')) sql_id
from
    dual
connect by
    level <= length(trim('&1'))
/

但是谁都不想在数据库中跑一个每分钟执行一次的job解决这样的问题，从那个version count很高的sql 看出是一个包含47个绑定变量的update,child cursor不能重用的原因也是bind mismatch，随后做了10046 ，从trace 文件 中发现了有相同的字段不同数据类型变量加上不同字段组合的原因。之前关于version count我记也记录过http://www.anbob.com/?p=1669
这次记录分析10046 trace的方法。主要是根据oacdty

SQL> var a varchar2(25);
SQL> var b number;
SQL> exec :a:='TABLE';
SQL> exec :b:=10;
SQL> alter session set events '10046 trace name context forever,level 12';
SQL> select * from anbob.obj where object_id<:b and object_type=:a;
SQL> alter session set events '10046 trace name context off';

PARSING IN CURSOR #1 len=61 dep=0 uid=0 oct=3 lid=0 tim=1321000186605824 hv=3134025673 ad='dfa229e8'
select * from anbob.obj where object_id<:b and object_type=:a
END OF STMT
PARSE #1:c=0,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1321000186605821
BINDS #1:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=152 off=0
  kxsbbbfp=7f334cd78590  bln=22  avl=02  flg=05
  value=10
 Bind#1
  oacdty=01 mxl=128(50) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=852 siz=0 off=24
  kxsbbbfp=7f334cd785a8  bln=128  avl=05  flg=01
  value="TABLE"

oacdty  - Datatype code
mxl     - Maximum length of the bind variable value (private maximum length in parentheses)
mxlc    - appears to be the maximum number of characters for the bind variable, but only if the variable uses character length semantics.
mal     - array length
scl     - Scale
pre     - Precision
oacflg  - Special flag indicating bind options
fl2     - second part of oacflg
frm     - Unknown        :(
csi     - Unknown        :(
siz     - Amount of memory to be allocated for this chunk
off     - Offset into this chunk for this bind buffer
kxsbbbfp- Bind address
bln     - Bind buffer length
avl     - actual value length
flg     - bind status flag
value   - Value of the bind variable

SQL> var var1 varchar2(50 char);
SQL> exec :var1 :='TABLE';  
SQL> select * from anbob.obj where object_type=:var1;  
BINDS #1:
kkscoacd
 Bind#0
  oacdty=01 mxl=128(100) mxlc=50 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000010 frm=01 csi=852 siz=128 off=0
  kxsbbbfp=7fdb6c3a88c8  bln=128  avl=05  flg=05
  value="TABLE"

oacdty=
1 VARCHAR2/NVARCHAR2
2 NUMBER(p,s)
8 LONG
12 DATE
etc..
Datatype code read reference ORACLE doc
http://docs.oracle.com/cd/B12037_01/server.101/b10758/sqlqr06.htm

对于为什么10G下运行正常11G出问题，作者提交SR后得到回复
the MOS “Manager Performance Team Americas” stated in the SR:
Its important to note that cursor obsoletion code was removed in version 11. That means we no longer obsolete a parent cursor when it reaches 1024 child cursors.
给我们的启示是做版本升级前，一定要经过测试。