SQL ordered by Version Count and Troubleshooting
在AWR 报告中有一个指标SQL ordered by Version Count,列出的是大于20个版本child cursor的sql,也可以使用下面的sql去v$sqlarea查询
SELECT sql_text,version_count,loaded_versions,executions,PARSE_CALLS,address,hash_value FROM V$SQLAREA where version_count>20 order by version_count desc;
version_count 表示这个父游标下子游标的数量，通常执行一条sql,加载到shared pool中，v$sqlarea是父游标，而v$sql反应的也就是子游标
loaded_versions表示这个父游标在cache中的已加载context heap的子游标数量对应的是v$sql中loaded_versions=1的数量。对应V$SQL_SHARED_CURSOR
v$sql.users_openng表示当前child cursor被任何用户打开的总数，对应的是v$open_cursor中的数量, v$sql.loads 表示对象被加载或挤出shared pool中重新加载回来的次数,还有last_load_time,last_active_time,first_load_time都有可以参考
如果一个相同的sql字有太多的子游标，就会增加library cache latch竞争。
下面用一个例子来说明问题db_version 10204
SQL> SELECT version_count,loaded_versions,executions,PARSE_CALLS,address,hash_value,sql_id
2 FROM V$SQLAREA
3 where version_count>100
4 order by version_count desc;
VERSION_COUNT LOADED_VERSIONS EXECUTIONS PARSE_CALLS ADDRESS HASH_VALUE SQL_ID
------------- --------------- ---------- ----------- ---------------- ---------- -------------
147 19 13780 13782 0000000249DC8A60 835964168 fhnpmnnsx7m88
104 2 193 192 0000000248FE13A8 1769035544 0d917t1nr2pss
SQL> select sql_text from v$sqlarea where sql_id='fhnpmnnsx7m88'
----------------------------------------------------------------------------------------
insert into ICME_Nxxxxxx_SCORE (ACTIVITY_TYPE_ID, FROM_ORG_ID, SCORE_TYPE_ID, GIVE_SCORE_ORG_ID, IC_CODE, SPECIALTY_ID, TO_ORG_ID, CLASS_HOUR, CLASS_SCORE,
SCORE_ACTIVITY_DATE, SCORE_ACTIVITY_CONTENT, TRAIN_PLACE, VERIFY_CODE, SCORE_REMARK, SCORE_CHECK_FLAG, ADMIN_ID, ORG_ID, IS_VALID, SOURCE_FLAG, CREATE_TIME, UPDATE_TIME, CERTIFICATE_CODE, score_holder, SCORE_ID) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24)
可以看到sql使用了绑定变量，而且是一条简单的insert sql
cause:
bug,related to name resolution issues, fine grained access control, cursor invalidation, bind buffer length or any of the other common structural or coding reasons for multiple child cursors
Technical background:
The database does adjust the length of CHAR bind buffers to preset sizes. There are four steps: 32, 128, 2000 and 4000 bytes. So if we are execute a statement with a bind value of 10 bytes,the buffer will be 32 bytes. Do we re-execute it with 52 bytes in the bind variable, the previously created child cursor cannot be reused, and will be recreated with a bind buffer of 128 bytes. The system view v$sql_shared_cursor indicates this invalidated child cursor as BIND_LENGTH_UPGRADEABLE.
SQL> desc ixxxe.icme_nxxxxxx_score; Name Null? Type ----------------------------------------------------- -------- ------------------------------------ SCORE_ID NOT NULL NUMBER(38) IC_CODE NOT NULL VARCHAR2(9) CLASS_HOUR NOT NULL NUMBER(8,2) CLASS_SCORE NOT NULL NUMBER(8,2) SCORE_ACTIVITY_DATE NOT NULL DATE SCORE_ACTIVITY_CONTENT VARCHAR2(500) SCORE_REMARK VARCHAR2(500) GIVE_SCORE_ORG_ID NOT NULL NUMBER(38) FROM_ORG_ID NOT NULL NUMBER(38) SCORE_CHECK_FLAG NOT NULL NUMBER(38) SCORE_TYPE_ID NOT NULL NUMBER(38) ACTIVITY_TYPE_ID NOT NULL NUMBER(38) SPECIALTY_ID NOT NULL NUMBER(38) ADMIN_ID NOT NULL VARCHAR2(50) ORG_ID NOT NULL NUMBER(38) IS_VALID NOT NULL NUMBER(38) CREATE_TIME NOT NULL DATE UPDATE_TIME NOT NULL DATE TO_ORG_ID NOT NULL NUMBER(38) TRAIN_PLACE VARCHAR2(100) VERIFY_CODE VARCHAR2(100) CERTIFICATE_CODE VARCHAR2(100) SOURCE_FLAG NOT NULL NUMBER(1) SCORE_HOLDER VARCHAR2(100) COURSE_ID VARCHAR2(20)
每个字符类型的绑定变量有4种类型，表上有9个varchar2类型，如果开发的在声明绑定变量时长度不一，理论上就有可能产生多个child cursor,幸运的是我们当最大时也就不到150，
平时loaded_count也小于20，这个虽然对于一个executions 1.3W 次并不多，但也应该重视。一个游标有多个版本确实不是什么好事，因为所有的version都会转换为libary cache latch,在执行你的sql查找正确version，你就会holding latch并阻止其它用户，随着version的增长等待的时间就会越来越长，带来的性能问题就是latch contention for the library cache latches ,这也是10gr2推出mutex 的原因。
10503 event它允许用户指定字符绑定缓冲区的长度。根据使用的长度，子游标中的字符绑定都可以使用相同的绑定长度创建;跳过绑定毕业，保持子链相对较小。这有助于缓解与分级绑定相关的潜在游标共享问题(V$SQL_SHARED_CURSOR.BIND_LENGTH_UPGRADEABLE=Y)。之前BLOG中提到过默认字符绑定变量是分32, 128, 2000 和4000 bytes4个刻度。注意在11.2.0.4以前存在 Bug 10274265 在session级配置event不工作的现象。
event = 10503 trace name context level xxxx, forever
The level of the event is the bind length to use, in bytes. It is relevant for binds of types:
o Character (but NOT ANSI Fixed CHAR (type 96 == DTYAFC))
o Raw
o Long Raw
o Long
如果可能，计算v$sql_bind_capture的输出。查看子游标之间的Max_length变化，表明绑定缓冲区的大小在增加。也可以通过v$sql_bind_metadata可以查看每个绑定变量的长度范围，比如绑定变更:11就在两种长度区间中。
SQL> select m.position,m.bind_name , m.max_length,count(*) child_cursor_count
2 from v$sql s, v$sql_bind_metadata m
3 where s.sql_id = 'fhnpmnnsx7m88'
4 and s.child_address = m.address group by m.position,m.bind_name , m.max_length
5 order by 1, 2;
POSITION BIND_NAME MAX_LENGTH CHILD_CURSOR_COUNT
---------- ------------------------------ ---------- ------------------
1 1 22 6
2 2 22 6
3 3 22 6
4 4 22 6
5 5 32 6
6 6 22 6
7 7 22 6
8 8 22 6
9 9 22 6
10 10 11 6
11 11 128 3
11 11 2000 3
12 12 32 6
13 13 32 1
13 13 128 5
14 14 32 1
14 14 128 1
14 14 2000 4
15 15 22 6
16 16 32 6
17 17 22 6
18 18 22 6
19 19 22 6
20 20 11 6
21 21 11 6
22 22 32 5
22 22 128 1
23 23 32 4
23 23 128 2
24 24 22 6
30 rows selected.
Debug trace
在10G中，可以使用CURSORTRACE来帮助调查为什么游标没有被共享。首先从v$sql获取sql hash_value.
alter system set events 'immediate trace name cursortrace level 577, address hash_value';
(levels 578-580 can be used for high level tracing (577=level 1, 578=level 2, 580=level 3)
To turn off tracing use:-
alter system set events 'immediate trace name cursortrace level 2147483648, address 1';
In 11.2 可以使用 cursordump，只能是system级，这会转储一些额外的信息，比如展开“optimizer_mismatch”问题的参数。在RDBMS的后期版本中，也有一些改进，可以转储更多关于子游标不能共享的实际原因的信息(即参数差异)。该信息可以在v$sql_shared_cursor的REASON列中找到，并且是XML格式的。
alter system set events 'immediate trace name cursordump level 16'
可以去V$SQL_SHARED_CURSOR查询什么没有共享子游标的原因, MOS中提供了version_rpt.sql 分析。
SQL> select * from V$SQL_SHARED_CURSOR where sql_id='fhnpmnnsx7m88';
Metalink’s node ID 10157392.8 Bug 10157392 12.1 fixed
* High version counts are seen on cursors with binds
* The reason given in v$sql_shared_cursor is a bind mismatch
* The problem happens when many sessions are trying to execute the same cursor and there’s evidence that the child cursors are being reloaded back into the shared pool.
(See v$librarycache).
临时的解决办法是加大shared_pool size,减少cursor 被flush出去的机率，或使用dbms_shared_pool包keep 方法，减少cursor老化被替换出去。
exec dbms_shared_pool.keep('&address,&hash_value','C')
keep以后的对象去v$db_object_cache查询，条件是kept=’YES’,相反用unkeep
SELECT * FROM v$db_object_cache where name like 'insert into ICME_NOPROJECT_SCORE%'
dbms_shared_pool.purge是指定单条sql的cursor从library cache 中清除参考官方文档
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_shared_pool.htm#sthref7227
DBMS_SHARED_POOL.PURGE (
name VARCHAR2,
flag CHAR DEFAULT 'P',
heaps NUMBER DEFAULT 1)
name
Name of the object to purge.
The value for this identifier is the concatenation of the address and hash_value columns from the v$sqlarea view. This is displayed by the SIZES procedure.
Currently, TABLE and VIEW objects may not be purged.
flag
(Optional) If this is not specified, then the package assumes that the first parameter is the name of a package/procedure/function and resolves the name.
Set to 'P' or 'p' to fully specify that the input is the name of a package/procedure/function.
Set to 'T' or 't' to specify that the input is the name of a type.
Set to 'R' or 'r' to specify that the input is the name of a trigger.
Set to 'Q' or 'q' to specify that the input is the name of a sequence.
In case the first argument is a cursor address and hash-value, the parameter should be set to any character except 'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or 'T' or 't'.
heaps
Heaps to be purged. For example, if heap 0 and heap 6 are to be purged:
1<<0 | 1<<6 => hex 0x41 => decimal 65, so specify heaps =>65.Default is 1, that is, heap 0 which means the whole object would be purged
purge sql curosr使用如下
SQL> exec dbms_shared_pool.purge('0000000249DC8A60,835964168','c',65);
or
SQL> exec dbms_shared_pool.purge('0000000249DC8A60,835964168','c',64); --only executions plan
or
SQL> exec dbms_shared_pool.purge('0000000249DC8A60,835964168','c',1);
or
SQL> exec dbms_shared_pool.purge('0000000249DC8A60,835964168','c');
or
SQL> exec dbms_shared_pool.purge('0000000249DC8A60,835964168','z'）;
note: 10r2中使用dbms_shared_pool.purge中存在bug,要使用5314566 enven 或者应用5614566补丁,如还还是清理不掉，就要自己排查当前cursor是否被其它session cache or open from v$open_cursor.
SQL> alter session set events ‘5614566 trace name context forever’;
SQL> exec dbms_shared_pool.purge(‘0000000249DC8A60,835964168′,’c’);
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。