Oracle几个优化有用的视图

Oracle几个优化有用的视图

目的：不要死记硬背script，深入理解view的含义，才能自如运用它们。
包括：V$SYSTEM_EVENT/ V$SESSION_EVENT/ V$SESSION_WAIT / V$WAITSTAT
http://download-west.Oracle.com/docs/cd/B10501_01/server.920/a96533/sqlviews.htm#17389

V$SYSTEM_EVENT（统计）
自从instance启动以后，所有产生的（若没有等待则不显示）等待事件event 总的等待wait的统计信息。
SQL> desc v$system_event
Name                         
------------------------
EVENT                         
TOTAL_WAITS        --总等待次数
TOTAL_TIMEOUTS    --等待超时次数           
TIME_WAITED        --总的等待时间，单位是百分之一秒 （最重要考虑）       
AVERAGE_WAIT      -- 平均等待时间，总的等待时间/总等待次数           
TIME_WAITED_MICRO --总的等待时间的微秒形式 1/1000000   

常见的等待现象：
latch free
enqueue
free buffer waits
buffer busy waits: 查看V$WAITSTAT
db file sequential read
db file scattered read

Script:
select * from
(select EVENT, TOTAL_WAITS ,TOTAL_TIMEOUTS ,TIME_WAITED , AVERAGE_WAIT
from v$system_event order by TIME_WAITED desc )
where rownum < 21;

发现瓶颈
1．Statspack lists idle events at the end.
2．Examine the time spent waiting for different events.
3．Examine the average time for each wait also, because some waits (like log file switch completion) might happen only periodically, but cause a big performance hit when they happen.

V$SESSION_EVENT （统计）
字段比V$SYSTEM_EVENT多一个sid, 提供各个session 所有等待过事件的统计信息。而当前等待的信息在v$session_wait中

V$SESSION_WAIT （当前）
系统当前的每个活动session ( v$session )当前正在等待什么（如果现在没有等待任何资源，在显示最后一次等待的情况）
• SID: session id
• EVENT: Event the session is currently waiting for, or the last event the session had to wait for
• WAIT_TIME: Time (in hundredths of a second) that the session waited for the event; if the WAIT_TIME is 0, then the session is currently waiting for the event
• SEQ#: Gets incremented with every wait of the session
• P1, P2, P3: Wait event specific details for the wait
• P1TEXT, P2TEXT, P3TEXT: Description of P1,P2,P3 for the given event

WAIT_TIME Meaning                                                  Waiting
>0 Time waited in the last wait (in 10 ms clock ticks)      No
0 Session is currently waiting for this event                  Yes
-1 Time waited in the last wait was less than 10 ms        No
-2 Timing is not enabled                                                No

Script:
select * from
(select sid, event ,p1,p2,p3 ,WAIT_TIME, SECONDS_IN_WAIT
from v$session_wait
where SID = &THE_SID
order by wait_time desc )
where rownum < 21;