一、问题描述
接到业务反馈,oracle 12c RAC 数据库 CPU 使用率很高。
二、处理
2.1 数据库检查
检查等待事件,存在大量 cursor: mutex X 、 cursor: mutex S 。
查看这些等待事件对应sql语句为
set linesize 220
set pagesize 1000
col username for a40
col event for a40
col sql_id for a20
select a.username,
a.event,
a.sql_id,
c.plan_hash_value,
c.child_number,
max(round(decode(c.executions,
0,
c.buffer_gets,
c.buffer_gets / c.executions))) exec_gets,
a.row_wait_obj#,
count(*)
from v$session a, v$sql c
where a.status = 'ACTIVE'
and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and
a.wait_class = 'Idle')
and a.sql_id = c.sql_id(+)
and a.sql_child_number = c.CHILD_NUMBER(+)
group by a.username,
a.event,
a.sql_id,
c.plan_hash_value,
a.row_wait_obj#,
c.child_number
order by count(*) desc,
a.username,
a.sql_id,
c.child_number,
a.row_wait_obj#;
2.2、简单分析
由等待事件 cursor: mutex X 、 cursor: mutex S 与硬解析、软解析有关,检查子游标发现存在7000多个子游标。
同一条sql,不存在绑定变量,又产生这么多子游标。那么需要检查子游标不共享的原因。
经检查不共享原因为LANGUAGE_MISMATCH。语言环境不一致,怎么可能每次环境都不一样。这个原
因先不管,先想办法让业务连接吧。
2.3、临时处理
数据库有个机制,当子游标数量超过一定数量会清除父游标。查看该参数
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc describe from x$ksppi a,x$ksppcv b where a.indx = b.indx and a.ksppinm like '%cursor_obsolete%';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIBE
--------------------------------------------------------------------------------
_cursor_obsolete_threshold
8192
Number of cursors per parent before obsoletion.
8192,这边子游标才7000,够不着啊。看这个sql内容也没多大意义,手工清理吧
SQL> select address,hash_value,version_count from v$sqlarea where sql_id='f0h5rpzmhju11';
ADDRESS HASH_VALUE VERSION_COUNT
---------------- ---------- -------------
000000006BFFAC00 3498659280 2
SQL> exec dbms_shared_pool.purge('<address,hash_value>','C');
Ex:
SQL> exec dbms_shared_pool.purge('000000006BFFAC00,3498659280','C');
PL/SQL procedure successfully completed.
这时候查看等待事件,数量在稳步下降,最后全部下降。客户可以连接了。
解法:
清除共享池中的SQL执行计划
exec dbms_shared_pool.purge('<address,hash_value>','C');
2.4、后续分析
查询MOS,发现两篇相关文档。
数据库挂起 由于 LANGUAGE_MISMATCH 的 High Version Count 导致 ‘cursor:mutex X’ 争用 (Doc ID 2577528.1)
该文档该处的临时解决方法也是干掉父游标。其次调整_cursor_obsolete_threshold参数为较低的值以至子游标达到一定数量后使父游标失效,重新解析。
Bug 25054064 - Cursor Has High Version Count In PDB Whose Character Set Is Different From CDB$ROOT (Doc ID 25054064.8)
文档说是因为PDB与CDB字符集不一致导致LANGUAGE MISMATCH,看起来和那个sql对的上。按照文档安装补丁即可。
目前观察12.2版本中每次登录都会出现同sqlid的新的子游标。猜测为bug