hot block的診斷案例
2024-07-21 02:34:17
供稿：網友
診斷因為inefficent SQL導致嚴重Cache buffers chain / Cache buffer lru chain Latch，引起的CPU緊張和用戶Batch緩慢 數據庫817，有人叫batch太慢了，開始檢查。 CPU使用率一直很高，且有進程IO等待，但iostat沒有顯示有wt vmstat 1 10
PRocs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s2 s3 in sy cs us sy id
3 7 0 39824280 41916960 0 0 0 0 0 0 0 0 6 6 6 4294967196 0 0 -810 -1696 -1165
21 28 0 39444944 40480648 3093 12 16512 0 0 0 0 0 1 5 3 65720 205520 53041 70 29 0
28 6 0 39444944 40480784 3307 4 14392 0 0 0 0 0 3 3 3 58991 161877 43637 74 26 0
25 115 0 39445008 40480696 1776 13 16032 0 0 0 0 0 0 1 2 53622 170834 40450 71 29 0
46 10 0 39446648 40482024 1054 2 12976 0 0 0 0 0 2 3 2 49978 163920 36885 72 28 0
17 10 0 39446648 40482000 1366 7 14664 0 0 0 0 0 0 0 2 44678 187017 36723 71 28 0
14 5 0 39445952 40481536 1142 696 9904 0 0 0 0 0 5 2 0 54537 109587 30931 71 29 0
46 121 0 39445376 40480744 875 26 11464 0 0 0 0 0 3 5 3 75939 197783 44311 69 31 0
40 43 0 39451008 40485880 1364 1384 14368 0 0 0 0 0 2 5 4 75546 180180 47494 70 30 0
16 15 0 39450232 40485128 1782 994 18408 0 0 0 0 0 0 3 1 61110 138910 38253 72 28 0iostat 1 5
tty sd0 sd1 sd2 sd3 cpu
tin tout kps tps serv kps tps serv kps tps serv kps tps serv us sy wt id
23 1227 0 0 0 105 6 7 105 6 7 103 6 7 45 95 21 44
0 234 0 0 0 0 0 0 8 1 1 8 1 11 71 29 0 0
0 80 0 0 0 32 4 8 32 4 10 8 1 3 68 32 0 0
0 80 0 0 0 0 0 0 24 3 12 8 1 9 70 30 0 0
0 80 0 0 0 8 1 7 16 2 1 8 1 6 69 31 0 0 使用top/prstat，沒有發現使用非凡多cpu資源的進程 察看數據庫是否有并行進程，v$px_session返回空行?！ z查v$session_wait,發現很多latch free,懷疑latch free導致cpu 緊張，db file sequential/scattered read導致有進程被IO阻塞。EVENT COUNT(*)
---------------------------------------------------------------- ---------
PL/SQL lock timer 21
SQL*Net message from client 362
SQL*Net message to client 2
SQL*Net more data to client 2
db file scattered read 6
db file sequential read 25
latch free 10
log file sync 3
pmon timer 1
rdbms ipc message 6
smon timer 1 察看是那些latch,發現很多cache buffers chains，cache buffers lru chain懷疑不高效率的SQL導致hot blockSQL> select v$session.sid,name latch_name from
v$session,v$latch,v$session_wait s where v$session.sid=s.sid and s.event='latch free' and s.p2=v$latch.latch#;SID LATCH_NAME
---------- ---------------------------------------------------------------
181 cache buffers chains
287 cache buffers chains
416 cache buffers lru chain
485 cache buffers lru chain
502 cache buffers lru chain
586 cache buffers lru chain 想察看哪個segment引起hot block， 但如下sql 幾分鐘內無返回，故cancel。Select distinct owner,segment_name,partition_name,segment_type from dba_extents a ,
(Select obj, dbarfil, dbablk from x$bh where Hladdr in (Select addr from
v$latch_children where name='cache buffers chains' and addr in (select p1raw from v$session_wait
where event='latch free') )) b where
b.dbarfil=a. RELATIVE_FNO and b.dbablk >= a.block_id and b.dbablk<( a.block_id+a.blocks);
跟蹤進程，察看進程執行什么SQL********************************************************************************SELECT SEVT.SCHD_EV_ID,ATEV.ROW_SEQFROMFT_T_SEVT SEVT,FT_T_ATEV ATEV WHERE SEVT.SCHD_POST_TMS<=:b1 AND SEVT.ACTG_TRN_EV_ID=ATEV.ACTG_TRN_EV_ID AND SEVT.ACCT_GRP_OID=:b2 AND SEVT.ACCT_ID BETWEEN:b3 AND:b4 ORDER BY ATEV.ROW_SEQcall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 0 0.00 0.00 0 0 0 0Execute 20 0.00 0.00 0 0 0 0Fetch 19 57.42 90.34 461156 517860 3344 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 39 57.42 90.34 461156 517860 3344 0********************************************************************************SQL> @$Oracle_HOME/rdbms/admin/utlXPlsPlan Table-------------------------------------------------------------------------------- Operation Name Rows Bytes Cost Pstart Pstop -------------------------------------------------------------------------------- SELECT STATEMENT 20 1K 1724 SORT ORDER BY 20 1K 1724 NESTED LOOPS 20 1K 1722 TABLE access FULL FT_T_SEVT 20 1K 1662 TABLE ACCESS BY INDEX RFT_T_ATEV 415M 6G 3 INDEX UNIQUE SCAN FT_T_ATEV 415M 2 --------------------------------------------------------------------------------9 rows selected. 明顯表FT_T_SEVT 缺少索引，導致full table scan. 還trace了其他幾個session，發現也是執行如上同樣的SQL語句。檢查v$sqlSQL>selectusers_opening,EXECUTIONS,BUFFER_GETS,DISK_READS from v$sql where address='B83AC5F0';USERS_OPENINGUSERS_OPENING 表示有60個session在執行同樣的SQL語句，導致表FT_T_SEVT 非常熱點，嚴重的cache buffer chain/cache buffer lru chain 使CPU使用率較高；Full table scan也給IO帶來等待。EXECUTIONS BUFFER_GETS DISK_READS 60 49784 -1.604E+09 1963650435
