10g开始OWI有了很多功能加强,比如:wait event分类,历史数据等。10g新增了几个动态视图,对V$session的功能也有所加强。
l V$SESSION_WAIT_HISTORY
保留了每个session最近10个等待事件的信息,每1秒刷新一次。
SQL> desc v$session_wait_history
Name Type
------------------- ----------------------------
SID NUMBER
SEQ# NUMBER
EVENT# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P2TEXT VARCHAR2(64)
P2 NUMBER
P3TEXT VARCHAR2(64)
P3 NUMBER
WAIT_TIME NUMBER
WAIT_COUNT NUMBER
Wait_time=0表示session正在等待该事件,如果>0则表示session最后一次等待该事件的时间。
Wait_count记录session等待该事件的次数,但是,为什么都是0呢?
对于处于active状态的Session,更多信息可以查看 V$ACTIVE_SESSION_HISTORY
l V$SYSTEM_WAIT_CLASS
SQL> DESC V$SYSTEM_WAIT_CLASS
Name Type
--------------------------------------------------
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
TOTAL_WAITS NUMBER
TIME_WAITED NUMBER
不太明白下面两个查询有什么区别,似乎结果还稍有差异??
SELECT * FROM V$SYSTEM_WAIT_CLASS
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED
------------- ----------- ---------- ----------- -----------
1893977003 0 Other 19748 7611
4217450380 1 Application 1250 2965
3290255840 2 Configuration 1144 329
3875070507 4 Concurrency 4506 48555
3386400367 5 Commit 223439 24126
2723168908 6 Idle 11447350 4147422259
2000153315 7 Network 1032589 93587
1740759767 8 User I/O 317564 80257
4108307767 9 System I/O 3802447 199997
SELECT WAIT_CLASS_ID,WAIT_CLASS#,WAIT_CLASS,SUM(TOTAL_WAITS),SUM(TIME_WAITED_MICRO)
FROM V$SYSTEM_EVENT
GROUP BY WAIT_CLASS_ID,WAIT_CLASS#,WAIT_CLASS
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS SUM(TOTAL_WAITS) SUM(TIME_WAITED_MICRO)
------------- ----------- ---------- ----------- -----------
1893977003 0 Other 11293 74210776
4217450380 1 Application 1250 29645783
3290255840 2 Configuration 1144 3286503
3875070507 4 Concurrency 4506 485554670
3386400367 5 Commit 223440 241259062
2723168908 6 Idle 11447408 41474353756980
2000153315 7 Network 1032593 935871852
1740759767 8 User I/O 317564 802574769
4108307767 9 System I/O 3802462 1999973852
l V$SESSION_WAIT_CLASS
SQL> desc v$session_wait_class
Name Type
---------------------- ----------------------------
SID NUMBER
SERIAL# NUMBER
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
TOTAL_WAITS NUMBER
TIME_WAITED NUMBER
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/55472/viewspace-429411/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/55472/viewspace-429411/