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
------------------------------ ---------- ------------- ---------------------------------------- ----------
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 c25ar03n0p5sz row cache lock                                   13
20200929 01:11:25                      91 c25ar03n0p5sz row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 c25ar03n0p5sz row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 c25ar03n0p5sz row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 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('20200929 01:00:00','YYYYMMDD hh24:mi:ss')
     AND SAMPLE_TIME<to_date('20200929 01:15:00','YYYYMMDD hh24:mi:ss')
     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高的现象随之解决,不再复现。