Oracle10g New Feature -- 11. Wait Interface

本章内容同上一章一样,都是针对数据库性能。本章更注重对等待事件的分析。

视图v$session_wait, v$session, v$session_wait_class,  v$system_wait_class, v$session_wait_history帮助用户取得关于等待事件的各种详细信息

[@more@]

Wait Interface

1.       Enhancement in V$SESSION_WAIT

Added 3 columns:

 

2.       Enhancement in V$SESSION

    Event wait information is also included in view v$session, like BLOCKING_SESSION_STATUS, BLOCKING_SESSION, SEQ#, EVENT#, EVENT, P1TEXT, P1, P1RAW, P2TEXT, P2, P2RAW, P3TEXT, P3, P3RAW, WAIT_CLASS_ID, WAIT_CLASS#, WAIT_CLASS, WAIT_TIME, SECONDS_IN_WAIT, STATE, SERVICE_NAME.

    So, if a session is waiting for a lock holding by another session, you can issue the following query:

SQL> select BLOCKING_SESSION_STATUS, BLOCKING_SESSION from v$session where sid=216;

 
 

 
 

 
 

3.       How Many Waits?

    We can get more precise wait information by issue the following query:

SQL>select * from v$session_wait_class where sid = 269;

SID SERIAL# WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS    TOTAL_WAITS TIME_WAITED

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

 269    1106    4217450380           1 Application           873      261537

 269    1106    3290255840           2 Configuration           4           4

 269    1106    3386400367           5 Commit                  1           0

 269    1106    2723168908           6 Idle                   15      148408

 269    1106    2000153315           7 Network                15           0

 269    1106    1740759767           8 User I/O               26           1

the session has waited 873 times for a total of 261,537 centi-seconds for application-related waits, 15 times in network-related events, and so on.

 

    System wide statistics for wait classes(we also can use this query to get wait_class_id and wait_class list):

SQL>select * from v$system_wait_class;

WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS    TOTAL_WAITS TIME_WAITED

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

   1893977003           0 Other                2483       18108

   4217450380           1 Application          1352      386101

   3290255840           2 Configuration          82         230

   3875070507           4 Concurrency            80         395

   3386400367           5 Commit               2625        1925

   2723168908           6 Idle               645527   219397953

   2000153315           7 Network              2125           2

   1740759767           8 User I/O             5085        3006

   4108307767           9 System I/O         127979       18623

 

To show the metric values of wait classes for the most recent 60-second interval

SQL>select * from V$WAITCLASSMETRIC

To show the metric value of wait classes for all intervals in the last one hour:

SQL>select * from V$WAITCLASSMETRIC_HISTORY

 

4.       Session Wait History:

V$SESSION_WAIT_HISTORY automatically maintains the last 10 wait event for active sessions.

SQL>select event, wait_time, wait_count

from v$session_wait_history

where sid = 265

/

EVENT                           WAIT_TIME WAIT_COUNT

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

log file switch completion              2          1

log file switch completion              1          1

log file switch completion              0          1

SQL*Net message from client         49852          1

SQL*Net message to client               0          1

enq: TX - row lock contention          28          1

SQL*Net message from client           131          1

SQL*Net message to client               0          1

log file sync                           2          1

log buffer space                        1          1

 

 

 

 

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

转载于:http://blog.itpub.net/207/viewspace-778841/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值