近期整理案例,发现前不久同事反馈一个数据库服务器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
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高的现象随之解决,不再复现。