问题描述:
最近遇见一个问题关于单个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博客 ” ,链接:http://blog.itpub.net/25105315/viewspace-2131803/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25105315/viewspace-2131803/