Oracle Wait Interface学习笔记(2) OWI Componets(8)

上一篇 / 下一篇 2008-08-15 15:19:26 / 个人分类：性能调整

查看( 36 ) / 评论( 0 ) / 评分( 0 / 0 )

(OWI学习笔记索引)

l V$EVENT_HISTOGRAM

这个动态视图保留了各等待事件的等待时间分布图，统计的依据是wait_time_milli字段，是预定义的：1ms,2ms,4ms,8ms,16ms…2²²ms

SQL> DESC V$EVENT_HISTOGRAM

Name Type

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

EVENT# NUMBER

EVENT VARCHAR2(64)

WAIT_TIME_MILLI NUMBER

WAIT_COUNT NUMBER

例子：针对db file sequential read事件作的统计，从instance启动至今，对该事件的等待<1ms的次数是5928次，<2ms的次数是6099次，如下的分布基本来说还是比较健康的。

select *
from  v$event_histogram
3o\,UAw+YQ{0where event# in (290,291);ITPUB个人空间/kCjw b0SF
ITPUB个人空间0Yv.O"M H#lF U
   EVENT# EVENT                    WAIT_TIME_MILLI WAIT_COUNTITPUB个人空间"Y[.r)T![.\
---------- ------------------------- --------------- ----------
-i`t6ki)h0      290 db file sequential read                1      5928
K Bh;W2i-K0      290 db file sequential read                2      6099
MA7|-?4Z,o0      290 db file sequential read                4       509ITPUB个人空间8~!g.e&?Bh
      290 db file sequential read                8       640
#m%K!P{x{*k0      290 db file sequential read               16      1653
Q*V.f*Q g0      290 db file sequential read               32      2327ITPUB个人空间7l n%I1p+W9~
      290 db file sequential read               64       506ITPUB个人空间@'h u0R+R|,EO
      290 db file sequential read              128        67ITPUB个人空间`I4AV8}J.a
      290 db file sequential read              256        20
/gV _?l0n6Nu%I0      290 db file sequential read              512        13
w[F&~5c v] ?0      290 db file sequential read             1024         2ITPUB个人空间vwWc"BF,f,Z!qO
ITPUB个人空间s%HBw1C&~vR
   EVENT# EVENT                    WAIT_TIME_MILLI WAIT_COUNTITPUB个人空间S)Xgm;`"N
---------- ------------------------- --------------- ----------ITPUB个人空间m;dn gc(w9S G
      291 db file scattered read                 1      4228ITPUB个人空间%F tCOnhT1f)X+~A
      291 db file scattered read                 2      2230ITPUB个人空间|1BhaH+p1@(\S
      291 db file scattered read                 4      1002
Jt+^qF i3{lHRS0dc*R0      291 db file scattered read                 8      2875
d+[s-^wo{Y/ru0      291 db file scattered read                16       616
JH R.{1N3AB?K0      291 db file scattered read                32      1040ITPUB个人空间4[^s(ow"n%P-v!E
      291 db file scattered read                64       655
:y o-q/A8b0      291 db file scattered read               128       144

l Type of Wait Events

在10g之前，等待事件没有明确的分类，一般就是简单分为Foreground(V$session.type=’USER’)、Backgroud(V$session.type=’BACKGROUND’)、IDLE和non-idle。一个等待事件可能同时属于几个分类。

对于Idle事件，一般情况下可以忽略。

Table 2-1: Non-Idle Wait Events (Not a Complete List)

db file sequential read (F, B)		db file scattered read (F, B)
direct path read (F, B)		direct path write (F, B)
db file parallel write (B)		log file parallel write (B)
control file parallel write (B)		write complete waits (F, B)
free buffer waits (F)		log buffer space (F)
latch free (F, B)		log file sync (F)
enqueue (F, B)		buffer busy waits (F, B)
SQL*Net more data to client (F)		SQL*Net message to client (F)
SQL*Net more data from client (F)

Table 2-2: Idle Wait Events (Not a Complete List)

PL/SQL lock timer	all events beginning with PX Deq
PX idle wait	SQL*Net message from client
SQL*Net message from dblink	dispatcher timer
jobq slave wait	pipe get
pmon timer	queue messages
rdbms ipc message	single-task message
smon timer	virtual circuit status
wait for activate message	wait for transaction
wakeup event for builder	wakeup event for preparer
wakeup event for reader	wakeup time manager
Null event