Oracle Wait Interface

1.Introduction
以往性能调整常用的一些指标:
* Buffer cache>90%
* Data dictionary misses<10%
* Sqlarea get hit ratio and pin hit ratio>90% (ratio of reloads<1%)
* willing-to-wait latch hit ratios shalt be close to 1
但是光靠这些指标是不够的,在现实中,高缓存命中率并不总是意味着性能良好.
在Oracle数据库10g性能调优指南中有如下描述:
When tuning, it is common to compute a ratio that helps determine whether there is a problem. Such ratios include the buffer cache-hit ratio, the soft-parse ratio, and the latch-hit ratio. These ratios should not be used as’hard and fast’identifiers of whether there is or is not a performance bottleneck. Rather, they should be used as indicators. In order to identify whether there is a bottleneck, other related evidence should be examined.
Oracle从7.0.12开始推出OWI,当时有104个等待事件,到9i发展到400个,到10g已发展到800个.事实上,10g的Enterprise Manager Performance中已经不再有buffer cache hit ratio.
OWI是一种用于定位进程瓶颈(即wait evennts)的方式,包括I/O,locks,latches,bk process activities,network latencies等等.它记录了所有这些事件的等待次数和总的等待时间,解除或者降低这些等待都会使系统性能得到提高.这些数据都被记录在动态视图中.
有了OWI我们就可以快速的定位问题,而在OWI之前,要定位问题必须将检查列表上的所有项目都执行一遍,再根据经验判断问题所在,这往往浪费大量的时间而且容易产生错误.
数据库响应时间结构(Database Response Time Tuning Model)
Response Time = Service Time + WaitTime
服务时间(Service Time)是指该进程使用CPU的时间量.
等待时间(Wait Time)是指该进程在继续处理之前等待可用特定资源所花费的时间量.
然而从端到端的响应时间要多于数据库响应时间.端到端响应时间包括服务器延迟时间(如队列时间,上下文切换和内存管理等)以及网络和中间层延迟时间.但我们仅需查看数据库响应时间,可以将前面的公式重写如下:
Response Time = CPU used when call started + ∑ TIME_WAITED
在会话级,服务时间指V$SESSTAT视图中的CPU used when call started统计数据,而等待时间则是V$SESSION_EVENT视图中特定会话的所有前台相关等待事件的TIME_WAITED之和.
开始调用后使用的CPU时间统计包含3类:
用于解析的CPU时间-使用统计parse time CPU追踪
用于递归调用的CPU时间-使用统计recursive cpu usage追踪
用于正常工作的CPU时间- (CPU_W)可计算如下:
CPU_W = CPU used when call started – parse time CPU – recursive cpu usage
可使用如下SQL来获取用户进程的response time:
SELECT b.NAME, sum(a.VALUE) AS time_spent
FROM v$sesstat a, v$statname b
WHERE a.statistic# = b.statistic#
AND b.NAME = ‘CPU used when call started’
AND a.sid = &sid
group by b.NAME;
UNION ALL
SELECT event, sum(time_waited)/100 AS time_spent
FROM v$session_event
WHERE sid = &sid
group by event
注意:CPU统计仅在每次调用结束时更新,而等待事件统计则是实时更新.因此长时间运行的SQL语句直到它从调用中返回时才会增加CPU统计.这意味着数据库响应时间的计算精确度极大的取决于何时采样统计值.

2.OWI Components
Oracle能做到自我监控但是做不到自我调整,oracle收集了大量的性能数据,包括instance级和session级.
什么是等待事件?
Session连接到oracle instance后需要获取某些资源来完成它的任务,这里所说的资源可能是data buffer,latch,enqueue(lock),pin或者db object.每当会话必须等待某些资源,等待时间会被追踪并由与那个等待关联的事件负责.
例1:session需要一个不存在于SGA中的index block,它产生一个对操作系统的读调用并等待索引块的递送.等待的时间被记录到db file sequential read.
例2:session完成了任务正处于idle状态,等待用户的输入.此时,等待时间被记入SQL*Net message from client.
简要地说,当一个会话没有使用CPU时,它可能正在等待一个资源,一个待完成的工作,或者仅仅是更多的工作.因此,所有这类与等待相关联的事件都称为等待事件.

OWI Components
OWI是由一系列的动态性能视图和一个扩展SQL Trace文件组成的.
OWI提供了对wait events的统计信息,如等待某个资源的次数以及等待某个资源的总时间.获取等待累计时间的前提是将TIMED_STATISTICS参数设为TRUE,现在设置这个参数已经不会带来系统压力,因为绝大多数的服务器都提供硬件级的fast timer支持.
OWI主要包括以下动态性能视图:
V$EVENT_NAME
V$SESSION_WAIT(*)
V$SESSION_EVENT(*)
V$SYSTEM_EVENT(*)
V$SYSTEM_WAIT_CLASS(10G新增)
V$SESSION_WAIT_CLASS(10G新增)
V$SESSION_WAIT_HISTORY(10G新增)
V$EVENT_HISTOGRAM(10G新增)
V$ACTIVE_SESSION_HISTORY(10G新增)
然而V$SESSION_WAIT,V$SESSION_EVENT,V$SYSTEM_EVENT仍然是3个重要的动态视图.最低粒度级由V$SESSION_WAIT视图提供,该视图的信息汇总到V$SESSION_EVENT视图,进而到V$SYSTEM_EVENT视图.这种关系可表达如下:
V$SESSION_WAIT→V$SESSION_EVENT→V$SYSTEM_EVENT
对这些视图的查询往往是反复的,可以使用扩展SQL Trace功能将这些信息抓取到trace文件中,然后用tkprof工具分析.

