OWI性能诊断与调整实践指南（1~4）-CSDN博客

第1章、介绍Oracle Wait Interface

在v$sesstat 和v$sysstat 视图中有关于CPU used by this session 和CPU used when call started 的统计数据，但是推荐使用后者，因为后者比前者要稳定。

第2章、 Oracle Wait Interface 组件

1. 等待事件：当一个会话没有使用CPU 时，他可能正在等待一个资源、一个待完成的动作、或者仅仅是更多的工作，所有与这类等待相关的事件都被称为等待事件。

2. OWI 中有三个最为重要的性能视图：v$session_wait ，v$session_event ，v$system_event ，他们之间的关系是：v$session_wait < v$session_event < v$system_event 。

3. V$event_name 显示了每个等待事件的属性和他的类别，该视图不是动态的，不会随时间变化，其中event_id ，wait_class_id ，wait_class# ，wait_class 列属于10g 中添加的列，id 列式相应name 列的hash 值，即使版本升级了，但是只要相应的name 没有变化，则该列的值也不会变化。

4. V$system_event 中time_waited 列的单位是厘秒（1/100 秒），他是通过time_waited_micro 除以10000 得到的，time_waited_micro 的单位是微秒（1/1000000 秒）。

5. V$session_wait 中seq# 是与会话有关的事件的内部序列号，每次会话等待时，他会递增，wait_time 记录等待事件已等待的时间量（单位：s），负表示未知、0 表示任在等待、正表示实际等待时间，seconds_in_wait 在等待事件时用秒为单位的等待时间。State 、wait_time 、seconds_in_wait 这三列应该统一起来看。

6. V$session_wait 视图具有实时特性，该视图在任何时间，只包含每个会话的一行活动或不活动的信息，可能在每次查询中都会显示不同的结果，比较适合查看某个特定时刻会话的等待信息。

7. 追踪事件10046 能够提供大量更加丰富的信息，他是SQL 追踪的超集。他的级别分为：0 、关闭；1 、标准SQL 追踪；4 、标准SQL 追踪+绑定变量；8 、标准SQL 追踪+等待信息；12 、1+4+8 。影响该事件的参数有：timed_statistics 、max_dump_file_size 。

8. 启用10046 事件方法：

u 实例级：pfile 中添加event= ”10046 trace name context forever,level 12 ”;

u 会话级：

1) alter session set tracefile_identifier= ’xxx ’;（可选）

alter session set events ”10046 trace name context forever,level 12 ”;

alter session set events ”10046 trace name context off ”;

2) dbms_support 包，此包需要需要运行rdbms 中的dbmssupp.sql 创建。

Exec dbms_support.start_trace;

Exec dbms_support.stop_trace;

3) Exec dbms_support.start_trace_in_session

(sid=>123,serial#=>4567,waits=>true,binds=>true);

Exec dbms_support.stop_trace_in_session

(sid=>123,serial#=>4567);

4) Exec dbms_system.set_ev(123,4567,10046,12, ’xxx ’);

Exec dbms_system.set_ev(123,4567,10046,0, ’xxx ’);

5) SQL > Oradebug setospid 23423

SQL > Oradebug unlimit;

SQL > Oradebug event 10046 trace name context forever,level 8;

SQL > Oradebug event 10046 trace name context off;

SQL > Oradebug tracefile_name

6) Exec dbms_monitor.session_trace_enable

(session_id=>123,serial_num=>4567,waits=>true,binds=>true);

Exec dbms_monitor.session_trace_disable

(session_id=>123,serial_num=>4567);

7) Exec dbms_monitor.serv_mod_act_trace_enable

(service_name=> ’xxx ’,module_name=> ’xxx ’,action_name=> ’xxx ’,waits=>true,binds=>true,instance_name=>null);

Exec dbms_monitor.serv_mod_act_trace_disable

(service_name=> ’xxx ’,module_name=> ’xxx ’,action_name=> ’xxx ’);

9. Oracle 10g 中新的OWI 视图：

u v$session_wait_history ：每个sid 提供10 条等待信息，其中seq#=10 的最老，seq#=1 的最新。

u V$system_wait_class ：等待类在实例级别的总等待和等待时间。

u V$session_wait_class ：与v$system_wait_class 类似作用，只是粒度不同。

u V$event_histogram ：每个等待事件的直方图信息，但是其中的桶大小是固定了，不可改变，通过wait_time_milli 列可以查看桶大小。

第3章、常见的等待事件

