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

(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
where  event# in (290,291);

    EVENT# EVENT                     WAIT_TIME_MILLI WAIT_COUNT
---------- ------------------------- --------------- ----------
       290 db file sequential read                 1       5928
       290 db file sequential read                 2       6099
       290 db file sequential read                 4        509
       290 db file sequential read                 8        640
       290 db file sequential read                16       1653
       290 db file sequential read                32       2327
       290 db file sequential read                64        506
       290 db file sequential read               128         67
       290 db file sequential read               256         20
       290 db file sequential read               512         13
       290 db file sequential read              1024          2

    EVENT# EVENT                     WAIT_TIME_MILLI WAIT_COUNT
---------- ------------------------- --------------- ----------
       291 db file scattered read                  1       4228
       291 db file scattered read                  2       2230
       291 db file scattered read                  4       1002
       291 db file scattered read                  8       2875
       291 db file scattered read                 16        616
       291 db file scattered read                 32       1040
       291 db file scattered read                 64        655
       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

OWI的缺陷

l   No CPU Statistics

l   No END-TO-END Visibility

l   No Historical Data

不完全准确！！！

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

转载于:http://blog.itpub.net/55472/viewspace-429413/