AWR--Top 5 Timed Foreground Events-CSDN博客

基于Wait Interface的调优是目前的主流！每个指标都重要！

Waits : 该等待事件发生的次数，对于DB CPU此项不可用

Times : 该等待事件消耗的总计时间，单位为秒，对于DB CPU 而言是前台进程所消耗CPU时间片的总和，但不包括Wait on CPU QUEUE

Avg Wait(ms) : 该等待事件平均等待的时间，实际就是 Times/Waits，单位ms，对于DB CPU此项不可用

db file scattered read文件散列读取等待即多块读等待事件是当SESSION等待multi-block I/O时发生的，通常是由于full table scans或index fast full scans引起，Avg wait>20ms就要注意了，db file scattered read 异常大，可能是由于全表扫描引起

该事件通常与全表扫描或者index fast full scan有关。因为全表扫描是被放入内存中进行的，通常情况下基于性能的考虑，有时候也可能是分配不到足够长的连续内存空间，所以会将数据块分散(scattered)读入Buffer Cache中。该等待过大可能是缺少索引或者没有合适的索引(可以调整optimizer_index_cost_adj) 。这种情况也可能是正常的，因为执行全表扫描可能比索引扫描效率更高。当系统存在这些等待时，需要通过检查来确定全表扫描是否必需的来调整。因为全表扫描被置于LRU(Least Recently Used，最近最少适用)列表的冷端(cold end)，对于频繁访问的较小的数据表，可以选择把他们Cache 到内存中，以避免反复读取。当这个等待事件比较显著时，可以结合v$session_longops 动态性能视图来进行诊断，该视图中记录了长时间(运行时间超过6 秒的)运行的事物

关于参数OPTIMIZER_INDEX_COST_ADJ＝n：该参数是一个百分比值，缺省值为100，可以理解为FULL SCAN COST/INDEX SCAN COST。当n%* INDEX SCAN COST<full scan="" cost时，oracle会选择使用索引。在具体设置的时候，我们可以根据具体的语句来调整该值。如果我们希望某个statement使用索引，而实际它确走全表扫描，可以对比这两种情况的执行计划不同的COST，从而设置一个更合适的值。

关于INDEX FULL SCAN和INDEX FAST FULL SCAN的区别在于，前者在对索引进行扫描的时候会考虑大索引的结构，而且会按照索引的排序，而后者则不会，INDEX FAST FULL SCAN不会去扫描根块和分支块，对索引像访问堆表一样访问，所以这两个扫描方式用在不同的场合 ,如果存在ORDER BY这样的排序，INDEX FULL SCAN是合适的，如果不需要排序，那INDEX FAST FULL SCAN效率是更高的。

DB file sequential read单块读等待意味着发生顺序I/O读等待,如果这个等待严重，则应该对大表进行分区以减少I/O量，或者优化执行计划（通过使用存储大纲或执行数据分析）以避免单块读操作引起的sequential read等待，通常由于INDEX FULL SCAN/UNIQUE SCAN 、INDEX RANGE SCAN、行迁移或行链接引起，Avg wait>20ms就要注意了。

这里的sequential指的是将数据块读入到相连的内存空间中(contiguous memory space)，而不是指所读取的数据块是连续的。

? 最为常见的是执行计划中包含了INDEX FULL SCAN/UNIQUE SCAN,此时出现”db file sequential read”等待是预料之中的

? 当执行计划包含了INDEX RANGE SCAN-(“TABLE ACCESS BY INDEX ROWID”/”DELETE”/”UPDATE”)，服务进程将按照”访问索引->找到rowid->访问rowid指定的表数据块并执行必要的操作”顺序访问index和table，每次物理读取都会进入”db file sequential read”等待，且每次读取的都是一个数据块；这种情况下clustering_factor将发挥其作用（ALL_INDEXES的CLUSTERING_FACTOR*值来判断数据的离散程度）

? Extent boundary,假设一个Extent区间中有33个数据块，而一次”db file scattered read”多块读所读取的块数为8，那么在读取这个区间时经过4次多块读取后，还剩下一个数据块，但是请记住多块读scattered read是不能跨越一个区间的(span an extent)，此时就会单块读取并出现”db file sequential read”。

