之前遇到过一次11.2.0.4.0 RAC 环境 Result Cache: Channel
这次再次遇到,
update 一条语句时,造成提交持续锁表
select * from (select sid,event,p1,p2,p3,p1text,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where wait_class# <> 6
order by wait_time desc) where rownum <=10;
select session_id,sql_id,sample_id,event,sample_time from v$active_session_history where session_id==&sid;
select sql_text from v$sqltext where sql_id='&sql_id' order by piece;
result cache被使用
SQL> show parameter result_cache_max_size
NAME_COL_PLUS_SHOW_PARAM TYPE
-------------------------------------------------------------------------------- -----------
VALUE_COL_PLUS_SHOW_PARAM
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
result_cache_max_size big integer
197280K
gv$chanel_waits显示高等待的组件
SQL> set linesize 200 pagesize 500
SQL>
SQL> SELECT CHANNEL,
2 SUM(wait_count) sum_wait_count
3 FROM GV$CHANNEL_WAITS
4 GROUP BY CHANNEL
5 ORDER BY SUM(wait_count) DESC;
CHANNEL SUM_WAIT_COUNT
---------------------------------------------------------------- --------------
MMON remote action broadcast channel 806260
kxfp control signal channel 167559
RBR channel 26448
obj broadcast channel 5153
LCK0 ksbxic channel 252
quiesce channel 2
service operations - broadcast channel 2
kill job broadcast - broadcast channel 1
parameters to cluster db instances - broadcast channel 1
9 rows selected
如果满足上面的3个条件,那么可以确定该等待事件由result cache处理相关的bug_19557279造成,该问题在Oracle 12.2版本中修复。
SOLUTION
- Upgrade to a version where Bug 19557279 is fixed i.e. Oracle 12c release 12.2 or the 12.1.0.2.0 Patchset
- Apply patch 18416368 if available on your platform
- To workaround the issue, disable result cache by setting the following parameter:
SQL> alter system set result_cache_max_size=0;
The instance(s) must be restarted for the parameter to take effect.
调整后 问题解决
SQL> SELECT CHANNEL,
2 SUM(wait_count) sum_wait_count
3 FROM GV$CHANNEL_WAITS
4 GROUP BY CHANNEL
5 ORDER BY SUM(wait_count) DESC;
CHANNEL SUM_WAIT_COUNT
---------------------------------------------------------------- --------------
kxfp control signal channel 19
MMON remote action broadcast channel 5
quiesce channel 2
service operations - broadcast channel 1