问题描述：

最近遇见一个问题关于单个SQL拥有过高子游标。当时接到oracle数据库报警，有200多会话一直处在active状态，insert语句非常缓慢。主机CPU达到了97%。

问题分析：

1. 查看awr报告

发现top 5等待事件排在前面的是resmgr:cpu quantum，cursor: mutex S，library cache lock。等待事件resmgr:cpu quantum应该是使用了Oracle资源隔离，cpu资源达到了瓶颈。

2. 查看具体的会话等待

发现基本上都是一个insert的SQL在等待resmgr:cpu quantum和mutex S。但是这个SQL语句只是一个简单的insert语句，类似于insert into tabname values (value1,value2,...);

3. 查看v$sql中改SQL的具体信息

发现这个sql有2000多个子游标。为什么有这么多子游标呢？可以查询视图v$sql_shared_cursor查找sql不能共享的原因。其中大部分不能共享的原因是ROLL_INVALID_MISMATCH，这个字段的值是N。

通过查阅资料metalink 557661_1，ROLL_INVALID_MISMATCH是由于调用了dbms_stats.gather_xxxx后收集了统计信息，统计信息收集后过了一段时间，当再次执行sql并解析时，将旧子游标失效，生成新的子游标，标记旧的子游标的v$sql_shared_cursor.ROLL_INVALID_MISMATCH为N。

4. 查看该SQL的表是否有统计信息收集，表的最近统计信息收集时间是问题发生的一个小时前。

问题解决：

1. 知道是统计信息导致后，决定手工flush shared pool

手工清理shared pool后，查看数据库的等待事件，发现短时间内有大量的cursor: mutex S，library cache lock等待时间，过了2分钟后这些等待事件消失。系统恢复正常。

2. 表的统计信息锁定

由于有问题SQL的表insert非常频繁，为了防止问题再次发生，先将这张表的统计信息锁住。

针对过高SQL子游标问题，oracle metalink也提供了一个脚本，

Document 438755.1 High SQL Version Counts - Script to determine reason(s)

下面是运行结果：

点击(此处)折叠或打开

SQL> SELECT * FROM TABLE(version_rpt('7sx5p1ug5ag12'));
COLUMN_VALUE
----------------------------------------------------------------------------------------------------
Note:438755.1 Version Count Report Version 3.2.4 -- Today's Date 03-jan-17 14:32
RDBMS Version :11.2.0.4.0 Host: cnsz081215 Instance 1 : d1fo007
==================================================================
Addr: 0000000092F83C28 Hash_Value: 2656386082 SQL_ID 7sx5p1ug5ag12
Sharable_Mem: 1499832 bytes Parses: 198 Execs:9912
Stmt:
0 SELECT SPARE4 FROM SYS.OPTSTAT_HIST_CONTROL$ WHERE SNAME = :B1
1
Versions Summary
----------------
LANGUAGE_MISMATCH :86
BIND_LENGTH_UPGRADEABLE :1
Total Versions:85
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cursor_sharing = EXACT
_cursor_obsolete_threshold = 1024 (See Note:10187168.8)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
1097271556 86
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for LANGUAGE_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)
======== ======== =============== =============== ======== =============== =================
86 1 32 128 1 Yes (,)
SUM(DECODE(column,Y, 1, 0) FROM V$SQL
IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
=========== ================= ============= ============
0 82 0 2
####
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
'immediate trace name cursortrace address 2656386082, level LLL';
To turn it off do use address 1, level 2147483648
================================================================
53 rows selected.
SQL>

ITPub博客

过高SQL子游标案例分析

最新文章