? 假设某个区间内有8个数据块，它们可以是块a,b,c,d,e,f,g,h，恰好当前系统中除了d块外的其他数据块都已经被缓存在buffer cache中了，而这时候恰好要访问这个区间中的数据，那么此时就会单块读取d这个数据块，并出现”db file sequential read”等待。注意这种情况不仅于表，也可能发生在索引上。 ?

chained/migrated rows行迁移行链接，chained/migrated rows会造成服务进程在fetch一行记录时需要额外地单块读取，从而出现”db file sequential read”。这种现象需要我们特别去关注，因为大量的链式/迁移行将导致如FULL SCAN等操作极度恶化(以往的经验是一张本来全表扫描只需要30分钟的表，在出现大量链式行后，全表扫描需要数个小时)，同时也会对其他操作造成不那么明显的性能影响。可以通过DBA_TBALES视图中的CHAIN_CNT来了解表上的链式/迁移行情况

? 创建Index entry，显然当对表上执行INSERT操作插入数据时，虽然在执行计划中你看不到过多的细节，但实际上我们需要利用索引来快速验证表上的某些约束是否合理，还需要在索引的叶子块中插入相关的记录，此时也可能出现”db file sequential read”等待事件

? 针对表上的UPDATE/DELETE，不同于之前提到的”INDEX RANGE SCAN-UPDATE/DELETE”，如果我们使用rowid去更新或删除数据时，服务进程会先访问rowid指向的表块(注意是先访问table block)上的行数据，之后会根据该行上的具体数据去访问索引叶子块(注意Oracle并不知道这些leaf block在哪里，所以这里同样要如range-scan/unique-scan那样去访问index branch block)，这些访问都将会是单块读取，并会出现’db file sequential read’，完成必要的读取后才会执行更新或删除的实际操作

log file sync：触发LGWR把log buffer写入redo log file的条件有5个（log_buffer中的内容满1/3 、间隔3秒钟、log_buffer中的数据到达1M、commit、DBWR 需要写入的数据的SCN大于LGWR记录的SCN导致DBWR 触发LGWR写入）只有commit称为同步写入，其他的都是后台写入，log file sync只和同步写入commit有关（参数_wait_for_sync的解释wait for sync on commit MUST BE ALWAYS TRUE），log file sync是用户commit/rollback触发，在AWR中属于Foreground Wait Events栏，导致'log file sync' 等待事件的主要原因有：

1. 磁盘IO 慢导致LGWR进程将redo buffer中的信息写入到redo log file速度慢。

2. user session commit过于频繁。

3. 本地或者远程服务器CPU资源不足，导致LMS和/或者LGWR不能及时得到CPU调度，不能正常工作。

4. RAC私有网络性能差，导致LMS同步commit SCN慢。

5.user session到DB之间的网络慢（201503宇达案例，DB的网络里面一台机器中毒导致不断发包致使交换机很慢，虽然客户端ping DB不慢，但是客户端session发送和接送数据比ping的字节大导致慢。）

6.log buffer太大引起的，log buffer过大，会使lgwr懒惰，因为日志写的触发条件有一个是1/3的log buffer满。当设置过大的log buffer，也会让一次性写入过大的redo record到log file中，写得过多导致log file sync等待。有一个隐含参数可以控制redo 写的阀值:_log_io_size。

所以没有必要将log buffer设置过大，只要没有出现log buffer space等待事件，log buffer大小就足够，而且通常默认情况下log buffer是足够的。

通常Log file sync结合Load Profile中的Redo size和Instance Activity Stats中的user commits来一起看比较好

1. user session执行提交（commit）

2. user session会通知LGWR进程将redo buffer中的信息写入到redo log file

3. LGWR进程将redo buffer中的信息写入redo log file（RAC的话有如下三个小步骤）

3.1. 同时LGWR 将COMMIT SCN同步/传播给远程的数据库实例的LMS 进程。

3.2. 远程数据库实例的LMS将commit SCN同步到本地SCN，然后通知commit实例的LMS，表示SCN 同步已经完成。

