背景
某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
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1869426/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1869426/
611
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