1. Buffer busy wait 等待事件（属于concurrency 类）：

l 产生原因：当会话想要访问缓冲存储器中的数据块，而该数据块正在被其他会话使用时，将产生buffer busy wait 等待事件。

u 在10g 之前：其他会话可能正在从数据文件中读入该块，或者正在对数据缓存中的该块进行修改。

u 在10g 之后：其他会话从数据文件中读入该块作为read by other session 事件，此事件只是单纯的等待其他会话对数据块的修改。

l 10g 中还有一个buffer busy 等待事件，当会话在使用ASM 的数据库中访问高速缓存的元数据时提交buffer busy 事件（属于other 类）。

l 该等待的参数中：P1 表示绝对文件号；P2 表示数据所在的块号；P3 在10g 之前表示等待原因码，10g 之后表示v$waitclass 中的类。

l 该等待的时间为100 厘秒（1秒）。

2. Control file parallel write 等待事件：

l 产生原因：当前会话等待对所有控制文件的写入请求完成时产生此事件。CKPT 每3秒触发一次，当会话执行nolog 或unrecoverable 选项的DML 时，Oracle 需要在控制文件中记录不可恢复的scn ，RMAN 会话也会在控制文件中记录信息，这样就会遇到等待控制文件写入的等待事件。

l 参数：P1 表示服务器正在写入的控制文件号码；P2 写入的总块数；P3 表示I/O 请求的号码。

3. Db file parallel read 等待事件：

l 产生原因：作为恢复的一部分而需要更改的数据库块从数据文件中并行读取时产生该事件，当一个进程从一个数据文件中读取多个非连续的单独数据块时也产生该事件。该事件并不与任何并行操作（并行DML 、DQL ）相关。

l 参数：P1 、读取文件号；P2 、读取块总数；P3 、I/O 请求数（多块读取中=P2 ）。

4. Db file parallel write 等待事件：

l 产生原因：不与任何并行操作相关，属于DBWR 进程。当DBWR 将一组脏数据块编译成批。他发布多个I/O 请求将“写入批量”写入到数据文件，然后以此事件等待直到I/O 请求都完成。

l 参数：P1 、文件号；P2 、块数；P3 、9.2 之前=P2, 在9.2 以后表示等待I/O 完成的超时时间（以厘秒为单位）。

5. Db file scattered read 等待事件：

l 产生原因：会话发布一个读入多个数据块的I/O 请求时，提交此事件。多发生在全表扫描或索引快速扫描。

l 参数：P1 、文件号；P2 、读取的起始块号；P3 、读取的块数。

6. Db file sequential read 等待事件：

l 产生原因：当进程等待顺序完成的I/O 完成时产生此等待。实际是是单块的读取，当从索引、回滚或撤销段、rowid 访问表、重建控制文件、转储数据文件时提交该等待事件。

l 参数：P1 、文件号；P2 、读取的起始块号；P3 、读取的块数，大多数情况 =1，在临时段中可能 >1 。

7. Db file single write 等待事件：

l 产生原因：该事件有DBWR 提交，主要是在检查点期间Oracle 更新数据文件标题时产生。

l 参数：P1 、文件号；P2 、起始块号；P3 、写入的块数，通常 =1 。

8. Direct path read 等待事件：

l 产生原因：Oracle 直接将数据块读入PGA 而不经过SGA 时产生的事件。通常是用于访问磁盘上的临时段，包括排序、并行查询、散列连接等。

l 参数：P1 、绝对文件号；P2 、起始块号；P3 、读取的块数。

9. Direct path write 等待事件：

l 产生原因：不经过SGA ，直接将数据从会话的PGA 写回数据文件时产生。通常出现于直接数据加载（APPEND 提示、CTAS ）或并行DML 中临时段的写入。

l 参数：P1 、绝对文件号；P2 、起始块号；P3 、写入的块数。

10. Enqueue 等待事件：

l 产生原因：排队是指用来串行访问数据库资源的一种内存结构。当排队请求因其他会话以不兼容模式持有对象锁而失败时，进程以此事件一直等待。

l 参数：P1 、请求的排队名称和模式，ASCII 编码（ST ：空间管理事务的排队；SQ ：序列号排队；TX ：一个事务的排队）；P2 、请求锁的资源标识符ID1 = v$lock.id1 ；P3 、请求锁的资源标识符ID2 = v$lock.id2 。

l P1 列的查看：col name for a10

Select sid,chr(bitand(p1,-16777216)/16777215)||