2.1.V$EVENT_NAME
SQL> desc v$event_name
Name          Type
————- ————
EVENT#        NUMBER
EVENT_ID      NUMBER
NAME          VARCHAR2(64)
PARAMETER1    VARCHAR2(64)
PARAMETER2    VARCHAR2(64)
PARAMETER3    VARCHAR2(64)
WAIT_CLASS_ID NUMBER
WAIT_CLASS#   NUMBER
WAIT_CLASS    VARCHAR2(64)
NAME列各版本基本一致;EVENT#是事件的唯一编号,因为是由Oracle代码中的编译时间宏生成,所以对于同一等待事件在各版本并不同;10g新增的EVENT_ID是基于事件名称的散列值,因此不随版本变更而变更.
每一个event可以由最多3个属性组成,被记录在PARAMETER1-3中.这三列在v$session_wait中对应的是P1TEXT、P2TEXT和P3TEXT,而3个属性的值则纪录在P1,P2和P3.
10G开始对event做了分类,目前共有12个分类:
SQL> select wait_class#, wait_class_id, wait_class
from  v$event_name
group by wait_class#, wait_class_id, wait_class;
WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS
———– ————- ——————————
10   2396326234 Scheduler
4   3875070507 Concurrency
8   1740759767 User I/O
0   1893977003 Other
6   2723168908 Idle
1   4217450380 Application
2   3290255840 Configuration
11   3871361733 Cluster
3   4166625743 Administrative
9   4108307767 System I/O
7   2000153315 Network
5   3386400367 Commit
12 rows selected.

2.2.V$SYSTEM_EVENT
此视图显示自从实例启动后所有Oracle会话遇到的所有等待事件的总计.它追踪等待的总次数,总超时,以及任意会话遭遇到的任一等待事件的等待时间.
SQL> desc v$system_event
Name                Type
——————– ———————
EVENT               VARCHAR2(64)
TOTAL_WAITS         NUMBER       等待总次数
TOTAL_TIMEOUTS      NUMBER
TIME_WAITED         NUMBER       等待总时间=time_waited_micro/10000
AVERAGE_WAIT        NUMBER    平均等待时间=time_waited/total_waits
TIME_WAITED_MICRO   NUMBER     (单位:microseconds,1/1,000,000秒)
*EVENT_ID            NUMBER
*WAIT_CLASS_ID       NUMBER
*WAIT_CLASS#         NUMBER
*WAIT_CLASS          VARCHAR2(64)
有些event有timeout属性,例如free buffer waits有一个Timeout的时间为100centisecond,当一个session处于free buffer waits事件超过100centisecond后,total_timeouts列会+1,同时,该Session开始第二个free buffer waits等待.而有一些event,特别是I/O相关的,都没有timeout的属性,这意味着session会一直处于等待状态直到获取所需要的资源.
v$system_event使用时注意:
* v$system_event提供的是instance级的事件统计信息,没有session级的信息;instance启动的时间越长,其累计的time_waited列值越大;
* 我们需要关注time_waited列,而非total_waits列.有些event如latch free会有非常高的total_waits,但是每一次的等待时间都非常短,而有些event如enqueue虽然total_waits很小,但是每一次的等待时间却很长;
set lines 160
set numwidth 18
col class for a15
col event for a30
col total_waits for 999,999,999
col total_timeouts for 999,999,999
col time_waited for 999,999,999,999
col average_wait for 999,999,999,999
select b.wait_class, a.*, c.startup_time
from  v$system_event a,
v$event_name b,
v$instance c
where a.event = b.name
order by b.wait_class, a.time_waited desc;
WAIT_CLASS  EVENT  TOTAL_WAITSTIME_WAITEDAVERAGE_WAIT   STARTUP_TIME
User I/Odb file sequential read195100 50336  0.26   2008-7-16 11:17:16
User I/Odb file scattered read 32096  16345  0.51   2008-7-16 11:17:16
特别注意和User I/O及System I/O相关的event,这些event反映了你系统的速度和IO Cost.
SQL> SELECT NAME FROM V$EVENT_NAME
2 WHERE WAIT_CLASS=’User I/O’ or WAIT_CLASS=’System I/O’;
获取阶段数据:可以使用statspack获取,也可以通过脚本来获取:
— Assumption is that you have TOOLS tablespace in your database.
— Create Begin and End tables to store V$SYSTEM_EVENT contents for
— time T1 and T2 to compute delta.
— ===================================
— You only need to create these tables once.
— ===================================
create table begin_system_event tablespace tools
as select * from  v$system_event where 1=2;
create table end_system_event tablespace tools
as select * from  v$system_event where 1=2;
— Take a snapshot of V$SYSTEM_EVENT information at time T1
truncate table begin_system_event;
insert into begin_system_event
select * from  v$system_event;
— Wait n seconds or n minutes, and then take another snapshot
— of V$SYSTEM_EVENT at time T2
truncate table end_system_event;
select * from  v$system_event;
— Report the ‘delta’ numbers for wait events between times T2 and T1
select t1.event,
(t2.total_waits – nvl(t1.total_waits,0)) “Delta_Waits”,
(t2.total_timeouts – nvl(t1.total_timeouts,0)) “Delta_Timeouts”,
(t2.time_waited – nvl(t1.time_waited,0)) “Delta_Time_Waited”
from  begin_system_event t1,
end_system_event t2
where t2.event = t1.event(+)
order by (t2.time_waited – nvl(t1.time_waited,0)) desc;

2.3.V$SESSION_EVENT
SQL> desc v$session_event
Name                Type
————————————————-
SID                 NUMBER
EVENT               VARCHAR2(64)
TOTAL_WAITS         NUMBER
TOTAL_TIMEOUTS      NUMBER
TIME_WAITED         NUMBER
AVERAGE_WAIT        NUMBER
MAX_WAIT            NUMBER  最大等待时间
TIME_WAITED_MICRO   NUMBER
EVENT_ID            NUMBER
WAIT_CLASS_ID       NUMBER
WAIT_CLASS#         NUMBER
WAIT_CLASS          VARCHAR2(64)
v$session_event视图包含当前连接到实例的所有会话的等待事件统计.它记录会话中每个等待事件的已等待时间和最大等待时间.
v$session_event对于查找某一个session的主要瓶颈非常有用,但是它对于确定根本原因不很有帮助,原因在于:
* v$session_event不跟踪产生瓶颈的SQL,例如你发现db file scattered read事件是你主要的瓶颈,但是不抓住执行全表扫描的SQL,一切还是徒劳;
* 还不能提供足够的证据来定位性能问题的根源.例如,所有的latch wait都会记入latch free事件,你并不清楚session所等待的究竟是什么latch.
MAX_WAIT纪录的是该session对某一个时间的最大等待时间,就像是等待事件的等待时间的高水位线,不过oracle并不记录达到这个高点的时间戳.但Oracle提供了一个内部过程dbms_system.kcfrms()可以将该列复位为0,然后可以监控该值是如何增加的;另外,它也会将v$filestat视图的maxiortm和maxiowtm列复位.

