1.创建测试数据.

[html]view plaincopy
create table t1 as select rownum id from dba_objects;  
CREATE INDEX T1_IDX ON T1(ID) ;  

2.创建两个存储过程.

[html]view plaincopy
create or replace  
PROCEDURE pselect2  
AS  
l_num number;  
BEGIN  
  FOR i IN 1..1000000  
  LOOP  
    SELECT count(*) into l_num FROM T1 where t1.id between 20000 and 30000;  
  END LOOP;  
END;  
  
  
create or replace  
procedure pupdate2  
AS  
BEGIN  
  FOR i IN 1..1000000  
  LOOP  
    UPDATE t1 SET id = rownum where t1.id  between 20000 and 30000;  
    COMMIT;  
  END LOOP;  
END;  

3.简单模拟几个并发
session 1:exec pselect2;
session 2:exec pselect2;
session 3:exec pselect2;
session 4:exec pupdate2;
session 5:exec pupdate2;
session 6:exec pupdate2;
...

4.查询等待事件

[html]view plaincopy
SELECT EVENT,  
  P1,  
  P1RAW,  
  P2,  
  P2RAW,  
  state  
FROM v$session_wait  
WHERE sid IN  
  (SELECT SID FROM V$SESSION WHERE USERNAME='KIN'  
  );  
    
EVENT                                       P1 P1RAW                    P2 P2RAW               STATE  
------------------------------ ---------- ---------------- ---------- ---------------- --------------------  
latch: cache buffers chains    8468531136 00000001F8C387C0          150 0000000000000096 WAITED SHORT TIME  
latch: cache buffers chains    8066496552 00000001E0CCF828          150 0000000000000096 WAITED SHORT TIME  
latch: cache buffers chains    8064062352 00000001E0A7D390          150 0000000000000096 WAITED SHORT TIME  
SQL*Net message from client    1413697536 0000000054435000            1 0000000000000001 WAITING  
latch: cache buffers chains    8467468736 00000001F8B351C0          150 0000000000000096 WAITED SHORT TIME  
latch: cache buffers chains    8468531136 00000001F8C387C0          150 0000000000000096 WAITED SHORT TIME  
latch: cache buffers chains    8064654952 00000001E0B0DE68          150 0000000000000096 WAITED SHORT TIME  

5.查询等待事件参数定义

[html]view plaincopy
SELECT name,  
  PARAMETER1,  
  PARAMETER2,  
  PARAMETER3  
FROM V$EVENT_NAME  
WHERE name='latch: cache buffers chains';  
NAME                               PARAMETER1                      PARAMETER2                     PARAMETER3  
------------------------------ ------------------------------ ------------------------------ ------------------------------  
latch: cache buffers chains    address                              number                             tries  

5.查询对应热块

[html]view plaincopy
SELECT  
  /*+ RULE */  
  E.OWNER  
  || '.'  
  || E.SEGMENT_NAME SEGMENT_NAME,  
  E.PARTITION_NAME,  
  E.EXTENT_ID EXTENT#,  
  X.DBABLK - E.BLOCK_ID + 1 BLOCK#,  
  X.TCH,  
  L.CHILD#  
FROM SYS.V$LATCH_CHILDREN L,  
  SYS.X$BH X,  
  SYS.DBA_EXTENTS E  
WHERE X.HLADDR='00000001F8C387C0'  
AND E.FILE_ID = X.FILE#  
AND X.HLADDR  = L.ADDR  
AND X.DBABLK BETWEEN E.BLOCK_ID AND E.BLOCK_ID + E.BLOCKS - 1  
ORDER BY X.TCH DESC;    
  
  
SEGMENT_NAME                       PARTITION_NAME                         EXTENT#     BLOCK#           TCH           CHILD#  
------------------------------ ------------------------------ ---------- ---------- ---------- ----------  
SYS.I_OBJ4                                                              17         67             1            30264  
SYS.WRH$_SYSMETRIC_HISTORY_INDEX                  20        117             1            30264  
KIN.T1_IDX                                                               7          2             0            30264  
KIN.T1_IDX                                                               7          2            93            30264  
KIN.T1_IDX                                                               7          2             1            30264  
KIN.T1_IDX                                                               7          2             1            30264  
KIN.T1_IDX                                                               7          2             1            30264  
KIN.T1_IDX                                                               7          2             1            30264  
KIN.T1_IDX                                                               7          2             1            30264  

9 rows selected.

至此模拟出索引热块场景.

latch: cache buffers chains-热块的简单模拟实验