3.3 当commit 实例的LMS接收到所有远程数据库实例的LMS的通知后，commit 实例的LMS再通知本地的LGWR 所有节点SCN同步已经完成以便LGWR进行IO写入操作。

4. LGWR 在完成了IO写入操作后通知user session commit 成功

5. user session收到LGWR写完的通知后，向用户端发送提交完成

从步1开始，进程开始等待log file sync，到步5结束。

步3、步4，LGWR等待log file parallel write。

Log File Parallel Write：由lgwr进程引起的，不是user commit/rollback引起的，在AWR中属于Background Wait Events栏，

log file sync比log file parallel write大很大，有两个原因，一是CPU紧张，二是同时提交的进程多。

试想一下，如果10个进程同时提交，每个进程等0.1秒，加起来就一共等了1秒。而log file parallel write只会有一个进程等待，就是LGWR。

log buffer space、log file switch completion

log buffer space是日志缓冲区写的速度快于LGWR写REDO log FILE的速度导致（服务器进程生成重做记录的速度快过LGWR写出重做记录的速度,因而发生等待）.日志I/O缓慢是log buffer space等待的主要原因之一.还有一点,如果日志缓存区过小,也容易出现此等待事件.将日志缓存设置的大一些,对于缓解此事件的等待会有帮助.但是,过大的日志缓存,又会降低LGWR刷新缓存的频率,这可能会使提交时必须刷新的缓存数量增多.从而造成log file sync 等待

log file switch completion是当一个日志文件满了,oracle要打开另一个日志文件,写完上一日志文件,准备好下一日志文件,这之间的等待就是此等待事件了,简单点说,就是为了完成日志文件切换而发生的等待. 若重做日志文件过小，则log file switch completion等待会增加

log buffer space等待和log file switch completion等待在许多情况下是同时出现的

Direct path read：

传统读取数据的方式是服务器进程通过读取磁盘，然后把数据加载到共享内存中，这样后面的进程就可以通过共享内存访问这些数据，不用再读取磁盘。direct path read读取数据块方式，是指服务器进程直接读取数据文件加载到服务器进程的PGA内中当中，不进入buffer cache中。

Oracle通过隐含参数 _small_table_threshold来界定大表小表的临界，Oracle认为对于大表执行直接路径读取的意义比较大，对于小表通过将其缓存可能受益更大。_small_table_threshold的单位为block。默认为db cache size的2%，只要表满足大于等于_small_table_threshold定义的大小就会采取直接路径读取

等待事件direct path read每秒读取多少M可以参考IOStat by Function summary或IOStat by Function/Filetype summary中的Function Name 为Direct Reads项

等待事件direct path read要考虑是普通堆表、lob、temporary tablespace中的哪个引起，就需要查看Instance Activity Stats中physical reads direct、physical reads direct (lob)、physical reads direct temporary tablespace谁的每秒读取最大。Segments by Direct Physical Reads中可以看到是哪个表被Direct Reads了。Instance Activity Stats中table scans (long tables)的per Second不是每秒读取块数，而是每秒读取次数（和其他指标不一样）。table scans (long tables)表示大表扫描次数，table scans (direct read)表示大表direct read扫描次数，一般table scans (direct read)<= table scans (long tables),如果前者1000，后者1000零几说明基本上大表扫描都是direct read

如果Segments by Direct Physical Reads中可只有一个表被Direct Reads了，结合Instance Activity Stats中physical reads direct、table scans (long tables)的per second值可以算出表的实际高水位多少M了，计算方法为(physical reads direct*8/1024)/ table scans (long tables)

两种方法来启用、禁用Direct path read特性。event 10949或者设置隐含参数_serial_direct_read(默认是false)

SQL> ALTER session SET EVENTS '10949 TRACE NAME CONTEXT FOREVER';

SQL> ALTER session SET EVENTS '10949 TRACE NAME CONTEXT off';

SQL> alter session set "_serial_direct_read"=true;

SQL> alter session set "_serial_direct_read"=false;

关闭Direct path read可以降低物理读，但是会提升逻辑读引起CPU升高

Buffer Busy Waits：

该事件是在一个SESSION需要访问BUFFER CACHE中的一个数据库块而又不能访问时发生的（指的是多个session对于同一个block的写入竞争）

