ORACLE等待事件latch: cache buffers chains
今天上午,電渠生產庫維護人員通知,ORACLE生產庫中有比較多的latch: cache buffers chains引起的會話,造成堵塞,相關處理過程如下
環境:hp-unix
資料庫版本:10.2.0.5
登入資料庫查詢等待事件:
根據等待事件latch: cache buffers chains查詢相關的SQL_ID,引起爭用的物件所在的檔案號和塊號
select * from (select
count(*),
sql_id,
nvl(o.object_name,ash.current_obj#) objn,
substr(o.object_type,0,10) otype,CURRENT_FILE# fn,
CURRENT_BLOCK# blockn
from v$active_session_history ash
, all_objects o
where eve 2 3 4 5 6 7 8 9 nt like 'latch: cache buffers chains'
and o.object_id (+)= ash.CURRENT_OBJ#
group by sql_id, current_obj#, current_file#,
current_block#, o.object_name,o.object_type
order by count(*) desc )where rownum <=10;
COUNT(*) SQL_ID OBJN OTYPE FN BLOCKN
---------- -------------------------- ------------------------------ ---------------------------------------- ---------- ----------
8391 0x1p0sb3c0ryj IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
6190 bp05yyd8fsz0a IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
6160 0r9c1zgk0pn0d IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
5621 cc9r2y7s20s6z IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
4917 f9d1mg4yjchwf IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
4591 9ydcft7ttz9fc IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
4168 a0ht9ahwtz34n IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
4134 5c7cq4qx32xft IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
4133 gfunt0a1kg64k IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
4078 69hcuf3rfamws -1 0 0
檢視latch: cache buffers chains引起爭用的具體會話及開始時間
select sid,username,status,sql_id,to_char(logon_time,'yyyy-mm-dd hh24:mi:ss') start_exec
from v$session s
where event='latch: cache buffers chains' order by logon_time;
經過查詢,以上的SQL是同一種型別的SQL,有30多個會話,其執行計劃如下:
SQL_ID cc9r2y7s20s6z, child number 0
-------------------------------------
SELECT count(*) from (SELECT * FROM (SELECT A.LOGID as rec_id, A.BUSFLOWNO, A.TYPE,
E.CMD_NAME, A.CONTENT, A.MONEY, A.NUM, A.ACTMONEY,
A.OPRDATE, TO_CHAR(TO_DATE(A.OPRDATE, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') AS OPRDATES, A.oprdate as b_time, A.oprdate as e_time,
A.TELPHONE, A.PSAMID AS PSAM, A.DEFRAYTYPE, A.UDPORDER,
A.TRADORDER, DECODE(A.DEFRAYTYPE, '00', '代理商支付',
'01', '銀聯POS支付', '02', '易寶POS支付', '03',
'WOPOS支付', '04', '代理預存', '其他支付') AS PAYTYPE, B.DEPUTY_NAME, B.AREA_CODE AS USER_AREACODE,
B.ARE
Plan hash value: 1374426385
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 86 | | | | |
| 2 | NESTED LOOPS | | 1 | 86 | 4 (0)| 00:00:01 | | |
| 3 | MERGE JOIN CARTESIAN | | 1 | 60 | 3 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS OUTER | | 1 | 48 | 2 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE SINGLE | | 1 | 41 | 1 (0)| 00:00:01 | 30 | 30 |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID| D_BUSIDEALLOG | 1 | 41 | 1 (0)| 00:00:01 | 30 | 30 |
|* 7 | INDEX RANGE SCAN | IDX_BUSIDEALLOG_OPRDATE | 1 | | 1 (0)| 00:00:01 | 30 | 30 |
|* 8 | INDEX UNIQUE SCAN | PK_S_CMDINTERFACE | 1 | 7 | 1 (0)| 00:00:01 | | |
| 9 | BUFFER SORT | | 11 | 132 | 2 (0)| 00:00:01 | | |
| 10 | VIEW | VW_NSO_1 | 11 | 132 | 1 (0)| 00:00:01 | | |
| 11 | HASH UNIQUE | | 11 | 176 | | | | |
|* 12 | CONNECT BY WITH FILTERING | | | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID | S_SYSAREA | 1 | 16 | 1 (0)| 00:00:01 | | |
|* 14 | INDEX UNIQUE SCAN | SYS_C006921 | 1 | | 1 (0)| 00:00:01 | | |
| 15 | NESTED LOOPS | | | | | | | |
| 16 | CONNECT BY PUMP | | | | | | | |
| 17 | TABLE ACCESS BY INDEX ROWID | S_SYSAREA | 11 | 176 | 1 (0)| 00:00:01 | | |
|* 18 | INDEX RANGE SCAN | IDX_SYSAREA_PARENTID | 11 | | 1 (0)| 00:00:01 | | |
|* 19 | TABLE ACCESS BY INDEX ROWID | S_PSAM_BASE | 1 | 26 | 1 (0)| 00:00:01 | | |
|* 20 | INDEX RANGE SCAN | IDX_PSAM_BASE_ID | 1 | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(("A"."CMD"='7504' OR "A"."CMD"='7615' OR "A"."CMD"='7805' OR "A"."CMD"='9001' OR "A"."CMD"='9002' OR
"A"."CMD"='9003' OR "A"."CMD"='9006' OR "A"."CMD"='9010' OR "A"."CMD"='9011' OR "A"."CMD"='9012' OR "A"."CMD"='9013' OR
"A"."CMD"='9016' OR "A"."CMD"='9026' OR "A"."CMD"='9030' OR "A"."CMD"='9203' OR "A"."CMD"='9204' OR "A"."CMD"='9205' OR
"A"."CMD"='9207'))
7 - access("A"."OPRDATE">='20150601000000' AND "A"."OPRDATE"<='20150602235959')
8 - access("A"."TYPE"="E"."CMD_CODE")
12 - access("PARENT_RECID"=PRIOR NULL)
14 - access("AREA_CODE"='15905')
18 - access("PARENT_RECID"=PRIOR NULL)
19 - filter("B"."AREA_CODE"="$nso_col_1")
20 - access("A"."PSAMID"="B"."PSAMID")
看SQL執行計劃是正常的
再根據檔案號,塊號查具體的爭用物件
環境:hp-unix
資料庫版本:10.2.0.5
登入資料庫查詢等待事件:
根據等待事件latch: cache buffers chains查詢相關的SQL_ID,引起爭用的物件所在的檔案號和塊號
select * from (select
count(*),
sql_id,
nvl(o.object_name,ash.current_obj#) objn,
substr(o.object_type,0,10) otype,CURRENT_FILE# fn,
CURRENT_BLOCK# blockn
from v$active_session_history ash
, all_objects o
where eve 2 3 4 5 6 7 8 9 nt like 'latch: cache buffers chains'
and o.object_id (+)= ash.CURRENT_OBJ#
group by sql_id, current_obj#, current_file#,
current_block#, o.object_name,o.object_type
order by count(*) desc )where rownum <=10;
COUNT(*) SQL_ID OBJN OTYPE FN BLOCKN
---------- -------------------------- ------------------------------ ---------------------------------------- ---------- ----------
8391 0x1p0sb3c0ryj IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
6190 bp05yyd8fsz0a IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
6160 0r9c1zgk0pn0d IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
5621 cc9r2y7s20s6z IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
4917 f9d1mg4yjchwf IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
4591 9ydcft7ttz9fc IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
4168 a0ht9ahwtz34n IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
4134 5c7cq4qx32xft IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
4133 gfunt0a1kg64k IDX_BUSIDEALLOG_OPRDATE INDEX PART 180 3908060
4078 69hcuf3rfamws -1 0 0
檢視latch: cache buffers chains引起爭用的具體會話及開始時間
select sid,username,status,sql_id,to_char(logon_time,'yyyy-mm-dd hh24:mi:ss') start_exec
from v$session s
where event='latch: cache buffers chains' order by logon_time;
經過查詢,以上的SQL是同一種型別的SQL,有30多個會話,其執行計劃如下:
SQL_ID cc9r2y7s20s6z, child number 0
-------------------------------------
SELECT count(*) from (SELECT * FROM (SELECT A.LOGID as rec_id, A.BUSFLOWNO, A.TYPE,
E.CMD_NAME, A.CONTENT, A.MONEY, A.NUM, A.ACTMONEY,
A.OPRDATE, TO_CHAR(TO_DATE(A.OPRDATE, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') AS OPRDATES, A.oprdate as b_time, A.oprdate as e_time,
A.TELPHONE, A.PSAMID AS PSAM, A.DEFRAYTYPE, A.UDPORDER,
A.TRADORDER, DECODE(A.DEFRAYTYPE, '00', '代理商支付',
'01', '銀聯POS支付', '02', '易寶POS支付', '03',
'WOPOS支付', '04', '代理預存', '其他支付') AS PAYTYPE, B.DEPUTY_NAME, B.AREA_CODE AS USER_AREACODE,
B.ARE
Plan hash value: 1374426385
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 86 | | | | |
| 2 | NESTED LOOPS | | 1 | 86 | 4 (0)| 00:00:01 | | |
| 3 | MERGE JOIN CARTESIAN | | 1 | 60 | 3 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS OUTER | | 1 | 48 | 2 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE SINGLE | | 1 | 41 | 1 (0)| 00:00:01 | 30 | 30 |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID| D_BUSIDEALLOG | 1 | 41 | 1 (0)| 00:00:01 | 30 | 30 |
|* 7 | INDEX RANGE SCAN | IDX_BUSIDEALLOG_OPRDATE | 1 | | 1 (0)| 00:00:01 | 30 | 30 |
|* 8 | INDEX UNIQUE SCAN | PK_S_CMDINTERFACE | 1 | 7 | 1 (0)| 00:00:01 | | |
| 9 | BUFFER SORT | | 11 | 132 | 2 (0)| 00:00:01 | | |
| 10 | VIEW | VW_NSO_1 | 11 | 132 | 1 (0)| 00:00:01 | | |
| 11 | HASH UNIQUE | | 11 | 176 | | | | |
|* 12 | CONNECT BY WITH FILTERING | | | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID | S_SYSAREA | 1 | 16 | 1 (0)| 00:00:01 | | |
|* 14 | INDEX UNIQUE SCAN | SYS_C006921 | 1 | | 1 (0)| 00:00:01 | | |
| 15 | NESTED LOOPS | | | | | | | |
| 16 | CONNECT BY PUMP | | | | | | | |
| 17 | TABLE ACCESS BY INDEX ROWID | S_SYSAREA | 11 | 176 | 1 (0)| 00:00:01 | | |
|* 18 | INDEX RANGE SCAN | IDX_SYSAREA_PARENTID | 11 | | 1 (0)| 00:00:01 | | |
|* 19 | TABLE ACCESS BY INDEX ROWID | S_PSAM_BASE | 1 | 26 | 1 (0)| 00:00:01 | | |
|* 20 | INDEX RANGE SCAN | IDX_PSAM_BASE_ID | 1 | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(("A"."CMD"='7504' OR "A"."CMD"='7615' OR "A"."CMD"='7805' OR "A"."CMD"='9001' OR "A"."CMD"='9002' OR
"A"."CMD"='9003' OR "A"."CMD"='9006' OR "A"."CMD"='9010' OR "A"."CMD"='9011' OR "A"."CMD"='9012' OR "A"."CMD"='9013' OR
"A"."CMD"='9016' OR "A"."CMD"='9026' OR "A"."CMD"='9030' OR "A"."CMD"='9203' OR "A"."CMD"='9204' OR "A"."CMD"='9205' OR
"A"."CMD"='9207'))
7 - access("A"."OPRDATE">='20150601000000' AND "A"."OPRDATE"<='20150602235959')
8 - access("A"."TYPE"="E"."CMD_CODE")
12 - access("PARENT_RECID"=PRIOR NULL)
14 - access("AREA_CODE"='15905')
18 - access("PARENT_RECID"=PRIOR NULL)
19 - filter("B"."AREA_CODE"="$nso_col_1")
20 - access("A"."PSAMID"="B"."PSAMID")
看SQL執行計劃是正常的
再根據檔案號,塊號查具體的爭用物件
SQL> select * from dba_extents where file_id=180 and 3908060 between block_id and block_id + blocks - 1;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------ -------------------- ---------- ---------- ---------- ---------- ---------- ------------
MINI_NEW IDX_BUSIDEALLOG_OPRDATE D_BUSIDEALLOG_201506 INDEX PARTITION MINI_PARTITION_IDX 0 180 3908057 65536 8 180
可以看出引起cbc爭用的物件是一個分割槽索引的一個分割槽,這是典型的索引熱塊爭用。
臨時處理:查詢相關的會話,kill掉;
後期處理:最佳化分割槽索引結構,本次決定將該分割槽索引修改成list分割槽。
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------ -------------------- ---------- ---------- ---------- ---------- ---------- ------------
MINI_NEW IDX_BUSIDEALLOG_OPRDATE D_BUSIDEALLOG_201506 INDEX PARTITION MINI_PARTITION_IDX 0 180 3908057 65536 8 180
可以看出引起cbc爭用的物件是一個分割槽索引的一個分割槽,這是典型的索引熱塊爭用。
臨時處理:查詢相關的會話,kill掉;
後期處理:最佳化分割槽索引結構,本次決定將該分割槽索引修改成list分割槽。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29357786/viewspace-1682168/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 等待事件_cache_buffers_chains_latch事件AI
- 處理 latch_cache_buffers_chains等待事件一例AI事件
- latch: cache buffers chainsAI
- latch:cache buffers chains案例AI
- 等待事件_cache_buffers_lru_chain_latch事件AI
- 模擬cache buffers chains與library cache pin等待事件AI事件
- 深入理解latch: cache buffers chainsAI
- Trouble shooting latch: cache buffers chainsAI
- latch:cache buffers chains解決步驟AI
- latch:cache buffers chains的優化思路AI優化
- cache buffers chains and cache buffers lru chainsAI
- buffer cache實驗6-latch:cache buffers lru chainsAI
- latch:cache buffers chains的最佳化思路AI
- latch: cache buffers chains---AWR實戰分析AI
- latch: cache buffers chains故障處理總結(轉載)AI
- 解決一例latch:cache buffers chains小記AI
- cache buffers chains vs cache buffers lru chainAI
- Cache Buffer Chain Latch等待事件AI事件
- 一次latch cache buffers chains問題的處理AI
- latch:library cache lock等待事件事件
- cache buffer lru chain latch等待事件AI事件
- Latch: cache buffer chains (%)AI
- oracle library cache相關的等待事件及latchOracle事件
- cache buffers LRU chain latchAI
- latch free(cache buffers chain)AI
- buffer busy waits, latch cache buffers chains, read by other session區別AISession
- latch: cache buffers chains-熱塊的簡單模擬實驗AI
- 用於排查cache buffers chainsAI
- Cache Buffers chains,存在共享模式?AI模式
- Bug 3797171 cache buffers chains latch contention increased in 10g-3797171.8AI
- buffer cache與相關的latch等待事件事件
- cbc latch或cache buffer chains latch系列一AI
- buffer cache實驗5-latch:cache buffers chainAI
- latch free等待事件事件
- 熱點塊競爭和解決--cache buffers chainsAI
- Latch free等待事件(轉)事件
- latch等待事件彙總事件
- latch 相關等待事件事件