sql version count引发cursor:pin s wait x及library cache latch library cache lock

背景

   某Q友在其生产系统AWR发现library cache latch及library cache lock及cursor:pin s wait x等待事件非常严重，经排查发生某些底层SQL的多版本现象非常严重，最高达到4000多左右
结论
   1,oracle为了控制某个SQL的子游标个数不止过高过多，采用绑定变量分级机制，即绑定变量的长度（字节）在一个范围内，就隶属一个子游标，否则就会新创建一个子游标
   2,可以通过v$sql_bind_metadata获取上述绑定变量分量的具体分界点
   3,v$sql_shared_cursor可以分析某SQL产生多个子游标的原因，然后进行不同的原因进行分析
   4,进一步基于第3步分析（这里子游标不能共享的原因是bind_mismatch）,在MOS查找相关文档 ，发现
      High Version Count Due To BIND_MISMATCH [ID 336268.1]
   5，基于第4步，我们已经知道多版本的产生原因，现在要解决这个问题，因为是生产系统， 可以采用10503事件进行暂时控制
      但不能成功


   6，基于第5步，就要分析为何事件配置不成功，继续查看MOS，以关键字进行查找，event 10503
      发现文章：
         Bug 10274265 - Event 10503 does not work at session level [ID 10274265.8]


    7，基于第6步，要思考下，即便配置10503事件成功了，毕竟是生产系统，会不会引发其它的一些问题或BUG呢，还是老办法，查看MOS
         
event可能导致ORA-600: [kxspoac : EXL 1]错误


ORA-600: [kxspoac : EXL 1] Occuring During Query If Event 10503 Is Set [ID 810194.1]






    8，由上述引发一些思考：






--由此可见ORACLE知识联系是非常复杂的，要调整一个参数，必须要从全方位考虑与分析


1，配置参数的命令是什么
2，如果配置参数不成功，它为何不成功，可能也有相关的MOS文章进行介绍
3，如果配置参数成功，会引发什么其它的问题，也要进行全面考虑，否则解决旧问题，引发新问题


引申一下，可见应用人员设计表以及开发人员，在编写代码时，也要考虑到这点，比如：不要在声明变量长度时
过分声明，比如本来20字节就够了，你声明了1000个字节，而且每个开发人员声明长度不同
，这样就会引发本文的子游标不能共共享的问题




4，基于关镇字查看是否是相关的BUG


5，进一步引证了TOP 5等待事件有时未必是因，很可能它只是一个果，而且它的原因不在本时间点采样的AWR（或者说不能很明白得看出来）
   也就是说需要你查看此时间点之前的AWR进行分析

分析

SQL> select sql_id,sql_text from v$sql where lower(sql_text) like '%select pos#,intcol#%';


SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
1qb4fr2qc8jsu select sql_id,sql_text from v$sql where lower(sql_text) like '%select pos#,intcol#%'
6769wyy3yf66f select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1


SQL> select sql_id,count(*) from v$sql where sql_id='6769wyy3yf66f' group by sql_id;


SQL_ID          COUNT(*)
------------- ----------
6769wyy3yf66f          1


SQL> select sid,event,prev_sql_id,sql_id from v$session where nvl(prev_sql_id,sql_id)='6769wyy3yf66f';


no rows selected


SQL> select count(*) from v$session where nvl(prev_sql_id,sql_id)='6769wyy3yf66f';


  COUNT(*)
----------
         0


SQL> desc icol$;
 Name              Null?    Type
 ----------------- -------- ------------
 OBJ#              NOT NULL NUMBER
 BO#               NOT NULL NUMBER
 COL#              NOT NULL NUMBER
 POS#              NOT NULL NUMBER
 SEGCOL#           NOT NULL NUMBER
 SEGCOLLENGTH      NOT NULL NUMBER
 OFFSET            NOT NULL NUMBER
 INTCOL#           NOT NULL NUMBER
 SPARE1                     NUMBER
 SPARE2                     NUMBER
 SPARE3                     NUMBER
 SPARE4                     VARCHAR2(100
                            0)
 SPARE5                     VARCHAR2(100
                            0)
 SPARE6                     DATE


SQL> select count(*) from icol$;


  COUNT(*)
----------
      2416




SQL> select obj#,bo#,col#,pos#,segcol#,offset,spare1,spare2 from icol$ where rownum<=10 order by 1;


      OBJ#        BO#       COL#       POS#    SEGCOL#     OFFSET     SPARE1     SPARE2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         3          2          1          1          0          0          0          0
         7          6          1          1          0          0          0          0
         9          8          1          1          0          0          0          0
         9          8          2          2          0          0          0          0
         9          8          3          3          0          0          0          0
        11         10          1          1          0          0          0          0
        24         23          1          1          0          0          0          0
        24         23          2          2          0          0          0          0
        26         25          1          1          0          0          0          0
        26         25          2          2          0          0          0          0


10 rows selected.