buffer busy waits就发生在pin数据块的过程里，读读，读写都不会有冲突。可是写写的话，就会出现buffer busy waits。buffer busy waits事件表明修改的太频繁了，而不是读的太频繁了

update导致buffer busy waits原因：undo不足或热点块造成

insert导致buffer busy waits原因：freelist 争用造成，可以将表空间更改为ASSM 管理或者加大freelist 。

解决办法：

1、发现hot block，改变pctused 和pctfree，使得一个block 中可以容纳更多得数据

2、增加freelist

3、增加一定数量的回滚段

当进程需要存取SGA中的buffer的时候，它会依次执行如下步骤的操作：
1.获得cache buffers chains latch，遍历那条buffer chain直到找到需要的buffer header
2.根据需要进行的操作类型(读或写)，它需要在buffer header上获得一个共享或独占模式的buffer pin或者buffer lock
3.若进程获得buffer header pin，它会释放获得的cache buffers chains latch，然后执行对buffer block的操作
4.若进程无法获得buffer header pin，它就会在buffer busy waits事件上等待
进程之所以无法获得buffer header pin，是因为为了保证数据的一致性，同一时刻一个block只能被一个进程pin住进行存取，因此当一个进程需要存取buffer cache中一个被其他进程使用的block的时候，这个进程就会产生对该block的buffer busy waits事件。

read by other session：

10G以前，等待事件read by other session划入到了buffer busy waits里,read by other session的意思是，多个session并发请求相同的数据块，但因该数据块不在buffer_cache中而必须从磁盘读取，处理这种情况，oracle会只让其中一个sesion进行磁盘读取，此时其它session等待块从磁盘上读取进buffer_cache而抛出read by other session等待事件。10G以后，read by other session被从buffer busy waits单独了出来，变的单纯。

你读的块，别人也正在从磁盘读。这是Read By Other Session，不是Buffer BUsy Waits事件了。导致原因可能是大量的逻辑/物理读 ;或者过小的buffer cache引起

latch free：

其实是未获得latch，而进入latch sleep，如果latch资源被争用，通常都会表现为CPU资源使用过高。latch只作用于内存中、只能被当前实例访问、是瞬间的占用和释放、非入队的、不存在死锁。

产生原因：SQL语句没有绑定变量、热块较多

Latch的种类

愿意等待(Willing-To-Wait)

大部分的latch都属于这种类型(Willing-To-Wait)。

任何时候，只有一个进程可以访问内存中的某一个数据块，如果进程因为别的进程正占用块而无法获得Latch时，他会对CPU进行一次spin(旋转)，时间非常的短暂，spin过后继续获取，不成功仍然spin，直到spin次数到达阀值限制(这个由隐含参数_spin_count指定)，此时进程会停止spin,进行短期的休眠，休眠过后会继续刚才的动作，直到获取块上的Latch为止。

长期等待锁存器(Latch Wait Posting)

此时等待进程请求Latch不成功，进入休眠，他会向锁存器等待链表(Latch Wait List)压入一条信号，表示获取Latch的请求，当占用进程释放Latch时会检查Latch Wait List，向请求的进程传递一个信号，激活休眠的进程。Latch Wait List是在SGA区维护的一个进程列表，他也需要Latch来保证其正常运行，默认情况下share pool latch和library cache latch是采用这个机制。

不等待（No-Wait）

这种类型的latch比较少，对于这种类型的latch来说，都会有很多个可用的latch。当一个进程请求其中的一个latch时，会以no-wait模式开始请求。如果所请求的latch不可用，则进程不会等待，而是立刻请求另外一个latch。只有当所有的latch都不能获得时，才会进入等待。

free buffer waits:

顾名思义就是db buffer里没有空闲可用buffer，使得当前会话当前进程等待内存db buffer中的可用空间。如果SQL充分绑定变量、I/0不是写太慢、Dbwr进程足够，那这种等待可能说明Buffer Cache 设置的偏小

library cache pin：

“LIBRARY CACHE PIN”通常是发生在编译或重新编译PL/SQL,VIEW,TYPES等object时