chr(bitand(p1,16711680)/65535) “Name ”,bitand(p1,65535) “Mode ” from v$session_wait where event= ’enqueue ’;

11. Free buffer waits 等待事件：

l 产生原因：会话在数据缓存中找不到空闲空间来读入数据块或建立数据块的CR （一致读）映象时，进程会通知DBWR 写脏数据，并且以此事件等待。通常表明Buffer 太小或者脏数据太多。

l 参数：P1 、文件号；P2 、块号；P3 、10g 之前不可用，之后显示LRU 和LRUW 列表的set_id# 。

l 等待时间：Oracle 用最多一秒的时间等待，然后再次尝试。

12. Latch free 等待事件：

l 产生原因：进程想要获取锁存器而此锁存器正在被其他进程持有时触发此事件。一次只能一个进程获得锁存器，如果获取失败，不用队列中等待，而是进行spin 。

l 常见的锁存器：cache buffer chains 、library cache 、shared pool 。

l 参数：P1 、锁存器地址；P2 、锁存器号=v$latchname.latch# ；P3 、尝试次数。

13. Library cache pin 等待事件：

l 产生原因：与库高速缓存的并发性有关，当会话尝试在库高速缓存中钉住对象或检查对象时产生该事件。当会话正在编译或分析PL/SQL 过程或视图时，Oracle 提交该事件。

l 参数：P1 、被检查或加载的对象的地址；P2 、加载锁的地址；P3 、包含模式加上命名空间。

l 等待时间：PMON 为1s ，其他进程为3s 。

14. Library cache lock 等待事件：

l 产生原因：与库高速缓存的并发性有关，当会话尝试修改或检查给定对象时，必须获取对象句柄的库高速缓存锁，阻止其他会话的访问，即有可能产生此事件。

l 参数：P1 、被检查或加载的对象的地址；P2 、加载锁的地址；P3 、包含模式加上命名空间。

l 等待时间：PMON 为1s，其他进程为3s。

15. Log buffer space 等待事件：

l 产生原因：当会话必须要等待log buffer 中的空间变成可用时，就产生此事件，这表明redo 生成的速度大于LGWR 写redo 的速度，要么是log buffer 太小，要么是online log 所在的磁盘上存在I/O 争用。

l 参数：无参数。

l 等待时间：通常是1s ，如果必须等待一个日志文件转换完成则是5s 。

16. Log file parallel write 等待事件：

l 产生原因：当会话等待LGWR 将重做信息写入到日志成员时产生此等待，有LGWR 提交此等待。仅当使用异步I/O 时并行写入日志，否则顺序写入。此等待事件通常表明online log 磁盘设备缓慢或存在争用。

l 参数：P1 、日志文件号；P2 、OS 块号；P3 、I/O 请求的号码。

17. Log file sequential read 等待事件：

l 产生原因：当进程等待从联机日志读入块时产生此事件，ARCH 进程在读取日志文件时会遭遇此等待。

l 参数：P1 、重做日志的相对序列号；P2 、开始读入的块号；P3 、从P2 开始读入的OS 块号。

18. Log file switch （archive needed ）等待事件：

l 产生原因：在写入重做日志文件时ARCH 进程跟不上LGWR 进程时产生此等待。

l 参数：没有参数

l 等待时间：1s 。

19. Log file switch （checkpoint incomplete ）等待事件：

l 产生原因：在日志转换的过程中，由于检查点未完成而使得日志文件的转换变得不可能，则会产生此事件。

l 参数：没有参数

l 等待时间：1s 。

20. Log file switch completion 等待事件：

l 产生原因：进程在等待日志文件转换完成的过程中，有可能产生此事件。

l 参数：没有参数

l 等待时间：1s 。

21. Log file sync 等待事件：

l 产生原因：当用户会话结束一个事务时，LGWR 必须将redo buffer 信息写入日志文件中，才能继续。进程以此等待事件等待LGWR 进程对日志文件的I/O 。

l 参数：P1 、需要同步的日志缓冲区的缓冲区号；P2 、P3 未使用。

l 等待时间：1s 。

22. SQL*NET message from client 等待事件：

l 产生原因：会话等待一个来自客户端的消息时的等待事件。一般意味着会话处于空闲状态，在批处理中此等待太多则有可能是网络层面的问题。

l 参数：P1 、（ASCII 码）客户端使用的网络驱动器类型，如：bequeath 、TCP ；P2 、会话接受来自客户端的字节数（通常是1，即使数据包中的字节大于1）；P3 、未使用。

