子游标过多问题
2019 年 4 月 1 日，在优化 emc 库 sql 的时候，发现一条 sql 下面有 60 多个子游标，其实不算太多，但是出于好奇还是想看看是什么原因导致了这么多的子游标无法共享的问题。之前处理过上千个子游标的问题，定位子游标无法共享原因的方法其实比较简单。
l 如果子游标没有那么多，比如这里就只有 60 多个，那么就直接查询 v$sql_shared_cursor 视图，子游标没有那么多，是可以肉眼观察分析结果的。
l 如果子游标很多，比如之前碰到的上千个，那么查询视图结果太多不好分析，此时可以借助 MOS 上拿来的存储过程，帮助我们分析。 select * from v$sql_shared_cursor where sql_id='bfd80zrdwa63f'; 这里采用直接查询视图定位原因的方法。 可以看到，子游标无法共享的原因在于 roll_invalid_mismatch 。 Oracle 在自动收集统计信息后，游标是否马上全部失效是由收集统计信息时的参数 no_invalid 控制的。该参数有 3 个值 TRUE: 不失效 FLASE: 马上失效 AUTO_INVALID:Oracle 自己决定什么时候失效。这个失效时间跟 sql 执行情况有关，也受另一个隐含参数的影响， _optimizer_invalidation_period 。
|
SQL> set line 200 SQL> col name for a30; SQL> col value for a10; SQL> select * from 2 (select 3 x.ksppinm name, 4 y.ksppstvl value, 5 y.ksppstdf isdefault, 6 decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, 7 decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj 8 from 9 sys.x$ksppi x, 10 sys.x$ksppcv y 11 where 12 x.inst_id = userenv('Instance') and 13 y.inst_id = userenv('Instance') and 14 x.indx = y.indx 15 order by 16 translate(x.ksppinm, ' _', ' ')) T where T.name like '%optimizer_invalidation_period%'; NAME VALUE ISDEFAULT ISMOD ISADJ ------------------------------ ---------- --------------------------- ------------------------------ --------------- _optimizer_invalidation_period 18000 TRUE FALSE FALSE |
默认值是 18000 秒。这么做的好处是可以避免收集统计信息后的硬解析风暴问题。 那么这 60 多个子游标需不需要处理呢？其实这里完全没必要，因为并没有出现硬解析问题和扫描子游标带来的 latch 的争用问题，所以这里完全可以忽略这个问题。 如果数据库中出现了这个问题，需要解决子游标过多的问题，那么该怎么解决呢？
1. 绑定执行计划
2. 清除无效的子游标
11g 以上 Oracle 提供了一种利用 DBMS_SHARED_POOL.PURGE 包清除异常子游标的方法。
如果子游标过多的话，可以通过 version_rpt3_24.sql 脚本来定位原因。
脚本安装：
|
connect / as sysdba start version_rpt3_24.sql |
脚本使用，针对之前处理过的一条 sql 7r6rbrdwr4v9h 生成报告：
|
set pages 2000 lines 100 SELECT * FROM TABLE(version_rpt('7r6rbrdwr4v9h'));
COLUMN_VALUE ---------------------------------------------------------------------------------------------------- Note:438755.1 Version Count Report Version 3.2.3 -- Today's Date 04-jun-18 10:38 RDBMS Version :11.2.0.4.0 Host: zp-prd-oracle-11 Instance 1 : BDODS1 ================================================================== Addr: 0000000F2CE29848 Hash_Value: 2037542192 SQL_ID 7r6rbrdwr4v9h Sharable_Mem: 8883118 bytes Parses: 17448 Execs:17459 Stmt: 0 select t.name tname,t.servip,t.calcendeid,t.secondname from kett 1 le.execinfo2 t where t.name=:1 and t.servip=:2 and decode(t.se 2 condname,:3 ,1, 0) =1 3
Versions Summary ---------------- LANGUAGE_MISMATCH :5 ROLL_INVALID_MISMATCH :400 BIND_LENGTH_UPGRADEABLE :11
Total Versions:410
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ cursor_sharing = EXACT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Plan Hash Value Summary ----------------------- Plan Hash Value Count =============== ===== 1886329918 411
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Details for LANGUAGE_MISMATCH :
No details available ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Details for ROLL_INVALID_MISMATCH :
No details available ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Details for BIND_LENGTH_UPGRADEABLE :
Consolidated details for BIND* columns: BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)
from v$sql_bind_capture COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE) ======== ======== =============== =============== ======== =============== ================= 411 1 32 128 1 Yes (,) 411 2 32 32 1 No (,) 411 3 32 32 1 No (,)
SUM(DECODE(column,Y, 1, 0) FROM V$SQL IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE =========== ================= ============= ============ 0 411 0 411 #### To further debug Ask Oracle Support for the appropiate level LLL. alter session set events 'immediate trace name cursortrace address 2037542192, level LLL'; To turn it off do use address 1, level 2147483648 ================================================================
61 rows selected. |
可以看到 child cursor 多的原因有如下三条，最主要的就是 roll_invalid_mismatch
LANGUAGE_MISMATCH :5
ROLL_INVALID_MISMATCH :400
BIND_LENGTH_UPGRADEABLE :11 LANGUAGE_MISMATCH 是由于客户端字符集导致的。 BIND_LENGTH_UPGRADEABLE 是由于绑定变量长度导致的。
ROLL_INVALID_MISMATCH 与 dbms_stats 的 no_invalidate 参数有关，一般大批量 sql 出现这种情况一般是因为自动收集统计信息导致的。
子游标产生的原因很多，除此之外，比较常见的还有： 带绑定变量的 sql 由于 ACS 特性（自适应游标）导致； 基数反馈； 绑定变量长度； Cursor_sharing 设置为 similar 或 force ；
11g 中引入了一个隐含参数 _cursor_obsolete_threshold ，该参数用来限制单个 parent cursor 下的 child cursor 的数量 ,12c 中该参数默认值为 8192. 当子游标数超过这个值， parent cursor 就会被丢弃并同时重新创建一个新的 parent cursor. 不过这个参数设置的不合理的会可能会导致 cursor:mutex X 等待事件，比如 12c 中 8192 的值就有点大了，可能会发生这个等待事件问题。