2.4.V$SESSION_WAIT
SQL> desc v$session_wait
Name                  Type
———————- —————————-
SID                   NUMBER
SEQ#                  NUMBER
EVENT                 VARCHAR2(64)
P1TEXT                VARCHAR2(64)
P1                    NUMBER
P1RAW                 RAW(8)
P2TEXT                VARCHAR2(64)
P2                    NUMBER
P2RAW                 RAW(8)
P3TEXT                VARCHAR2(64)
P3                    NUMBER
P3RAW                 RAW(8)
WAIT_CLASS_ID         NUMBER
WAIT_CLASS#           NUMBER
WAIT_CLASS            VARCHAR2(64)
WAIT_TIME             NUMBER
SECONDS_IN_WAIT       NUMBER
STATE                 VARCHAR2(19)
v$session_wait视图提供每个会话正在等待的事件或资源的详细信息.该视图在任何给定时间,只包含每个会话的一行活动的或不活动的信息.不同于其他视图,该视图实时显示会话级的等待信息.
wait_time列记录会话已经等待的时间量:负值表示未知等待时间;零值表示会话仍在等待;而正值则表示实际的等待时间,
当得知某个正在运行的应用有问题时查询v$session_wait最有用,由于这个视图是实时刷新的,你只需要重复查询这个视图,如果查询结果不停的变换,用户一直都在经历不同的wait event,说明应用正在运行,如果应用持续的等候同一个事件,那就意味着这个session处于idle状态或者正在等待某个资源,如lock,latch等.
v$session_wait提供的是实时的数据,10g开始,历史数据可以从v$session_wait_history获取.
STATE字段共有4个状态:
WAITED UNKNOWN TIME只表示TIMED_STATISTICS初始化参数设置为false,而oracle无法确定等待时间,这种情况下,wait_time列显示-2.
WAITED SHORT TIME表示以前的等待低于1厘秒.这种情况下,wait_time列显示-1.
WAITING表示会话当前正处于等待状态,wait_time列显示0,但可以从SECONDS_IN_WAIT列确定当前等待所花的时间,
WAITED KNOWN TIME表示oracle能够确定最近一次等待的持续时间,并且将时间记录在wait_time列.

2.5.Trace Event 10046—The Extended SQL Trace
前面说的,要跟踪某个session的运行状况,找到其瓶颈,最好的方式就是定期的获取wait event的数据,将这些数据写入trace文件是一个很好的选择.我们可以设置session级的参数SQL_TRACE=TRUE,也可以使用trace event 10046获取更多的信息,这是SQL trace的扩展.
Trace level:
* Level 0 Tracing is disabled. This is the same as setting SQL_TRACE = FALSE.
* Level 1 Standard SQL trace information (SQL_TRACE = TRUE). This is the default level.
* Level 4 SQL trace information plus bind variable values.
* Level 8 SQL trace information plus wait event information.
* Level 12 SQL trace information, wait event information, and bind variable values.
追踪用户会话时,追踪文件会记录在由参数USER_DUMP_DEST指定的目录中;追踪后台进程时,追踪文件会记录在由参数BACKGROUND_DUMP_DEST指定的目录中.

2.5.1. 启动trace event 10046
INSTANCE级:更改init.ora,添加EVENT参数后重启,不建议！
# This enables the trace event 10046 at level 8 for the instance.
# Restart the instance after this change is made to the init.ora file.
EVENT = “10046 trace name context forever, level 8″

推荐使用Session级的trace event 10046
2.5.2. 追踪自身会话
方法1
alter session set timed_statistics= true;
alter session set max_dump_file_size= unlimited;
— To enable the trace event 10046 in Oracle 7.3 onwards
alter session set events ‘10046 trace name context forever, level 8’;
— Run your SQL script. or program to trace wait event information
— To turn off the tracing:
alter session set events ‘10046 trace name context off’;

方法2
$sqlplus ‘/ as sysdba’
SQL>start $ORACLE_HOME/rdbms/admin/dbmssupp.sql
Package created.
Package body created.
SQL> grant execute on DBMS_SUPPORT to username;
Grant succeeded.
SQL> connect username/pwd
SQL> — To include Wait Event data with SQL trace (default option)
SQL>exec sys.dbms_support.start_trace;
PL/SQL procedure successfully completed.
SQL> — To include Bind variable values, Wait Event data with SQL trace
SQL>exec sys.dbms_support.start_trace(waits => TRUE, binds=> TRUE)
PL/SQL procedure successfully completed.
SQL> — Run your SQL script. or program to trace wait event information
SQL> — To turn off the tracing:
SQL>exec sys.dbms_support.stop_trace;
PL/SQL procedure successfully completed.

2.5.3. 追踪他人的会话
先调整参数
— Set TIME_STATISTICS to TRUE for SID 1234, Serial# 56789
execsys.dbms_system.set_bool_param_in_session( –
sid => 1234, –
serial# => 56789, –
parnam => ‘TIMED_STATISTICS’, –
bval => true);
— Set MAX_DUMP_FILE_SIZE to 2147483647
— for SID 1234, Serial# 56789
execsys.dbms_system.set_int_param_in_session( –
sid => 1234, –
serial# => 56789, –
parnam => ‘MAX_DUMP_FILE_SIZE’, –
intval => 2147483647);

