latch: cache buffers chains-熱塊的簡單模擬實驗

n-lauren發表於2013-02-27
AI
1.建立測試資料.
[html] view plaincopy
<embed>
  1. create table t1 as select rownum id from dba_objects;  
  2. CREATE INDEX T1_IDX ON T1(ID) ;  

2.建立兩個儲存過程.
[html] view plaincopy
<embed>
  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 plaincopy
<embed>
  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 plaincopy
<embed>
  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 plaincopy
<embed>
  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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章