Oracle動態效能檢視學習筆記(4)_v$waitstat

參考文件<>Chapter 24

##################################################################
1 Overview
##################################################################
This view keeps a summary all buffer waits since instance startup. It is useful for breaking down the waits by class

if you
see a large number busy waits on the system.

記錄自系統啟動以來的等待。

##################################################################
2 Useful Columns for v$waitstat
##################################################################
1) CLASS: Class of block(Data segment header, undo segment header, data block)

2) COUNT: Number fo waits for this class of blocks.

3) TIME: Total time of waited for this class of block.(second)

##################################################################
2 Reasons for Waits 出現等待的原因
##################################################################
1) Undo segment header: not enough rollback segments

2) Data segment header/freelist: freelist contention

3) Data block

4) Large number of CR clones for the buffer.

5) Range scan on indexes with large number of deletions

6) Full table scans on tables with large number of deleted rows

7) Blocks with high concurrency

示例：

SQL> select * from v$waitstat;

CLASS                   COUNT       TIME
------------------ ---------- ----------
data block                  6          2
sort block                  0          0
save undo block             0          0
segment header              0          0
save undo header            0          0
free list                   0          0
extent map                  0          0
1st level bmb               0          0
2nd level bmb               0          0
3rd level bmb               0          0
bitmap block                0          0
bitmap index block          0          0
file header block           0          0
unused                      0          0
system undo header          0          0
system undo block           0          0
undo header                22          4
undo block                  0          0

Oracle動態效能檢視學習筆記(4)_v$waitstat

相關文章