ORACLE等待事件latch: cache buffers chains

清風艾艾發表於2015-06-02
    今天上午,電渠生產庫維護人員通知,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分割槽。

   

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29357786/viewspace-1682168/,如需轉載,請註明出處,否則將追究法律責任。

相關文章