等待事件监测性能瓶颈

1. 会话等待事件性能视图：v$system_event, v$session_event, v$session_wait

v$system_event: 查看整个系统级或者说最高级别的整体系统性能情况，这里面没有每个会话的相关信息，而是对每个事件总计了从系统启动以来在所有会话中发生过的情况。这个视图有5个字段：

  • event: 列出所有发生的事件的名称。
  • total_waits: 从数据库启动到现在这个等待事件总的等待次数。
  • total_timeouts：总的等待超时次数。
  • time_waited：总的等待时间，表示从数据库启动以来某个等待事件在所有会话中(包括已经结束的和正保持着连接状态的会话)总的等待时间之和。
  • average_wait: 平均等待时间，表示从数据库启动以来某个等待事件在所有会话中发生的平均等待时间。也就是time_waited/total_waits。

v$session_event: 每个等待事件在各自会话中的统计，字段只是多了一个会话ID字段。

v$session_wait：这个视图只包含了当前正处于连接状态的会话的等待事件，没有存储总的信息，而是提供当前已经发生或者正在发生的事件的可用信息。其中STATA字段是最有用的，它提供了对WAIT_TIME和SECONDS_IN_WAIT的解释，STATE有下面4种含义：

  • waiting：会话正在等待这个事件。
  • waited unknown time：由于timed_statistics被设置为false，而导致不能得到关于时间的信息。
  • waited short time：表示会话发生了等待，但是等待时间非常小，不超过一个时间单位(one clock tick)，所以没有记录。
  • waited known time：一旦会话等待了资源然后得到了，那么状态从waiting进入waited known time。

2. 等待事件主要分为2类：一类是空闲等待，一类是非空闲等待。

空闲等待意味着oracle正在等待某种动作的发生。有时oracle的进程在等待，实际上并不是因为忙而等待，而是因为没有事情所以在等待，大部分的空闲等待和性能无关，只有少量的空闲等待事件和性能有关。空闲类的等待事件典型有：client message(客户机消息), null event(null 事件), pipe get(管道取操作), SQL*Net message from client(来自客户端的消息), SQL*Net message to client(发送至客户端的消息), rdbms ipc message(数据库ipc信息), virtual circuit status(虚拟环路状态信息), smon timer(smon计时器), pmon timer(pmon计时器), dispatcher timer(调度器计时器)等。

非空闲事件通常是数据库发生了竞争时出现，当某种操作所需要的资源正在被其他操作占用，而这种独占的资源不能被后面的操作请求立即得到，操作请求被阻塞而发生了等待。非空闲等待事件主要有：

  • buffer busy waits(数据高速缓存忙等待)：表示在等待对数据高速缓存区的访问。通常出现在会话读取数据到buffer中或者修改buffer中数据时，例如DBWR正在写一些数据块到数据文件的同时，其他进程需要去读取相应的数据块，同时也可能表示在表上设置的free lists太少，不能支持大量并发的insert操作。
  • db file scattered read(数据文件离散读), 表示发生了和全表扫描相关的等待。通常意味着全表扫描过多，或者IO能力不足或IO竞争。
  • db file sequential read(数据文件顺序读), 表示发生了和索引扫描相关的等待。同样意味着IO竞争或IO需求太多。
  • db file parallel write(数据文件并行写),与DBWR进程相关的等待，一般都代表IO出现问题。通常与配置的多个DBWR进程或者DBWR的IOslaves个数有关。
  • db file single write(数据文件单次写), 表示在检查点发生时与文件头写操作相关的等待。通常与检查点同步数据文件头时文件号的紊乱有关。
  • enqueue(队列), 表示与内部队列机制相关的等待。例如对保护内部资源或者组件的锁的请求等，一种并发保护机制。 
  • free buffer inspected(空闲数据缓冲区探测), 表示将数据读入数据高速缓存区的时候等待进程找到足够大的内存空间。通常这类等待表示数据高速缓存区偏小。
  • free buffer waits(空闲缓冲区等待), 表示内存缓冲区缺少内存空间。通常与数据高速缓存区太小或者脏数据写入太慢有关。可以通过增大数据高速缓存区或者设置更多的DBWR来增加写脏数据的能力。
  • latch free(lath空闲等待), 表示在某个锁存器上发生了竞争。首先应该确保已经提供了足够多的latch数。
  • log file parallel write(日志文件并行写), 表示等待LGWR向操作系统请求IO开始直到完成IO。在触发LGWR写的情况下，如3s，1/3,1MB，DBWR写之前可能发生。这种事件发生通常表示日志文件发生了IO竞争或者文件所在的驱动器较慢。
  • log file sync(日志文件同步), 表示当服务器进程发出commit或rollback后，直到LGWR完成相关的日志写操作这段时间的等待。如果有多个服务进程同时发出这种命令，LGWR不能及时完成对日志的写操作，就可能出现这种等待。
  • log buffer space(日志缓冲区空间分配), 表示日志缓冲区出现了空间等待事件。意味着写日志缓冲区的时候得不到相应的内存空间，通常发生在日志缓冲区太小或者LGWR进程写太慢的时候(也可能由其他原因造成)。
  • log file single write(日志文件单次写), 表示写日志文件头块时出现了等待，一般都是发生在检查点发生时。
  • log file switch(archiving needed), 由于归档过慢造成日志无法切换而发生的等待。主要是归档速度赶不上日志切换的速度。
  • log file switch(checkpoint incomplete), 表示在日志切换时相应文件上的检查点还没有完成。一般意味着日志文件太小造成日志切换太快或者DBWR没有写完脏数据造成检查点太慢。
  • direct path read(直接路径读), 表示与直接IO读相关的等待。当直接读数据到PGA时，direct path read出现，这种类型的读请求典型的作为：排序IO(当排序不能在内存中完成的时候)，并行slave查询或者预先读请求等，通常与IO能力或IO竞争有关。
  • direct path write(直接路径写),同direct path read，只是操作为写。
  • library cache load lock(库高速缓存装载锁), 表示在将对象装载到库高速缓存时出现等待。通常代表发生了负荷很重的语句重载或者装载，可能由于sql语句没有共享或者共享池区域偏小造成。
  • library cache lock(库高速缓存锁),  表示与访问库高速缓存的多个并发进程相关的等待。通常表示不合理的共享池大小。
  • library cache pin(库高速缓存执行锁), 这个等待事件也和库高速缓存的并发性有关，当库高速缓存中的对象被修改或者被检测的时候发生。
  • timer in sksawat(归档过慢),
  • transaction(事务阻塞), 表示发生了一个阻塞回滚操作的等待。
  • undo segment extension(回滚段动态扩展)等。表示在等待回滚段的动态扩展。这表示可能事务量过大，同时也意味着可能回滚段的初始大小不是最优，MINEXTENTS设置得偏小，考虑减少事务，或者使用最小区数更大的回滚段。