当我们对object进行维护,如”ALTER”,”GRANT”,”REVOKE”时,就会使object变得无效, 通过object的”LAST_DDL”属性可查看到这些变化.当object变得无效时,Oracle 会在第一次访问此object时试图去重新编译它,如果此时其他session已经把此object pin到library cache中,就会出现问题,特别时当有大量的活动session并且存在较复杂的dependence时.在某种情况下,重新编译object可能会花几个小时时间,从而阻塞其它试图去访问此object的进程。

library cache lock：

当要对一个过程或者函数进行编译时，需要在library cache中pin该对象。在pin该对象以前，需要获得该对象handle的锁定，如果获取失败，就会产生library cache lock等待。如果成功获取handle的lock，则继续在library cache中pin该对象，如果pin对象失败，则会产生library cache pin等待。如果是存储过程或者函数，可以这样认为：如果存在librarycache lock等待，则一定存在library cache pin等待；反过来，如果存在library cache pin等待，不一定会存在library cache lock等待；但如果是表引起的，则一般只有library cache lock等待，则不一定存在library cache pin。

可能发生library cache pin和library cache lock的情况：
1、在存储过程或者函数正在运行时被编译。
2、在存储过程或者函数正在运行时被对它们进行授权、或者移除权限等操作。
3、对某个表执行DDL期间，有另外的会话对该表执行DML或者DDL。
4、PL/SQL对象之间存在复杂的依赖性

gc buffer busy

gc就是global cache，11g的gc buffer busy，在10g中就叫global cache buffer busy。gc buffer busy产生的原因和单实例的 buffer busy waits 类似就是一个时间点节点a的实例向节点b请求block的等待。11g开始gc buffer busy分为gc buffer busy acquire和gc buffer busy release。

产生原因：热块，低效sql（越多的数据块请求到buffer cache 中，那么越可能造成别的会话等待。）数据交叉访问（RAC数据库，同一数据在不同数据库实例上被请求访问）

所以RAC建议不同的应用功能在不同的数据库实例上被访问

gc buffer busy acquire：

gc buffer busy acquire是当session#1尝试请求访问远程实例(remote instance) buffer，但是在session#1之前已经有相同实例上另外一个session#2请求访问了相同的buffer，并且没有完成，那么session#1等待gc buffer busy acquire。

gc buffer busy release：

gc buffer busy release是在session#1之前已经有远程实例的session#2请求访问了相同的buffer，并且没有完成，那么session#1等待gc buffer busy release。

gcs log flush sync

GCS日志刷新同步

flush 是Oracle为了保证Instance Recovery实例恢复机制，而要求每一个current block在本地节点local instance被修改后(modify/update) 必须要将该current block相关的redo 写入到logfile 后（要求LGWR必须完成写入后才能返回)，才能由LMS进程传输给其他节点使用。

The cause of this wait event 'gcs log flush sync' is mainly - Redo log IO performance.

enq:TX-row lock contention：

行锁，当一个事务开始时，如执行INSERT/DELETE/UPDATE/MERGE等操作或者使用SELECT ... FOR UPDATE语句进行查询时，会首先获取锁，直到该事务结束。当然在AWR的Segments by Row Lock Waits一栏中也可以看到锁住的对象

产生原因

第一种情况：当两个会话对同一行进行更新时，ORACLE为了保证数据库的一致性，加了一个TX锁，这时另一个或多个会话必须等待第一个会话commit或rollback，否则会一等待下去，这是最为常见的一种模式！

第二种情况：两个或多个会话向具有唯一主健索引的表中插入或更新相同的数据行，既然是唯一主健索引，那么先获得插入的session以TX排它锁模式进行添加，此时其它session只能等待，也是常见的一种情况

第三种情况：两个或多个会话插入或更新具有位图索引的列，跟据位图索引的特性，一个索引键值对应多个数据行的rowid值，此种情况下也是以TX模式访问，一般在OLTP系统中很少有位图索引，但不排除个别系统，本例中不存在位图索引。

第四种情况：IO慢、log file sync慢、commit慢、cluster集群慢等多重原因也会引起

获取该等待事务对应行锁的脚本

select sid, sql_id

from v$session s, v$sql q

where sid in