等待事件分类:
===============================================================================================================
Administrative: 等待DBA命令执行结果导致的用户等待(例如:重建索引)
Waits resulting from DBA commands that cause users to wait (for example, an index rebuild)
Application:等待用户应用程序代码结果(例如:行级锁或者显式锁命令导致的锁等待)
Waits resulting from user application code (for example, lock waits caused by row level locking or explicit lock commands)
Cluster:与RAC资源相关的等待(例如:全局缓存资源'gc cr block busy')
Waits related to Real Application Clusters resources (for example, global cache resources such as 'gc cr block busy')
Commit: 这类等待事件只包含一种等待事件-一次提交之后等待重做日志确认(日志文件同步 'log file sync')
This wait class only comprises one wait event - wait for redo log write confirmation after a commit (that is, 'log file sync')
Concurrency:等待内部数据库资源(例如:闩)
Waits for internal database resources (for example, latches)
Configuration:这种等待由数据库或者实例资源配置不足造成(例如:较小的日志文件大小,共享池大小)
Waits caused by inadequate configuration of database or instance resources (for example, undersized log file sizes, shared pool size)
Idle:这种等待表明会话不活跃,等待工作(例如:'SQL*Net message from client')
Waits that signify the session is inactive, waiting for work (for example, 'SQL*Net message from client')
Network:与网络报文传输相关的等待(例如:'SQL*Net more data to dblink')
Waits related to network messaging (for example, 'SQL*Net more data to dblink')
Other:系统中不常见的等待事件(例如:'wait for EMON to spawn')
Waits which should not typically occur on a system (for example, 'wait for EMON to spawn')
Scheduler:资源管理器相关等待(例如:'resmgr: become active')
Resource Manager related waits (for example, 'resmgr: become active')
System I/O:等待后台进程I/O(例如:DBWR等待'db file parallel write')
Waits for background process I/O (for example, DBWR wait for 'db file parallel write')
User I/O:等待用户I/O(例如:'db file sequential read')
Waits for user I/O (for example 'db file sequential read')
===============================================================================================================
等待事件信息可以查询以下三个动态性能视图:
V$SESSION_WAIT displays the events for which sessions have just completed waiting or are currently waiting.显示刚结束等待或者在进行等待的会话的等待事件
V$SYSTEM_EVENT displays the total number of times all the sessions have waited for the events in that view.显示视图中事件在所有会话中的等待总次数
EVENT:等待事件名称;TOTAL_WAITS:事件总等待次数;TIME_WAITED:事件的总等待时间(单位:百分之一秒)
AVERAGE_WAIT:事件的平均等待用时(单位:百分之一秒)(time_waited/total_waits); TOTAL_TIMEOUTS:事件总等待超时次数
V$SESSION_EVENT is similar to V$SYSTEM_EVENT, but displays all waits for each session. 显示每次会话中的所有等待。