3. 等待事件监测性能瓶颈步骤：

  • 查看这个系统从启动以来最主要的等待事件是什么：

select event "wait event", time_waited "time waited", time_waited/(select sum(time_waited) from v$system_event) "%time waited", total_waits "waits", total_waits/(select sum(total_waits) from v$system_event) "%waited" from v$system_event order by 3 desc;

  • 查看当前应用执行这段时间中最主要的等待事件，执行查询2次(业务繁忙期间)，并对这2次查询进行比较，得到应用执行这段期间最重要的等待事件。同样使用上面的sql。比如说找到log file parallel write最多。
  • 通过对v$session_event, v$session_wait查询来获得是由哪些会话引起的等待。

select sid, event "wait event", state "wait stat", wait_time "w'd so far(secs)", seconds_in_wait "time w'd(secs)" from v$session_wait where event like '%log file%' order by 5;

  • 下一步找到是哪些语句引起的等待，找寻系统中执行过的sql语句一般通过v$sqlarea，这个视图可以找到自系统启动以来放在共享池中所有sql语句。查询按照最消耗资源的顺序排列显示：select * from (select address "stmt addr", disk_reads "disk rds", buffer_gets "buff gets",sorts "sorts", executions "runs", loads "boby loads" from v$sqlarea where disk_reads>1000 order by disk_reads) where rownum<1000;
  • 上一步的查询其实没有多大用处，从第3步得到了那些产生等待事件的会话信息，通过这些会话信息和v$sqltext进行关联查询就可以得到相应的sql语句：

1)查询引起大量等待的会话执行的sql语句地址信息：

select cpu.sid "sid", cpu.username "user name", cpu.value "cpu(sec)", reads.value "IO read(k)", writes.value "IO write(k)", cpu.sql_address "sql_address"  from

(select a.sid, a.username, a.sql_address, b.name, c.value value from v$session a, v$statname b, v$sesstat c where a.sid=c.sid and b.statistic#=c.statistic# and b.name='CPU used by this session') cpu,

(select a.sid, a.username, a.sql_address, b.name, c.value value from v$session a, v$statname b, v$sesstat c where a.sid=c.sid and b.statistic#=c.statistic# and b.name='physical reads') reads,

(select a.sid, a.username, a.sql_address, b.name, c.value value from v$session a, v$statname b, v$sesstat c where a.sid=c.sid and b.statistic#=c.statistic# and b.name='physical writes') writes

where cpu.sid=reads.sid and reads.sid=writes.sid and cpu.sid='[SID]' and cpu.username is not null;

2)通过第1)查询得到的sql地址在v$sqltext里查询到引起事件等待的sql语句：

select sql_text "sql_statement text" from v$sqltext where address='[sql_address]' order by piece asc;

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/PerOpt/p/3736575.html