create table icol$                                     /* index column table */
( obj#          number not null,                      /* index object number */
  bo#           number not null,                       /* base object number */
  col#          number not null,                            /* column number */
  pos#          number not null,        /* column position number as created */
  segcol#       number not null,                 /* column number in segment */
  segcollength  number not null,             /* length of the segment column */
  offset        number not null,                         /* offset of column */
  intcol#       number not null,                   /* internal column number */
  spare1        number,                                              /* flag */
                                              /* 0x01: this is an expression */
                                                  /* 0x02: desc index column */
                                          /* 0x04: filter by col for dom idx */
                                           /* 0x08: order by col for dom idx */
  spare2        number,            /* dimension table internal column number */
  spare3        number,           /* pos# of col in order by list of dom idx */
  spare4        varchar2(1000),
  spare5        varchar2(1000),
  spare6        date
)
cluster c_obj#(bo#)




SQL> create table t_bind(a varchar2(100));


Table created.


SQL> insert into t_bind values(1);


1 row created.


SQL> commit;


Commit complete.


SQL> var x varchar2(10);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> var x varchar2(100);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,sql_text from v$sql where sql_text like '%select a from t_bind where a=%';


SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
3swjj5kc9kfkp select a from t_bind where a=:x
3swjj5kc9kfkp select a from t_bind where a=:x


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8            1 Y


SQL> var x varchar2(38);
SQL> select a from t_bind where a=:x;


no rows selected


---发现子游标没有继续增加
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8            1 Y    




SQL> var x varchar2(50);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8            1 Y


---会不会变量长度，也有个分级，不同分级的长度，会产生新的子游标呢
SQL> alter table t_bind modify a varchar2(1000);


Table altered.


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


no rows selected


SQL> var x varchar2(10);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N


SQL> var x varchar2(100);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8            1 Y


SQL> var x varchar2(200);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8            1 N




SQL> var x varchar2(500);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8            1 N


SQL> var x varchar2(1000);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8            1 N


---可见刷新共享池只会保存
SQL> alter system flush shared_pool;


System altered.


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8            1 N






---继续分析下到底绑定变量分级，到度在哪些值范围内，隶属一个子游标，否则会新生一个子游标


经查MOS，发现文章：High Version Count Due To BIND_MISMATCH [ID 336268.1]
对于绑定变量,ORACLE根据变量长度进行了分级，对于VARCHAR2类型共有如下4级：


第一级：1-32
第二级：33-128
第三级：129-2000
第四级：2000+


SQL> alter table t_bind modify a varchar2(300);


Table altered.




SQL> var x varchar2(50);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N


SQL> var x varchar2(129);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8            1 Y




SQL> var x varchar2(2001);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8            1 Y
3swjj5kc9kfkp 0000000064964090 0000000066EA5F40            2 Y




SQL> var x varchar2(15);
SQL> select a from t_bind where a=:x;


no rows selected




---mos说，可以用10503事件暂时解决上述版本过高问题,好像10503事件没起作用
SQL> alter session set events '10503  trace name context forever ,level 4096';


Session altered.


SQL> alter table t_bind modify a varchar2(300);


Table altered.


SQL> var x varchar2(15);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N




SQL> var x varchar2(120);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8            1 Y


SQL> var x varchar2(600);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8            1 Y


SQL> var x varchar2(2600);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8            1 Y
3swjj5kc9kfkp 0000000064964090 0000000066EA5F40            2 Y




----继续分析10503事件为何不生效,看来不是105032命令的次序问题


SQL> alter session set events '10503  trace name context level 4096,forever';


Session altered.


SQL> alter table t_bind modify a varchar2(300);


Table altered.




SQL> var x varchar2(12);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N




SQL> var x varchar2(100);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8            1 Y


SQL> var x varchar2(250);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8            1 Y


SQL> var x varchar2(2500);
SQL> select a from t_bind where a=:x;


no rows selected


SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';


SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0            0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8            1 Y
3swjj5kc9kfkp 0000000064964090 0000000066EA5F40            2 Y


---继续经查MOS，发现文章
Bug 10274265 - Event 10503 does not work at session level [ID 10274265.8]


可见即使在会话层面配置了10503事件，仍然不会生效


---而且即使设置10503事件成功，可能会引发ORA-600报错
event可能导致ORA-600: [kxspoac : EXL 1]错误


ORA-600: [kxspoac : EXL 1] Occuring During Query If Event 10503 Is Set [ID 810194.1]




--由此可见ORACLE知识联系是非常复杂的，要调整一个参数，必须要从全方位考虑与分析


1，配置参数的命令是什么
2，如果配置参数不成功，它为何不成功，可能也有相关的MOS文章进行介绍
3，如果配置参数成功，会引发什么其它的问题，也要进行全面考虑，否则解决旧问题，引发新问题


引申一下，可见应用人员设计表以及开发人员，在编写代码时，也要考虑到这点，比如：不要在声明变量长度时
过分声明，比如本来20字节就够了，你声明了1000个字节，而且每个开发人员声明长度不同
，这样就会引发本文的子游标不能共共享的问题




基于关镇字查看是否是相关的BUG