视图优化oracle,Oracle几个优化有用的视图

V$WAITSTAT(统计)

a summary all block buffer waits since instance startup。适于查找buffer busy waits

Column:

class: Class of block (data segment header, undo segment header, data block……)

Count: Number of waits for this class of blocks

time: Total time waited for this class of block

Reason:

Undo segment header: not enough rollback segments

Data segment header/freelist: freelist contention

Data block:

1. Large number of CR clones for the buffer

2. Range scans on indexes with large number of deletions

3. Full table scans on tables with large number of deleted rows

4. Blocks with high concurrency, hot block

Oracle10g:v$session_wait_history视图

SQL> select event,p1text,p1,p2text,p2,p3text,p3,wait_time

2  from v$session_wait_history where sid=120;

EVENT                        P1TEXT        P1 P2TEXT                P2 P3TEXT  P3  WAIT_TIME

---------------------------- -------- -------- --------------- ---------- -------- -- ----------

db file sequential read      file#          14 block#              97456 blocks    1          0

row cache lock            cache id          11 mode                    0 request  3        49

row cache lock            cache id          11 mode                    0 request  3          0

db file sequential read      file#          10 block#              260171 blocks    1          1

db file sequential read      file#          14 block#              570536 blocks    1        10

db file sequential read      file#          14 block#                6363 blocks    1        12

db file sequential read      file#          14 block#              35285 blocks    1          9

db file sequential read      file#          14 block#              40674 blocks    1          9

db file sequential read      file#          14 block#              69631 blocks    1          1

db file sequential read      file#          14 block#              82498 blocks    1          3

10 rows selected

为了更有效的保留Session信息，Oracle10g新增加了一个v$session_wait_history视图

该视图用以记录活动Session的最近10次等待事件。

通过这个视图，Oracle将V$SESSION_WAIT延伸，从而可以获取更多的相关信息辅助数据库问题诊断。

这是Oracle10g中在ASH之外对于等待事件诊断的一个有益小改进。