今天上午，電渠生產庫維護人員通知，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執行計劃是正常的
再根據檔案號，塊號查具體的爭用物件

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分割槽。

ORACLE等待事件latch: cache buffers chains

相關文章