23. SQL*NET message to client 等待事件：

l 产生原因：会话发送消息至客户端，而客户端可能太忙或者网络的延迟等而不能接收消息的传送，则产生了此事件。

RAC 环境中常见的等待事件：

1. Global cache cr request （gc cr request ）等待事件：

l 产生原因：会话等待远程实例上的高速缓冲区的一致读副本时，产生此等待。

l 参数：P1 、文件号；P2 、块号；P3 、块类别。

l 常见块类：0 、系统回滚段；1 、数据块；2 、排序块；3 、延迟回滚段块；4 、段标题块；5 、延迟回滚段标题块；6 、空闲列表块；7 、区映射块；8 、位映射空间管理块；9 、空间管理索引块；10 未使用。

2. Buffer busy global cache （gc buffer busy ）等待事件：

l 产生原因：类似于单实例中的buffer busy wait ，想修改远程buffer cache 中的块时产生的事件。

l 参数：P1 、文件号；P2 、块号；P3 、表示原因的数值型代码。

3. Buffer busy global cr （gc cr block busy ）等待事件：

l 产生原因：当一个以上的会话在同一个实例中排队等待远程实例中的一个CR 副本时，就会在此事件上等待。

l 参数：P1 、文件号；P2 、块号；P3 、表示原因的数值型代码。

4. Global cache busy 等待事件：

l 产生原因：会话想要修改以共享模式持有的块时，必须在此事件上等待。通常global cache s to x 紧跟在此事件之后发生，如果这些等待过长，则表示节点之间的网络互联太慢。

l 参数：P1 、文件号；P2 、块号；P3 、块类别。

5. Global cache null to x 等待事件：

l 产生原因：当会话要修改块时，必须以独占模式在本地高速缓存中持有该块。

l 参数：P1 、文件号；P2 、块号；P3 、块类别。

l 等待事件：1s 。

6. Global cache null to s 等待事件：

l 产生原因：当会话要读取块时，必须从空模式转换到共享模式。

l 参数：P1 、文件号；P2 、块号；P3 、块类别。

l 等待事件：1s 。

7. Global cache s to x 等待事件：

l 产生原因：会话以共享模式持有缓冲区而想要转换到独占模式时。

l 参数：P1 、文件号；P2 、块号；P3 、块类别。

l 等待事件：1s 。

8. Global cache open x 等待事件：

l 产生原因：该块没有以任何模式缓存本地实例，是一个新块时，通常在此块进行插入或批量加载。

l 参数：P1 、文件号；P2 、块号；P3 、块类别。

l 等待事件：1s 。

9. Global cache open s 等待事件：

l 产生原因：第一次将块读入本地磁盘。

l 参数：P1 、文件号；P2 、块号；P3 、块类别。

l 等待事件：1s 。

10. Row cache lock 等待事件：

l 产生原因：字典的高速缓存是行缓存，以行级别保存信息。DDL 操作请求row cache lock ，用此事件来锁定数据字典信息。

l 参数：P1 、行高速缓存的ID 号，可从v$rowcache 获得；P2 、持有锁的模式；P3 、请求锁的模式。

OWI 不统计CPU 信息，但是关于CPU 的信息可以由v$sysstat 和v$sesstat 视图显示出来，v$statname 显示了相关统计的列表。

第4章、 OWI 监控和收集方法

l 将database logoff trigger 用作数据收集器：

Create or replace trigger xxx before logoff on database …;

在其中填充两张历史数据存储表（必须预先创建），保存v$session_event 和v$sesstat 中相关的会话信息，这样就能在每个会话注销时，自动收集该会话相关的信息，留作以后性能诊断的参考数据。

l 利用PL/SQL 过程进行性能数据采样：

1) 数据源：v$session_wait ，该视图实时性比较高，非常适合定时数据的收集。

2) 采样频率：可以适量考虑（例如：1分钟）。

3) 存储库：需要保存等待事件存储库和SQL 语句存储库。

4) 监控的事件：可以先监控部分常见等待事件，然后再后续添加其他等待事件。

l 对于不同的等待事件，要同时保存等待事件涉及到的相关对象信息和SQL 语句，其中可能涉及到的视图有：v$session_wait 、v$session 、v$sql 、dba_extents 、dba_segments 、v$sql_text 、dba_objects 、v$latchname 、v$datafile 、v$tempfile(file# 的查看需要加上v$parameter 中的db_files) 、v$lock 等。