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#17389V$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 readScript:
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 eventWAIT_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 NoScript:
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;

Oracle递归START WITH...CONNECT BY PRIOR子句用法Oracle RAC 环境下修改主机名相关资讯 Oracle视图

Oracle学习笔记之视图（08/30/2015 19:05:48）
Oracle DBA_EXTENTS视图与 DBA_ （05/06/2015 10:48:42）
oracle desc dba_data_files视图报（05/21/2014 07:27:51）

Oracle通过视图和告警日志检查主库（07/20/2015 21:11:35）
Oracle 数据库视图与基表的关系（03/18/2015 20:24:41）
Oracle视图基础详解与实例（02/28/2014 18:48:27）

本文评论查看全部评论（0）