** 方法1:Use the DBMS_SUPPORT package procedures:
— Enable ‘level 12’ trace in session 1234 with serial# 56789
exec dbms_support.start_trace_in_session( –
sid => 1234, –
serial# => 56789, –
waits => true, –
binds => true);
— Let the session execute SQL script. or
— program for some amount of time
— To turn off the tracing:
exec dbms_support.stop_trace_in_session( –
sid => 1234, –
serial# => 56789);

** 方法2:Use the DBMS_SYSTEM package procedure(oracle不建议此用法)
— Enable trace at level 8 for session 1234 with serial# 56789
exec dbms_system.set_ev( 1234, 56789, 10046, 8, ”);
— Let the session execute SQL script. or
— program for some amount of time
— To turn off the tracing:
exec dbms_system.set_ev( 1234, 56789, 10046, 0, ”);

** 方法3:Use the oradebug facility.
You need to know the session’s OS process ID (SPID) or Oracle process ID (PID). You can look them up in the V$PROCESS view. Assuming you know the name of the user you want to trace:
select s.username,
p.spid os_process_id,
p.pid oracle_process_id
from  v$session s, v$process p
where s.paddr = p.addr
and   s.username = upper(‘&user_name’);
Now use SQL*Plus to connect as sysdba and issue following commands:
alter system set timed_statistics = true;
oradebug setospid 12345;
— 12345 is the OS process id for the session
oradebug unlimit;
oradebug event 10046 trace name context forever, level 8;
— Let the session execute SQL script.
— or program for some amount of time
— To turn off the tracing:
oradebug event 10046 trace name context off;
10g you can use DBMS_MONITOR package procedures to enable tracing based on the SID, service name, module, or action. The action-based tracing empowers a DBA to trace a specific business function. There is a little catch to this: the procedure requires that the DBA know the module and action names.

** 方法4:Use the DBMS_MONITOR package to enable tracing for session 1234 and serial# 56789 as shown below:
这个方法和dbms_support很相像,在10g下,建议用dbms_monitor
exec dbms_monitor.session_trace_enable( –
session_id => 1234, –
serial_num => 56789, –
waits => true, –
binds => true);
— Let the session execute SQL script. or
— program for some amount of time
— To turn off the tracing:
exec dbms_monitor.session_trace_disable( –
session_id => 1234, –
serial_num => 56789);

** 方法5:Use the DBMS_MONITOR package for service, module, and action-based tracing:
— Enable Level 12 trace for known Service,
— Module and Action
exec dbms_monitor.serv_mod_act_trace_enable( –
service_name => ‘APPS1’, –
module_name => ‘GLEDGER’, –
action_name => ‘DEBIT_ENTRY’, –
waits => true, –
binds => true, –
instance_name => null);
— Let the session execute SQL script. or
— program for some amount of time
— To turn off the tracing:
exec dbms_monitor.serv_mod_act_trace_disable( –
service_name => ‘APPS1’, –
module_name => ‘GLEDGER’, –
action_name => ‘DEBIT_ENTRY’);
使用以上方法获取的trace文件在USER_DUMP_FILE目录下,可以使用以下方式来为trace文件添加前缀:
alter session set tracefile_identifier = ‘MyTrace’;
如果是使用oradebug来获取trace文件的,还可以通过以下方式知道trace文件名:
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 8
Statement processed.
SQL> oradebug tracefile_name
d:\oracle\admin\or92\udump\or92_ora_171.trc

3.Common Wait Events
3.1.buffer busy waits
当一个session要获取buffer cache中的数据块,而该数据块正在被其他session所使用时将产生此事件.
两种情况下会产生这一事件:
* 为了确保会话拥有与获得所有更改或无更改的数据块一致的映像,修改该数据块的会话会修改该数据块头的标记,以防止其他session冲突;
* 当会话必须等待其他会话将同一数据块读入buffer cache时.这一情况在10g之前会产生buffer busy waits事件,从10g开始由read by other session事件替代.
不要混淆buffer busy waits和buffer busy,buffer busy是由于会话通过ASM访问cached metadata所产生的.
视图v$waitstat为每一类缓冲区提供了有用的等待统计.遭遇buffer busy wait等待事件最常见的缓冲区类为:data blocks,segment header,undo blocks和undo header.
参数:
P1-数据块驻留的绝对文件号
P2-进程需要访问的实际块号
P3-10g之前是一个代表等待原因的数值,10g之后是wait class,可以从v$session_wait_class或者v$system_wait_class中查看具体的类
等待时间(Timeout): 100cs or 1 second.

3.2.control file parallel write
当会话在等待对所有控制文件的写入请求完成时产生此事件.需要写控制文件主要有以下几种情况:
* CKPT每3秒向控制文件写入redo logs的checkpoint位置,用于recovery;
* NOLOGGING或者UNRECOVERABLE的DML操作时,oracle会记录下不可恢复的SCN;
* RMAN将备份和恢复信息写入控制文件.
对于此事件没有造成阻塞的会话.会话被阻塞在OS以及其I/O子系统上等待以完成对所有控制文件的写入.对控制文件进行写入的会话将被保持在CF(control file)队列中,以使其他会话可以在该队列上等待.
如果这一等待时间过多,说明系统写控制文件的操作过多,或者写控制文件的性能过低.
参数:
P1-服务器进程正在写入的控制文件号码
P2-写入控制文件的块总数
P3- I/O请求的号码

3.3.db file parallel read
与其名称暗示的相反,此事件不与任何并行操作相关-既不是并行DML操作也不是并行查询.产生此事件的情况如下:
* 在数据库recovery时,从Datafile并行读取恢复所需数据块;
* 用户进程从一个或者多个数据文件读取多个非连续的单独数据块.
参数:
P1-读取的文件编号
P2-读取的块总数
P3-I/O的请求总数

3.4.db file parallel write
此事件不与任何并行DML操作相关.该事件属于DBWR进程.该进程是想数据文件写入脏数据块的唯一进程.阻塞者是操作系统I/O子系统.这也会对I/O子系统造成影响,因为写入操作可能会影响从同一磁盘进行读取操作的会话的读取时间.
DBWR将一组脏数据块整理成”写入批量”,它发布多个I/O请求以将脏数据写入文件,然后以此事件等待直到I/O请求都完成.如果使用的是异步IO,则DBWR不等待IO写入完成,在将free buffer放回LRU chain供用户使用以前,仅等待一定百分比的批处理的完成.它也可能发布多个写入请求.
参数:
P1-写入文件的号码
P2-写入的块总数
p3-以厘秒为单位显示等待I/O完成的超时值

3.5.db file scattered read
当会话提交一个读入多个数据块的IO请求时出现该事件.这些被读取的数据块离散的进入buffer cache.这些块不需要在cache中保持临近.该事件是在全表扫描或索引快速全局扫描时发生的典型事件.参数db_file_multiblock_read_count决定了读取数据块的最大数目.
数据文件的IO等待是正常的现象,这一事件本身并不代表数据库有问题.但是如果用于等待该事件的时间明显高于其他等待事件,那就需要进一步查找原因了.
参数:
P1-读取数据块的文件号码
P2-开始读取的起始块号
P3-读取的块总数

3.6.db file sequential read
当进程等待顺序读取的I/O完成时产生此事件.它是单块读取操作.当从index, rollback或undo segments,table access by rowid, rebuilding control files, dumping datafile headers或the datafile headers时提交该事件..
同样,这一事件本身并不代表数据库有问题.但是如果用于等待该事件的时间明显高于其他等待事件,那就需要进一步查找原因了.
参数:
P1-读取数据块的文件号码
P2-开始读取的起始块号
P3-读取的块总数.绝大多数情况下是1,但临时段可以大于1

3.7.db file single write
由DBWR提交.该事件主要在检查点期间(check point)更新数据文件头时产生.当数据库出现不规则数目的数据文件时可能会注意到该事件.
参数:
P1-写入的文件号码
P2-开始写入的起始块号
P3-写入的块数,通常是1

3.8.direct path read
当会话直接将数据读入PGA而不是SGA的buffer cache时会产生此事件.直接读取以同步I/O或异步I/O模式执行,这取决于硬件平台和DISK_ASYNCH_IO参数的不同.直接读取I/O通常用于访问驻留在磁盘上的临时段.这些操作包括排序、并行查询、hash joins等.
这个事件的等待次数和时间在同步和异步的情况下是不同的:
* 同步的情况下会话提交一个请求以后会等待IO结束,但这个等待时间并不计入该事件.在IO完成会话获取到自己所需要的数据以后会提交该事件.因此,应该是每有一个读取请求,就会有一个对应的等待时间,但是等待时间却是相当短,可以忽略不计.
*  异步的情况下,会话持续提交多个直接读取请求之后,继续处理已经缓存在PGA中的块.仅当因请求的块还没有读入缓冲区而不能继续处理时,会话才提交此事件.所以,读取请求数可能不等于等待数.由于这些异常,因此未必能在v$system_event和v$session_event视图中看到这一等待事件.
参数:
P1-读取的绝对文件号码
P2-读取的起始块号
P3-要读取的块数

3.9.direct path write
此事件就是direct path read等待事件的相对操作.Oracle将从会话的PGA直接写入数据文件.一个会话可以发布多个写入请求和连续的处理,操作系统处理I/O操作.
直接写入操作通常用于直接数据加载(APPEND提示或CTAS),或并行DML操作时写入到临时段.
当异步I/O时等待次数和等待时间可能会有所误导作用.
参数:
P1-要写入的绝对文件号码
P2-写入的起始块号
P3-要写入的块数

3.10.Enqueue
排队时oracle用来串行访问数据库资源的一种共享内存结构.进程必须获得资源的排队锁才能访问它.当获取排队的请求因其他会话正在以不兼容模式持有对资源的锁而失败时,进程将以此事件一直等待.进程在队列中等待轮到它获取所请求的排队时刻.
串行访问不同的资源有不同类型的排队,由两字符的排队名称唯一的标识.
* ST 空间管理事务的排队
* SQ 序列号的排队
* TX 事务的排队
从10g开始,每种排队都有单独的等待事件来表示.
看个例子,在9i和10g分别实验,两个Session同时对同一张表作dml操作时,9i显示有”enqueue”事件,类型为“TX”,而10g报告的等待事件则为“enq: TX – row lock contention”.
–以下为9i的输出
select sid,event,p1text,p1,p2text,p2,p3text,p3,wait_time,state
from v$session_wait
where event=’enqueue’;
SID   EVENT   P1TEXT   P1 P2TEXT P2 P3TEXT P3 WAIT_TIME  STATE
22  enqueuename|mode  1415053318 id1131073 id2597156 0  WAITING
select sid,
chr(bitand(p1, -16777216)/16777215)||chr(bitand(p1,16711680)/65535) “Name”,
(bitand(p1, 65535)) “Mode”
from  v$session_wait where event = ‘enqueue’;
SID Name   Mode
22  TX 6
–以下为10g的输出
SQL> select sid,event,p1text,p1,p2text,p2,p3text,p3,wait_time,state from v$session_wait where sid in (144);
SID   EVENT                               P1TEXT    P1         P2TEXT     P2   P3TEXT   P3  WAIT_TIME    STATE
144   enq: TX – row lock contention   name|mode 1415053318 usn<<16 | slot 655364 sequence 1619 0           WAITING
参数:
P1-等待进程请求的排队名称和模式,该信息以ASCII码的格式编码.下面的SQL显示如何找到等待进程请求的排队名称和模式:
SELECT sid,
chr(bitand(p1,-16777216)/16777215) ||
chr(bitand(p1, 16711680) / 65535) “Name”,
bitand(p1, 65535) “Mode”
FROM v$session_wait
WHERE event = &enqueue_name;
P2- 请求的锁的资源标识符ID1,同V$LOCK.ID1
P3- 请求的锁的资源标识符ID2,同V$LOCK.ID2
等待时间:
等待时间取决于排队的名称.但在大多数情况下oracle等待到3秒或直到排队资源变成可用.当等待事件超时,oracle将检查持有锁的会话是否依然存活,若是,则再等待一次.

3.11.free buffer waits
当会话在buffer cache中找不到空闲缓冲区以读入数据块或建立一个数据块的一致读取(CR)映像时产生此事件.这意味着可能是buffer cache过小,或者将脏块写入磁盘的速度过慢,一旦出现这个等待事件,用户进程就会向DBWR发送信号,要求其尽快将脏数据块写入磁盘.
参数:
P1-oracle读取块的文件号码
P2-文件中oracle读入缓冲区的块号
P3-缓冲存储器中LRU和LRUW列表的SET_ID#

3.12.latch free
进程想要获取latch而此时该latch正被其他进程所持有时产生此事件.类似于排队,oracle使用latch来保护数据结构.一次只能由一个进程在获得latch后修改或检查数据结构.与不同的排队是,请求latch的进程不需要在队列中等待.如果获取latch的请求失败,则进程仅仅等待一小段时间久可以再次请求锁存器.这一小段等待时间称为”自旋”(spin).如果在一次或多次自旋重复之后还没有获得latch,进程就休眠一小段时间然后再次尝试获取latch,接下来休眠更长一段时间直到获得latch.
最常见的latch有:cache buffer chains(高速缓存缓冲区链),library cache(库高速缓存)和share pool(共享池).
参数:
P1-进程等待的latch地址
P2-latch号码,同v$latchname.latch#
P3-尝试的次数
等待时间:
该事件的等待时间呈指数级增长.它不包括进程为自旋花费的时间.
在oracle10g中大多数锁存器都有它们自己的等待事件.

3.13.library cache pin
该等待事件与库高速缓存并发性有关.当会话尝试在库高速缓存中钉住对象以进行修改或检查时产生该事件.会话必须获取插针(pin)以确保此时对象没有被其他会话更新.当会话正在编译或分析PL/SQL过程和视图时,oracle提交该事件.
采用什么样的动作来减少这些等待主要取决于产生了什么样的阻塞.常见的问题情景为:在PL/SQL过程内重编译PL/SQL代码处使用dynamic sql的情况,以及dynamic sql调用取决于调用过程的内容时.如果存在范围较大的普遍性等待,则需要调整共享池.若存在阻塞情况,下面的SQL可用来显示在等待中持有或请求由P1给定对象的插针的会话.
Select P1 from v$session_wait where event=’library cache pin’;
select s.sid, kglpnmod “Mode”, kglpnreq “Req”
from  x$kglpn p, v$session s
where p.kglpnuse=s.saddr
kglpnhdl=’&P1RAW’ ;
x$kglpn——[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s
##主要用来处理library cache pin holder
sys@ORALOCAL(192.168.0.22)> desc x$kglpn
Name                                                  Null?    Type
—————————————————– ——– ————
ADDR                                                           RAW(4)
INDX                                                           NUMBER
INST_ID                                                        NUMBER
KGLPNADR                                                       RAW(4)
KGLPNUSE                                                       RAW(4)
KGLPNSES                                                       RAW(4)
KGLPNHDL                                                       RAW(4)
##关联v$session_wait中event为library cache pin的P1RAW,再关联v$session,可以查出sid和serial#
KGLPNLCK                                                       RAW(4)
KGLPNCNT                                                       NUMBER
KGLPNMOD                                                       NUMBER
##如果值为3,表示为library cache pin的holder;如果值为0,表示为waiter
KGLPNREQ                                                       NUMBER
##如果值为0,表示为library cache pin的holder;如果值为2,表示为waiter
KGLPNDMK                                                       NUMBER
KGLPNSPN                                                       NUMBER
参数:
P1-被检查或加载的对象地址
P2-加载锁的地址
P3-包含模式加上命名空间(模式指示将加载对象的哪个数据片;命名空间是显示在v$db_object_cache视图中的对象命名空间).
等待时间:
对于PMON进程是1秒;对于其他所有进程是3秒;

3.14.library cache lock
该事件也与库高速缓存并发性有关.当会话尝试在库高速缓存中修改或检查给定对象时产生该事件.会话必须获取对象句柄的库高速缓存锁,以阻止其他会话同时访问它,或维持一段较长时间的依赖性,或在库高速缓存中给对象定位.
参数:
P1-被检查或加载的对象地址
P2-加载锁的地址
P3-包含模式加上命名空间(模式指示将加载对象的哪个数据片;命名空间是显示在v$db_object_cache视图中的对象命名空间).
等待时间:
对于PMON进程是1秒;对于其他所有进程是3秒;

3.15.log buffer space
当会话必须等待日志缓冲区中的空间变成可用以写入新信息时产生此等待事件.该等待表示生成重做日志的速度比LGWR进程将其写入重做日志文件的速度要快.可能的原因:日志缓冲区过小,或者重做日志文件在磁盘上存在I/O争用.
参数:
该等待事件无等待参数.
等待时间:
通常是1秒,但如果会话必须等待一个日志文件转换完成时则是5秒.

3.16.log file parallel write
当会话等待LGWR进程将日志缓冲区中的重做信息写入到重做日志组的所有日志成员时产生此等待事件.该事件通常由LGWR进程提交.仅当使用异步I/O时LGWR进程并行写入到活动的日志文件成员.否则它顺序的写入到每个活动的日志文件成员.
该事件的等待通常表示重做日志所处的磁盘设备缓慢或存在争用.
参数:
P1-写入的日志文件号
P2-写入的OS块号
P3-I/O请求的号码
等待时间:
完成所有I/O所占用的实际时间.虽然日志文件写入是并行的,但只有等到最后一个I/O操作完成时整个写入才算完成.

3.17.log file sequential read
当进程等待从联机重做日志文件读入块时产生此等待事件.ARCH进程在读取重做日志文件时会遭遇此等待事件.
参数:
P1-重做日志组中重做日志文件的相对序列号
P2-开始读入的块号
P3-从P2值开始读入的OS块号
等待时间:
完成请求读取的I/O所占用的实际时间.

3.18.log file switch(archiving needed)
该事件表示在写入重做日志文件时ARCH进程跟不上LGWR进程.按归档日志模式操作数据库时,只有ARCH进程通过将重做日志文件复制到归档目录完成归档后,LGWR进程才可以改写或转换该重做日志文件.失败的归档可能会中止归档进程.这样的错误会在警告日志文件中报告出来.
参数:
该事件没有等待参数.
等待时间:
1秒

3.19.log file switch(checkpoint incomplete)
该等待事件表示进程在等待日志文件转换完成,但是因对于此日志文件的检查点进程未完成而使得日志文件转换变得不可能.当重做日志文件容量太小时会出现此事件.
参数:
无等待参数
等待时间:
1秒

3.20.log file switch completion
当进程在等待日志文件转换完成时产生此等待事件.
参数:
无等待参数
等待时间:
1秒

3.21.log file sync
当用户会话通过提交或回滚完成一个事务时都会触发LGWR将重做信息写入重做日志,会话才能继续处理.进程以此事件等待LGWR进程完成对重做日志文件的I/O.
如果会话继续在相同缓冲区(buffer#-P1)上等待,那么v$session_wait视图的SEQ#列应每秒递增.如果不是,那么本地会话存在一个等待事件超时问题.若SEQ#列在递增,则造成阻塞的进程是LGWR进程.,就需要查找堵塞LGWR进程的原因.
调优LGWR的关键是磁盘IO,例如,redo logs不应放在RAID5的磁盘阵列上;另外,将分散的事务合并成大事务也有助于减少该等待事件.
参数:
P1-需要同步的日志缓冲区中的缓冲区号
等待时间:
1秒

3.22.SQL*Net message from client
当会话等待来着客户端的消息时提交此等待事件.一般来说,这意味着会话处于空闲状态.在不与使用键盘的终端用户进行交互的批处理程序中,若在此事件上的等待时间过多,则表示在应用程序代码中或网络层存在低效率因素.但是,该事件上的过高等待时间并不降低数据库的性能,因为该事件清楚的指出可觉察到的数据库性能问题并不是真正的数据库问题.
参数:
P1-P1RAW列包含一个ASCII值,用来显示客户端连接使用的网络驱动器的类型,例如承传(bequeath),TCP.
P2-会话接收来自客户端的字节数(通常是1),即使接收到的数据包中包含多于1字节的内容.
等待时间:
自前一个会话发送至客户端的消息开始,客户端消息到达时所花费的实际时间.

3.23.SQL*Net message to client
当会话发送一个消息至客户端时提交此等待事件.客户端进程可能太忙而不能接受消息的传送,使得服务器端的会话等待;或网络层延迟而是消息传送花费更长的时间.
参数:
P1-P1RAW列包含一个ASCII值,用来显示客户端连接使用的网络驱动器的类型,例如承传(bequeath),TCP.
P2-会话传送至客户端的字节数(通常是1),即使接收到的数据包中包含多于1字节的内容.
等待时间:
完成消息传送至客户端所花费的实际时间.

4.Common wait Events in RAC
4.1.global cache cr request
当会话用远程实例高速缓存查找缓冲区的一致读取(CR)副本时,会在此事件上等待,直到缓冲区在本地实例中可用.通常持有者构造缓冲区的CR版本,然后通过高速互连传送给请求的实例.当CR缓冲区到达本地buffer cache时,等待结束.
从oracle10g release1起,此等待称为gc cr request等待.
下面的情况之一会发生,取决于远程实例持有缓冲区的模式和远程实例传送CR副本给本地实例的次数:
(1)缓冲区的CR副本发送至请求的实例,该缓冲区的”公平计数器”递增.随后global cache cr blocks received的统计递增.
(2)如果缓冲区不是被远程实例高速缓存(但受远程实例控制),那么它会授权给请求的实例.会话可从磁盘读取该块,global cache gets统计递增.
(3)如果该特殊缓冲区的CR副本数达到_fairness_threshold上限,则远程实例会将锁降级为null模式,并将重做内容转储到磁盘.会话可以进行磁盘I/O以从磁盘取得该块.
在情况(2)和(3)中,global cache cr request等待后面通常有db file sequential read或db file scattered read等待跟随.
关于参数_FAIRNESS_THRESHOLD的解释:
The behavior of the write-to-read transfer is determined by the _FAIRNESS_THRESHOLD parameter,which was introduced in Oracle 8.1.5 and defaults to 4. Prior to the introduction of this parameter,when Instance A held a block in exclusive mode and Instance B requested a read-only copy of that block, Instance A would downgrade its exclusive lock to a shared lock and send the block to Instance B,which would also set a shared lock on the block. However, if Instance A is performing frequent updates on the block, it will need to reacquire the block and set an exclusive lock again. If this process is repeated frequently, then Instance A will be continually interrupted, as it has to downgrade the exclusive lock to a shared lock and wait until Instance B has finished reading the block before it can convert the shared lock back into an exclusive lock.
The _FAIRNESS_THRESHOLD parameter modifies this behavior. When this parameter is set,Instance A will no longer downgrade the exclusive lock. Instead, it sends a null lock to Instance B,and then it can continue processing. However, if instance B requests the block _FAIRNESS_THRESHOLD times, by default 4, then Instance A will revert to the original behavior—it will downgrade the exclusive lock to a shared lock and ship the block to Instance B, which will also set a shared lock on the block.
如果遇到RAC中的global cache cr request等待事件过长的情况,可以考虑
alter system set “_fairness_threshold” = 3
or even lower, like 1, or 0 to disable downgrading the lock.
Record the data_requests and fairness_down_converts in v$cr_block_server before and after the change. (After the change, let the database run for a while and record the numbers.)
Note:181489.1 Tuning Inter-Instance Performance in RAC and OPS
参数:
P1-读取块的文件号
P2-从文件中读入缓冲区的块号
P3-缓冲区的锁元素号或类
等待时间:
在超时之间,等待时间为1秒.实际等待时间是一直等到请求的实例以要求的兼容模式高速缓存缓冲区

4.2.buffer busy global cache
当会话想修改在远程实例中高速缓存的一个缓冲区时,在此事件上等待.该事件类似于单个实例体系结构中的buffer busy wait事件.该等待发生在高速缓存层.换句话说,就是该会话在本地高速缓存中等待一个缓冲区,此时本地高速缓存因等待一个全局高速缓存请求的完成而忙碌.RAC层的等待记录在global cache busy等待事件中.
自oracle 10g release 1起,buffer busy global cache等待称为gc buffer busy等待.
参数:
P1-读取块的文件号
P2-从文件中读入缓冲区的块号
P3-表示原因的数值型代码
等待时间:
在超时之间,等待时间为1秒

4.3.buffer busy global cr
当一个以上的会话在同一实例中排队等待缓冲区的一个CR副本,而该期待的CR副本来自远程实例时,会话在此事件上等待.一旦来自远程实例的CR副本到达本地缓存,在缓冲区可用之前将块的SCN与快照的SCN进行对比.
自oracle 10g release 1起,buffer busy global cr等待称为gc cr block busy等待.
参数:
P1-读取块的文件号
P2-从文件中读入缓冲区的块号
P3-表示原因的数值型代码
等待时间:
在超时之间,等待时间为1秒

4.4.global cache busy
当会话想要修改以共享模式持有的缓冲区时,必须在此事件上等待,直到它以当前模式获得该缓冲区.该事件通常在实例想从远程实例获取缓冲区,而锁转换或获取进程正在进展中时发生.通常,段头,索引分支块和位图段以共享模式持有.在对段增加新块期间,缓冲区从共享当前模式转换为独占当前模式.
通常,global cache s to x等待紧跟在global cache busy等待后发生.这些等待过长表示远程实例上的互连缓慢或存在对该特定块重做的清除转储的延迟.
参数:
P1-读取块的文件号
P2-从文件中读入缓冲区的块号
P3-缓冲区的锁元素号或类
等待时间:
在超时之间,等待时间为1秒

4.5.global cache null to x
当会话要修改块时,必须以独占模式在其本地高速缓存中持有该块.当缓冲区不以任何其他模式在其本地高速缓存中时会话将等待该事件.若该缓冲区在远程实例的全局高速缓存中,则将其传送到本地高速缓存并授以那个缓冲区的独占锁.转换完成后统计global cache current blocks served递增.
若该缓冲区不以任何其他模式在远程高速缓存中持有,则全局高速缓存服务(Global Cache Service,GCS)对其授以适当模式的锁.一但锁授予该缓冲区,就从磁盘读入缓冲区.
参数:
P1-读取块的文件号
P2-从文件中读入缓冲区的块号
P3-缓冲区的锁元素号或类
等待时间:
1秒

4.6.global cache null to s
当会话要读缓冲区时,必须从null模式转换到共享模式.该缓冲区不是全局缓存则可从磁盘读取,或可通过互连从其他实例的缓存中获得.
参数:
P1-读取块的文件号
P2-从文件中读入缓冲区的块号
P3-缓冲区的锁元素号或类
等待时间:
1秒

4.7.global cache s to x
当会话以共享模式持有缓冲区而想要转换到以独占模式持有时,在此事件上等待.如果没有其他的实例以共享模式或独占模式持有该缓冲区,则global cache coverts计数器递增后将立即更新锁至独占模式.如果有任何其他实例以共享模式持有该缓冲区,则锁模式降级为null模式.
通常,在遍历索引树时以共享模式锁定缓冲区的情况下,索引更新会导致该等待,以独占模式持有缓冲区后将在分支块中更新叶块的地址.
参数:
P1-读取块的文件号
P2-从文件中读入缓冲区的块号
P3-缓冲区的锁元素号或类
等待时间:
1秒

4.8.global cache open x
当会话要修改当前块(通常是插入和批量加载),而该块没有以任何模式缓存在本地实例中时,该会话在此事件上等待.当前块必须来自远程实例或磁盘.若块没有存储在任何高速缓存中,且对于全局高速缓存是一个新块,则不涉及锁的转换.其进程被称为块”newing”,且在新块分配至自由表链或位映射段时发生.
参数:
P1-读取块的文件号
P2-从文件中读入缓冲区的块号
P3-缓冲区的锁元素号或类
等待时间:
1秒

4.9.global cache open s
当会话第一次将块读入缓冲存储器时等待此事件.该事件可能在开始或块正第一次读入本地缓冲存储器时发生.该块可以是从磁盘读入,也可以是从其他实例高速缓存中传送过来.
参数:
P1-读取块的文件号
P2-从文件中读入缓冲区的块号
P3-缓冲区的锁元素号或类
等待时间:
1秒

4.10.row cache lock
字典的高速缓存被称为行高速缓存,因为它以行级别保存信息,与之相对的缓冲存储器是以块级别保存信息.保护数据字典对象定义的锁称为row cache lock.通常,DDL语句请求row cache lock,而会话则等待此事件来锁定数据字典信息.
该等待不是RAC特有的等待.它在单个实例中可用,当然在RAC环境中有更大的影响,因为库高速缓存和行高速缓存是全局协调的.
参数:
P1-会话等待的行高速缓存锁的缓存ID号;可从v$rowcache获得.
P2-持有锁的模式
P3-请求锁的模式
下面的SQL可用来从v$rowcache中找到高速缓存号:
select cache#,type,parameter
from v$rowcache
where cache#=&P1;
等待时间:
在超时之间,时间为3秒;100次超时以后,回滚进程,会话在alert.log中写入”WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK”,然后进程中止.

5.追踪CPU和其他统计
CPU统计由系统级的v$sysstat视图和会话级的v$sesstat视图显示.v$statname视图提供一个统计列表,该列表可用来给v$sesstat解析统计的名称.简而言之,v$sysstat显示自实例启动起累加的实例范围的统计,而v$sesstat显示自会话启动时起累加的会话范围的统计.
在为数众多的统计中,我们只分析那些直接或间接属于CPU使用和I/O请求的统计.
您也注意到,在v$sesstat中对于某个特定的会话,CPU used when call started的值和CPU used by this session大体上是相同的,而在v$sysstat中看来可能稍微有些差别,特别是在长时间运行查询操作的数据库上.这是因为,v$sysstat中的CPU used when call started是当前执行调用开始之前使用的CPU时间的快照,而CPU used by this session则记录了已完成的会话的CPU时间增量.

查看内部参数的方法:
select
x.ksppinm  name,
y.ksppstvl  value,
y.ksppstdf  isdefault,
decode(bitand(y.ksppstvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,’FALSE’)  ismod,
decode(bitand(y.ksppstvf,2),2,’TRUE’,’FALSE’)  isadj
from
sys.x$ksppi x,  sys.x$ksppcv y
where
x.inst_id = userenv(‘Instance’) and
y.inst_id = userenv(‘Instance’) and
x.indx = y.indx and
x.ksppinm like ‘%_&par%’
order by
translate(x.ksppinm, ‘ _’, ‘ ‘);