oracle 白鳝 row cache lock ROW CACHE LOCK导致数据库CPU高案例分析

近期整理案例，发现前不久同事反馈一个数据库服务器CPU居高不下，无法找到根因过来询问，详情如下：

EVENT COUNT(*)

---------------------------------------- ----------

row cache lock 1007

213

buffer busy waits 165

log file switch (checkpoint incomplete) 137

free buffer waits 114

db file async I/O submit 75

log file sync 73

db file sequential read 62

read by other session 36

log file parallel write 15

resmgr:cpu quantum 15

enq: TX - row lock contention 13

enq: HW - contention 9

direct path read 8

enq: SQ - contention 4

latch: undo global data 3

write complete waits 2

log file sequential read 1

null event 1

db file scattered read 1

Log archive I/O 1

LNS wait on SENDREQ 1

enq: US - contention 1

从ASH中看到，CPU高的时间段内，等待事件最多的为ROW CACHE LOCK，

TIME USER_ID SQL_ID EVENT P1

------------------------------ ---------- ------------- ---------------------------------------- ----------

2029 01:11:25 91 7265mcg5kwyc3 row cache lock 13

2029 01:11:25 91 11xs00r4qzb7n row cache lock 13

2029 01:11:25 91 c25ar03n0p5sz row cache lock 13

2029 01:11:25 91 c25ar03n0p5sz row cache lock 13

2029 01:11:25 91 7265mcg5kwyc3 row cache lock 13

2029 01:11:25 91 11xs00r4qzb7n row cache lock 13

2029 01:11:25 91 c25ar03n0p5sz row cache lock 13

2029 01:11:25 91 11xs00r4qzb7n row cache lock 13

2029 01:11:25 91 11xs00r4qzb7n row cache lock 13

2029 01:11:25 91 11xs00r4qzb7n row cache lock 13

2029 01:11:25 91 11xs00r4qzb7n row cache lock 13

2029 01:11:25 91 c25ar03n0p5sz row cache lock 13

2029 01:11:25 91 11xs00r4qzb7n row cache lock 13

2029 01:11:25 91 7265mcg5kwyc3 row cache lock 13

2029 01:11:25 91 11xs00r4qzb7n row cache lock 13

2029 01:11:25 91 11xs00r4qzb7n row cache lock 13

2029 01:11:25 91 7265mcg5kwyc3 row cache lock 13

2029 01:11:25 91 11xs00r4qzb7n row cache lock 13

2029 01:11:25 91 11xs00r4qzb7n row cache lock 13

2029 01:11:25 91 11xs00r4qzb7n row cache lock 13

2029 01:11:25 91 11xs00r4qzb7n row cache lock 13

2029 01:11:25 91 11xs00r4qzb7n row cache lock 13

2029 01:11:25 91 7265mcg5kwyc3 row cache lock 13

2029 01:11:25 91 7265mcg5kwyc3 row cache lock 13

2029 01:11:25 91 7265mcg5kwyc3 row cache lock 13

确认该时间段内的具体SQL，发现均存在SEQUENCE调用

SQL_ID COUNT(*)

------------- ----------

7265mcg5kwyc3 357

11xs00r4qzb7n 341

c25ar03n0p5sz 277

f9ym730zzf1s2 12

9dnygr7asqmy9 12

dksd9txy8vuk6 6

9qzwmgp034kx2 2

SQL> SELECT DISTINCT sql_id,

sql_text

FROM v$sql

WHERE sql_id IN ('dksd9txy8vuk6',

'11xs00r4qzb7n',

'9qzwmgp034kx2',

'9dnygr7asqmy9',

'7265mcg5kwyc3',

'c25ar03n0p5sz',

'f9ym730zzf1s2');

SQL_ID SQL_TEXT

------------- ------------------------------------------------------------------------------------------------------------------------

9qzwmgp034kx2 INSERT INTO opfqresultdata VALUES(OPFQRESULTDATA_SEQUENCE_ID.Nextval,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 )

f9ym730zzf1s2 select SUBSFLT_HISTORY_SEQUENCE_ID.nextval from dual

7265mcg5kwyc3 INSERT INTO pnrresultdata VALUES(PNRRESULTDATA_SEQUENCE_ID.NEXTVAL,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 )

c25ar03n0p5sz INSERT INTO etresultdata VALUES(ETRESULTDATA_SEQUENCE_ID.Nextval,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 )

9dnygr7asqmy9 select SUBSFLTREQUEST_SEQUENCE_ID.nextval from dual

11xs00r4qzb7n INSERT INTO unknowdata VALUES(UNKNOWDATA_SEQUENCE_ID.Nextval,:1 ,:2 ,:3 )

dksd9txy8vuk6 INSERT INTO fltresultdata VALUES(FLTRESULTDATA_SEQUENCE_ID.Nextval,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:1

SQL> select DISTINCT P1

FROM

(SELECT to_char(SAMPLE_TIME,'YYYYMMDD hh24:mi:ss') time,

USER_ID,

SQL_ID,

EVENT,

P1

FROM dba_hist_active_sess_history

WHERE SAMPLE_TIME>to_date('2029 01:00:00','YYYYMMDD hh24:mi:ss')

AND SAMPLE_TIME

AND EVENT = 'row cache lock');

P1

----------

13 --正是dc_sequences

SQL> select parameter,gets,getmisses,MODIFICATIONS from v$rowcache where cache#=13;

PARAMETER GETS GETMISSES MODIFICATIONS

-------------------------------- ---------- ---------- -------------

dc_sequences 766929021 5065 766929020

发现此sequence中的cache size均为0，出现row cache lock与cpu高便不足为奇

将频繁调用的sequence cache size调整至500后，等待与cpu高的现象随之解决，不再复现。