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

1.创建测试数据.

[html]  view plain copy

1. create table t1 as select rownum id from dba_objects;  
2. CREATE INDEX T1_IDX ON T1(ID) ;  

2.创建两个存储过程.

[html]  view plain copy

1. create or replace  
2. PROCEDURE pselect2  
3. AS  
4. l_num number;  
5. BEGIN  
6.   FOR i IN 1..1000000  
7.   LOOP  
8.     SELECT count(*) into l_num FROM T1 where t1.id between 20000 and 30000;  
9.   END LOOP;  
10. END;  
11.   
12.   
13. create or replace  
14. procedure pupdate2  
15. AS  
16. BEGIN  
17.   FOR i IN 1..1000000  
18.   LOOP  
19.     UPDATE t1 SET id = rownum where t1.id  between 20000 and 30000;  
20.     COMMIT;  
21.   END LOOP;  
22. 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 plain copy

1. SELECT EVENT,  
2.   P1,  
3.   P1RAW,  
4.   P2,  
5.   P2RAW,  
6.   state  
7. FROM v$session_wait  
8. WHERE sid IN  
9.   (SELECT SID FROM V$SESSION WHERE USERNAME='KIN'  
10.   );  
11.     
12. EVENT                                       P1 P1RAW                    P2 P2RAW               STATE  
13. ------------------------------ ---------- ---------------- ---------- ---------------- --------------------  
14. latch: cache buffers chains    8468531136 00000001F8C387C0          150 0000000000000096 WAITED SHORT TIME  
15. latch: cache buffers chains    8066496552 00000001E0CCF828          150 0000000000000096 WAITED SHORT TIME  
16. latch: cache buffers chains    8064062352 00000001E0A7D390          150 0000000000000096 WAITED SHORT TIME  
17. SQL*Net message from client    1413697536 0000000054435000            1 0000000000000001 WAITING  
18. latch: cache buffers chains    8467468736 00000001F8B351C0          150 0000000000000096 WAITED SHORT TIME  
19. latch: cache buffers chains    8468531136 00000001F8C387C0          150 0000000000000096 WAITED SHORT TIME  
20. latch: cache buffers chains    8064654952 00000001E0B0DE68          150 0000000000000096 WAITED SHORT TIME  

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

[html]  view plain copy

1. SELECT name,  
2.   PARAMETER1,  
3.   PARAMETER2,  
4.   PARAMETER3  
5. FROM V$EVENT_NAME  
6. WHERE name='latch: cache buffers chains';  
7. NAME                               PARAMETER1                      PARAMETER2                     PARAMETER3  
8. ------------------------------ ------------------------------ ------------------------------ ------------------------------  
9. latch: cache buffers chains    address                              number                             tries  

5.查询对应热块

[html]  view plain copy

1. SELECT  
2.   /*+ RULE */  
3.   E.OWNER  
4.   || '.'  
5.   || E.SEGMENT_NAME SEGMENT_NAME,  
6.   E.PARTITION_NAME,  
7.   E.EXTENT_ID EXTENT#,  
8.   X.DBABLK - E.BLOCK_ID + 1 BLOCK#,  
9.   X.TCH,  
10.   L.CHILD#  
11. FROM SYS.V$LATCH_CHILDREN L,  
12.   SYS.X$BH X,  
13.   SYS.DBA_EXTENTS E  
14. WHERE X.HLADDR='00000001F8C387C0'  
15. AND E.FILE_ID = X.FILE#  
16. AND X.HLADDR  = L.ADDR  
17. AND X.DBABLK BETWEEN E.BLOCK_ID AND E.BLOCK_ID + E.BLOCKS - 1  
18. ORDER BY X.TCH DESC;    
19.   
20.   
21. SEGMENT_NAME                       PARTITION_NAME                         EXTENT#     BLOCK#           TCH           CHILD#  
22. ------------------------------ ------------------------------ ---------- ---------- ---------- ----------  
23. SYS.I_OBJ4                                                              17         67             1            30264  
24. SYS.WRH$_SYSMETRIC_HISTORY_INDEX                  20        117             1            30264  
25. KIN.T1_IDX                                                               7          2             0            30264  
26. KIN.T1_IDX                                                               7          2            93            30264  
27. KIN.T1_IDX                                                               7          2             1            30264  
28. KIN.T1_IDX                                                               7          2             1            30264  
29. KIN.T1_IDX                                                               7          2             1            30264  
30. KIN.T1_IDX                                                               7          2             1            30264  
31. KIN.T1_IDX                                                               7          2             1            30264  

9 rows selected.

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22990797/viewspace-754844/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22990797/viewspace-754844/