常见等待事件的处理

--查看数据库中需要关注的等待事件：
select sw.seq#,sw.sid||','||s.serial# sids,s.username,sw.event,sw.P1,sw.p2,sw.p3,sw.wait_time "WAIT",
   sw.state,sw.seconds_in_wait sec,s.status,to_char(s.logon_time,'dd/hh24:mi:ss') logon_time
   from v$session s,v$session_wait sw
   where
   sw.sid =s.sid
   and s.username is not null
   and sw.event not like '%SQL*Net%'
   and sw.event not like 'PX Deq%'
   and sw.event not like 'rdbms ipc message'
   order by sw.event,s.username ;

 

   当在等待事件中查到有library cache ping这种锁等待时，要在后台执行以下语句：
   select s.sid||','||s.serial# sid_serial,kglpnmod "mode held",kglpnreq "request"
   from sys.x$kglpn p, v$session s
   where p.kglpnuse = s.saddr
   and kglpnhdl = (select p1raw from v$session_wait where sid=&sid_in_library_cache_pin);

   查到hold>0 的sid，如果local=no 则kill掉这个进程

   alter system kill session '21,9108';
   select * from v$process where addr in (select paddr from v$session where sid=49 and serial#=8149);

 

   ----如果系统中有大量library cache pin / lock 等待，需要查出pin和lock的holder，然后杀掉.

a.查找pin holder 并kill:

select 'alter system kill session '''||a.sid||','||a.serial#||''';'
from v$session a,x$kglpn b
where a.saddr=b.kglpnuse  and b.KGLPNMOD<>0 and b.kglpnhdl in  (  select p1raw  from  v$session_wait  where event ='library cache pin')
;

 

b.查找lock holder 并kill

select 'alter system kill session '''||a.sid||','||a.serial#||''';'
 from v$session a, X$KGLLK b where  b.kgllkmod>0  and
 b.KGLLKHDL in  (  select p1raw  from  v$session_wait  where event ='library cache lock')
 and a.sid=b.KGLLKSNM   ;
 
 
 
 
 
 
------编译过程或者package时候，只要去编译，就会有library cache pin 等待，说明有session在访问这个对象，查v$access :
 
SQL> @access
select b.sql_text text,a.sid sid ,a.serial# sria#,a.username username, c.type type,a.machine machine
from v$session a ,v$sqltext b ,v$access c
where c.object=upper('&1')
  and c.type in ('TABLE','PACKAGE','PROCEDURE')
  and a.sid=c.sid
  and b.address = a.sql_address
  and b.hash_value = a.sql_hash_value
order by a.sid,a.serial#,b.piece;

TEXT                                                                    SID      SRIA# USERNAME                       TYPE                     MACHINE
---------------------------------------------------------------- ---------- ---------- ------------------------------ ------------------------ ----------------------------------------------------------------
UPDATE CURRENT_ACCOUNT_LIST A SET ACTUAL_PAY_VOUCHER_DATE = :B6        1192      40302 username                       PROCEDURE               

 
 

##############################################################  
buffer busy waits
##############################################################

说明buffer cache中有一些buffers被多个进程尝试同时访问,逻辑读比较多 (已经获得了latch,再访问buffers的争用.不牵涉到latch的争用）。
查看V$WAITSTAT观察各种类型buffer wait的统计信息。
SELECT class, count FROM V$WAITSTAT WHERE count > 0 ORDER BY count DESC;

也可以查看V$SESSION_WAIT观察当前buffer wait信息,其中P1-FILE_ID, P2- BLOCK_ID,再通过DBA_EXTENTS查找哪些SEGMENT被争用。
例：
Select * from v$session_wait where event='buffer busy waits';

SELECT owner, segment_name
  FROM DBA_EXTENTS
 WHERE file_id = <&p1>
   AND <&p2> BETWEEN block_id AND block_id+blocks-1;

对于segment header争用：
1、很可能是freelist的争用，LMT中的ASSM可以解决问题。
2、如果不能使用ASSM,可以增加FREELIST数量，还不行就使用FREELIST GROUP
查看segment 的freelist 情况：
SELECT SEGMENT_NAME, FREELISTS
  FROM DBA_SEGMENTS
 WHERE SEGMENT_NAME = segment name
   AND SEGMENT_TYPE = segment type;

对于data block争用：
1、优化sql，避免使用选择性差的index
2、使用LMT中的ASSM，或增加FREELIST避免多个进程同时插入数据到相同的块。

对于undo header争用：
使用automatic undo 管理，或者增加rollback segments

对于undo block争用：
使用automatic undo 管理，或者增大rollback segments size

SQL> SELECT class, count FROM V$WAITSTAT WHERE class like 'undo%';

CLASS                   COUNT
------------------ ----------
undo header              3634
undo block              78293

SQL>

 

V$WAITSTAT视图保持自实例启动所有的等待事件统计信息。常用于当你发现系统存在大量的"buffer busy waits"时据此做出适当调整。

V$WAITSTAT中的常用列
? CLASS：块类别
? WAITS：本类块的等待次数
? TIME：本类块的总等待时间

等待发生的原因：
1.undo段头部：没有足够的回滚段
2.数据段头部/数据段空闲列：空闲列争夺
3.数据块冲突
4.缓存存在大量的CR复制
5.range检索时，索引列存在大量不连续
6.全表检索的表有大量被删除记录
7.高并发的读写块

 

 

 

##############################################################
free buffer waits
##############################################################

发现找不到free buffer来读取数据文件块到内存中，只能通知dbwr 将脏数据写入磁盘，以获得free buffer。
在全部sql语句都调整完好的情况下，这一选项表明需要增大db_buffer_cache。
形成等待dbwr完成的因素有：
1、 IO慢，异步磁盘，使用raw device
2、 等待某些资源，比如latch
3、 Buffer cache 太小，使得dbwr花费很长的时间用来写脏数据
4、 Buffer cache 太大，使得dbwr没有能力写出足够的脏数据来满足需求。

检查哪里阻塞了DBWR
1、 检查V$FILESTAT看哪里发生了最多的write操作
2、 检查操作系统的IO情况
3、 检查CACHE是否太小，查看buffer cache hit ratio是否很低，使用V$DB_CACHE_ADVICE 判断是否应该增大CACHE
4、 如果CACHE足够大，IO也没有问题，则考虑使用异步IO，或者多个DBWR进程，以满足负载
调整DB_WRITER_PROCESSES参数（from DBW0 to DBW9 and from DBWa to DBWj），适用于多个CPU(at least one db writer for every 8 CPUs) or multiple processor groups (at least as many db writers as processor groups)
5、 DBWR_IO_SLAVES

 

 

 

-----------------------------------------------------------------------------------------LATCH FREE------------------------------------------------------
kill 所有 latch free 等待的session：

select 'alter system kill session'||' '''||s.sid||','||s.serial#||''''||';'
from v$session_wait sw,v$session s
where sw.event='latch free'
and sw.sid=s.sid
--and s.username not in ('DBMGR');

该等待事件意味着该session正在等待其它session已经持有的latch。 V$LATCHNAME 里有不同类型的latch

latch free 如果是share pool里的latch 就说明是由sql语句造成的，一般是sql语句没有使用绑定变量的结果，让开发人员改写SQL语句，尽量使用绑定变量
latch free 如果是data buffer里的latch就说明是由block存取造成的。

--- 找到latch holder所在session的sid和serial#，考虑是否可以kill掉，缓解数据库的压力：
select a.username, a.sid, a.serial#, a.status, b.pid, b.laddr, b.name
  from v$session a, v$latchholder b
 where a.sid = b.sid;

 

SELECT name, 'Child '||child#, gets, misses, sleeps
  FROM v$latch_children
 WHERE addr in(select P1RAW from v$session_wait where event='latch free')
UNION
SELECT name, null, gets, misses, sleeps
  FROM v$latch
 WHERE addr in(select P1RAW from v$session_wait where event='latch free')
 order by sleeps desc;
 

（latch，内存锁，一个数据库中latch的个数是固定的，一个latch管一定的buffer块，latch free就是进程对latch的争用）
用于保护系统全局区域（SGA）中共享内存结构。latch 就像是一种快速地被获取和释放的内存锁。latch 用于防止共享内存结构被多个用户同时访问。
latch锁是是数据库内部提供的一种维护内部结构的一种低级锁，保护block，为了避免不同的进程随意径直并发修改和访问这些block，否则就很快会破坏block地结构。是cpu访问内存块级别的锁。
一个latch可以维护128个左右的buffers。由于latch使得对block的操作串行化。如果大量进程对相同的block进行操作，必然在这些latch上造成竞争，也就是说必然形成对latch的等待。这在宏观上就表现为系统级的等待。

1、 检查在等待什么类型的LATCH,比如shared pool latch, cache buffer LRU chain
检查V$SESSION_WAIT
P1-Address of latch  p2- Latch number  p3- 休眠等待次数

SELECT n.name, SUM(w.p3) Sleeps
FROM V$SESSION_WAIT w, V$LATCHNAME n
WHERE w.event = 'latch free' AND w.p2 = n.latch#
GROUP BY n.name;

2、 检查LATCH对应的资源使用情况，比如library cache latch竞争严重，一般说明sql没有绑定变量，很多硬解析。则检查the hard and soft parse rates
3、 检查存在LATCH竞争的SESSION所执行的SQL，是否需要优化。

 

 

Library Cache

##############################################################
Shared Pool and Library Cache Latch Contention
##############################################################

主要问题出在parse（sql硬解析）shared pool锁存器和library cache锁存器的争用主要是由于紧密的硬解析。
过多的硬解析通常出现在主要使用带有字面值的SQL语句的应用程序中。

Soft Parse——对SQL进行语法、语义检查，然后把语句变成hash value，hash value与shared pool里面的已缓存的cursor的hash value比较，如果找到了会继续检查一下cursor是否可以重用，若可重用即立即执行，soft parse结束。若不能重用则继续进行hard parse；
Hard parse—— 对SQL进行语法、语义检查，然后把语句变成hash value，hash value与shared pool里面的已缓存的cursor的hash value比较，如果没有找到或者找到不可重用则检查对象定义等并生成执行计划，再执行。
一般情况下soft parse的获取latch的代价要比hard parse少很多，但是如果soft parse数量增加到一定程度，也会引起library cache的争用，争用厉害时也会引发应用响应慢。

 

1、 Unshared SQL
手工检查那些只有一次执行的SQL是否相似：
SELECT sql_text  FROM V$SQLAREA
 WHERE executions < 4  ORDER BY sql_text;

或者用以下sql查出没有绑定变量的sql:
SELECT substr(sql_text,1,40) sql,count(*), sum(executions) total_exec
     FROM v$sqlarea
     WHERE executions < 5
     GROUP BY substr(sql_text,1,40)
     HAVING count(*) > 30
     ORDER BY 2 ;

查找完整的sqltext:
select hash_value from v$sqltext where sql_text like 'select name      , unrecoverable_change#%';
select sql_text from v$sqltext where hash_value='1020831236' order by piece Asc;

select hash_value from v$sqlarea where sql_text like 'select distinct party_no from cif_party_site_view%';
select sql_text from v$sqlarea where hash_value=1829852908;

查询sql占用share_pool的size:
select sum(a.SHARABLE_MEM) from v$sqlarea a where a.SQL_TEXT like 'select distinct party_no from %';
select sum(SHARABLE_MEM)/1024/1024||'M',count(*) from v$sql where sql_text like 'select distinct party_no from cif_party_site_view v where%';

使用下面sql查找当前连接session的硬解析数量。
select a.sid,
       c.username,
       b.name,
       a.value,
       round((sysdate - c.logon_time) * 24) hours_connected
  from v$sesstat a, v$statname b, v$session c
 where c.sid = a.sid
   and a.statistic# = b.statistic#
   and a.value > 1000
   and b.name = 'parse count (hard)'
 order by a.value;

2、 Reparsed Sharable SQL
SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS
FROM V$SQLAREA
ORDER BY PARSE_CALLS desc;
当PARSE_CALLS和EXECUTIONS相近的时候，说明进行了REPARSE。优化这些SQL

3、 By Session
检查是否某个session执行了很多的parse。最好结合时间，看parse率
SELECT pa.sid, pa.value "Hard Parses", ex.value "Execute Count"
  FROM v$sesstat pa, v$sesstat ex
 WHERE pa.sid=ex.sid
   AND pa.statistic#=(select statistic#
       FROM v$statname where name='parse count (hard)')
   AND ex.statistic#=(select statistic#
       FROM v$statname where name='execute count')
   AND pa.value>0;

 

 

##############################################################
cache buffer lru chain
##############################################################

保护buffers在cache中的链(list)，将buffer增加，移动，移出list时，必须要事先得到这个latch。
由大量buffer输入输出引起，比如低效的SQL重复访问不合适的index (large index range scans) or many full table scans。读Buffer会引起buffer在LUR中的移动。
查看Statements with very high logical I/O or physical I/O, using unselective indexes
或者CACHE太小，DBWR不能及时写出脏数据，而使前台进程花费很长的时间保持着latch去寻找free buffer。

 

 

 

##############################################################
cache buffers chains   (LATCH#=66)   热点块
##############################################################

用来当从buffer cache中查找、增加、删除buffer块时首先要获得latch。
u first need to get latch then access chain etc. then get the buffer
if u can't get latch, cache buffer chain comes out. if u can't pin or get buffer simultaneously buffer busy wait comes out
completely different event

体现为某些数据块被严重争用，即热点块。
如果等待的latch是cache buffers chains，则需要根据p1raw查出被争用的hot block和segment名称：

--- 从v$session_wait 中的cache buffers chains等待事件对应的p1raw 如0000000381492948 获得 latch 的addr信息，然后在后台执行：.

select P1RAW from v$session_wait where sid='&sid';

select distinct a.owner,a.segment_name from
dba_extents a,
(select dbarfil,dbablk
from x$bh
where hladdr in
    ('&ADDR')) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk;

输入sid来查找：
select distinct a.owner,a.segment_name from
dba_extents a,
(select dbarfil,dbablk
from x$bh
where hladdr in
    (select p1raw
        from v$session_wait where sid = &latch_requester_sid
     )
) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk ;

 

--- 在后台sys用户下执行
select /*+ RULE */
       e.owner || '.' || e.segment_name segment_name,
       e.extent_id extent#,
       x.dbablk - e.block_id + 1 block#,
       x.tch,
       l.child#
  from sys.v$latch_children l, sys.x$bh x, sys.dba_extents e
 where x.hladdr = '&P1RAW'
   and e.file_id = x.file#
   and x.hladdr = l.addr
   and x.dbablk between e.block_id and e.block_id + e.blocks - 1
 order by x.tch desc;

---一般tch超过100就算热块了

 

---直接用sys用户查出热点块对应的段名：
select distinct a.owner,a.segment_name from
dba_extents a,
(select dbarfil,dbablk
from x$bh
where hladdr in
    (select  addr
    from (select addr
        from v$latch_children
        order by sleeps desc)
        where rownum < 11)) b
 where a.RELATIVE_FNO = b.dbarfil
 and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk;

 或者用以下sql查热点块：
 select object_name
from dba_objects
where data_object_id in
(select obj
from x$bh
where hladdr in
    (select addr
    from (select addr
        from v$latch_children
        order by sleeps desc)
        where rownum < 11)) ;

 

 

 

 

cache buffer lru chain latch 和cache buffers chains latch的区别：

关键点在于他们保护的数据结构不同：
前者保护LRU chain pointer。LRU chain 用来发现free buffers，移动热buffer到MRU端，并协助完成写脏数据和检查点等动作；
后者用来保护hash chain。Hash chain 用来通过hash 算法（根据所在的文件和block id）访问cache在buffer中的blocks。

-----------------------------------------------------------------------latch free end -----------------------------------------------

 

 

###############################
db file scattered read
###############################
当oracle从磁盘上读取多个block到不连续的高速缓存区的缓存中就会发生这个等待事件。
数据库同时读取初始化参数db_file_multiblock_read指定的块数并把这些数据块离散地分布在数据库缓冲区中。
这种情况通常显示与全表扫描或者fast full index扫描相关的等待。
该等待事件在数据库会话等待多块io读取结束地时候产生，比如全表扫描和快速索引扫描。

当数据库进行全表扫时，基于性能的考虑，数据会分散(scattered)读入Buffer Cache。如果这个等待事件比较显著，可能说明对于某些全表扫描的表，没有创建索引或者没有创建合适的索引，我们可能需要检查这些数据表已确定是否进行了正确的设置。
然而这个等待事件不一定意味着性能低下，在某些条件下Oracle会主动使用全表扫描来替换索引扫描以提高性能，这和访问的数据量有关，在CBO下Oracle会进行更为智能的选择，在RBO下Oracle更倾向于使用索引。
当这个等待事件比较显著时，可以结合v$session_longops动态性能视图来进行诊断，该视图中记录了长时间（运行时间超过6秒的）运行的事物，可能很多是全表扫描操作（不管怎样，这部分信息都是值得我们注意的）。

表示通过multiblock read将data读入到很多不连续的内存中（根据file_id/block_id,通过hash算法分布于不同的地方），
通常发生于fast full scan of index，或者full table scan。

查看V$SESSION_WAIT: P1-FILE_ID, P2-BLOCK_ID, P3-NUMBER OF BLCOKS(>1表示读取了多个block)

在大型数据库中，通常物理读的等待和idle wait都排在最前面。当然也要考虑是否有以下的特征：
Direct read wait(fts with parallel) / db file scattered read wait / Poor buffer cache hit ratio / 用户响应时间慢。

--9i开始才有v$sql_plan
查看正在进行全表扫描的语句：
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='TABLE ACESS'
and p.options='FULL'
order by p.hash_value,t.piece;

查看快速索引扫描的sql语句：
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='INDEX'
and p.options='FULL SCAN'
order by p.hash_value,t.piece;

--如果是8i库，可以根据进程的sid来找到进行全表扫描的sql语句。
查看哪些session在进行全表扫描：
SELECT s.sql_address,s.sid, s.sql_hash_value,w.p1,w.p2
  FROM V$SESSION s, V$SESSION_WAIT w
 WHERE w.event LIKE 'db file%read' AND w.sid = s.sid ;

查看是哪个对象
SELECT owner, segment_name
  FROM DBA_EXTENTS
 WHERE file_id = &p1 AND &p2 between block_id AND block_id + blocks - 1 ;

查看是哪个sql
SELECT sql_text
  FROM V$SQL
 WHERE HASH_VALUE=&sql_hash_value AND ADDRESS=&sql_address

解决方法：
1，在合适的字段上建立索引把表的访问方式从全表扫描变为索引扫描，可以有效的降低物理io 。
2，对于大表，在合适的字段，比如年月，地区编码上建立分区把全表扫描变为分区扫描以减少物理io
3，把需要经常扫描的数据库表放在keep池同样会有效的降低物理io

 

#################################################

二者的区别：

a sequential read reads data into contiguous memory , a scattered read reads multiple blocks and scatters them into different buffers in the SGA.

db file scattered read 一般都是等待同时读取多个块到内存中。为了性能和更有效的内存空间利用，oracle一般会把这些块分散在内存中。db file scattered read 等待事件的P3参数指出了每次I/O读取的块数。每次I/O读取多少个块，由参数db_file_multiblock_read_count控制。
全表扫描或者快速索引扫描时一般使用的这种读取块的方式，所以，该等待很多时候都是因为全表扫描引起的

db file sequential read 一般情况下都是指读取单个块到内存中，既然是单个，当然是连续的。你可以发现db file sequential read 等待时间的P3参数一般都是1。索引块，如果不是快速索引扫描，一般都是一个一个块读取的，所以说，这个等待事件很多时候都是索引读取引起的。

 

为什么一般情况下，db file scattered read表示是full table scan ；db file sequential read表示是index scan？
1、dbfile scattered read
首先看这个等待事件，很常见，经常在top5中出现，这表示，一次从磁盘读数据进来的时候读了多于一个block的数据，而这些数据又被分散的放在不连续的内存块中，
因为一次读进来的是多于一个block的，通常来说我们可以认为是全表扫描的类型的读，因为根据索引读表数据的话一次只读一个block，这个数字过大意味着可能全表扫描过多，需要检查sql是否合理的利用了索引，或者是否需要建立合理的索引
2、dbfile sequential read
这个事件表示的是，每次连续的读磁盘的次数，这个象征着IO的数量，如果过多，可能以为着使用index进行查询了过多比例的表数据（即索引的选择性不好），
因为根据索引读数据的话，假设100条记录，根据索引，不算索引本身的读，而根据索引每个值去读一下表数据，理论上最多可能产生100 buffeer gets，而如果是full table scan，则100条数据完全可能在一个block里面，则几乎一次就读过这个block了，就会产生这么大的差异

A db file sequential read is an event that shows a wait for a foreground process
while doing a sequential read from the database.
This is an o/s operation, most commonly used for single block reads. Single
block reads are mostly commonly seen for index block access or table block
access by a rowid (Eg: to access a table block after an index entry has been
seen)

This can also be a multiblock read. Then it will usually
be a read from a SORT (TEMPORARY) segment as multiblock
reads for table scans (and index fast full scans) usually
show up as waiting on "db file scattered read"

A db file scattered read is the same type of event as "db file sequential read",
except that Oracle will read multiple data blocks. Multi-block reads are
typically used on full table scans. The name "scattered read" may seem
misleading but it refers to the fact that multiple blocks are read into DB block
buffers that are 'scattered' throughout memory.

piner：
一般的情况下，只要存在物理的io，就可能存在db file sequential reads  &  db file scattered reads
但是，如果是oltp系统，则更侧重出现db file sequential reads

不是出现这两个事件，系统就要优化，优化很好的系统也会出现，甚至很大。
可以说，这个说明一个指标，而不证明性能。

但是，很多情况下，db file sequential reads&db file scattered reads 的出现，的确是因为语句的性能不好，比如没有走索引，没有走正确的计划，语句本身不优化等等。
通过语句的调整可以大幅度减少该等待事件的次数，提高SGA的data buffer也可以减少该事件的出现，但是，只有在足够优化的情况下，这样做才最有意义。否则，语句本身的问题存在，不能更根本上解决问题。

#################################################
db file sequential read
#################################################

该等待事件通常意味着一次I/O读取（物理io）请求的结束。在大多数的情况下读取一个索引数据的block或者通过索引读取数据的一个block的时候都会去要读取相应的数据文件头的block.
在早期的版本中，会从磁盘中的排序段，通过single block read将data读入到连续的内存中, 往往通过索引（ 选择性差的索引）.
巨大的值暗示着可能存在低效的表连接顺序或者选择性差的索引扫描。

db file sequential read指的是需要一个但当前不在sga中的块，等待从磁盘中读取。

查看V$SESSION_WAIT: P1-FILE_ID, P2-BLOCK_ID, P3-NUMBER OF BLCOKS(=1,通常这个值为1，表明是单个block被读取，如果这个值大于1，则是读取了多个block，这种多block读取常常出现在早期的oracle版本中，从临时段中读取数据的时候)

 

查询各个数据文件i/o的分布：
select d.name name, f.phyrds, f.phyblkrd, f.phywrts, f.phyblkwrt, f.readtim, f.writetim from
v$filestat f, v$datafile d where f.file#=d.file# order by f.phyrds desc, f.phywrts desc;

查询读取物理I/O最多的segment:
select object_name,object_type,statistic_name,value
from v$segment_statistics
where statistic_name='physical reads'
order by value desc;

解决办法：
1，块读取通常是不可避免的，但可以通过最小化不必要的IO以避免不必要的块读取。
2，检查是否使用了不合适的索引扫描的sql，优化sql语句。
（如果有index range scans，但是却使用了不该用的索引－－选择性差，就会导致访问更多block，这个时候应该强迫使用一个可选择的索引，使得访问同样的数据尽可能少的访问索引块，
减少物理io的读取；如果索引碎片较多，那么每个block存储的索引数据就少，这样需要访问的block就多，这个时候最好rebuild index；）
3，可能的话，加大sga中的数据库缓存池以缓冲更多的数据库表块和索引块。
4，重新组织数据库表，比如使用export，import或者CTAS以使表有更好的聚集（cluster_factor)
5，判断表是否适合分区，以减少需要查询的数据块总数。

 

 

############################################
db file parallel read
############################################
当oracle从多个数据文件中并行读取多个block到内存的不连续缓冲中（sga或者是pga）时就可能出现这个等待事件。
v$session_wait  p1代表有多少个文件被读取所请求，p2代表一共有多少个block被请求，p3代表一共有多少次请求。

 

 

 

############################################
direct path read and direct path read (lob)
###############################################
将数据从disk中直接读到PGA中，而绕过SGA. 通常发生在DSS或WH
起因：
1、 排序过大，不能在sort memory中完成，而转移到temp disk中。然后又读入，形成直接读。
2、 Parallel slaves 查询
3、 The server process is processing buffers faster than the I/O system can return the buffers. 表明了很大的IO load
解决：
1、 查询V$TEMPSEG_USAGE, 找出产生sort的sql；查询V$SESSTAT, 查看sort size的大小。
2、 调整sql；如果WORKAREA_SIZE_POLICY=MANUAL, 增大SORT_AREA_SIZE;
如果WORKAREA_SIZE_POLICY=AUTO, 增大PGA_AGGREGATE_TARGET
3、 如果table 被定义为很高的degree of parallelism，将会引导optimizer使用parallel slaves 进行full table scan

direct path write

情况同read，等待直接从PGA中得到buffer，并写入磁盘。

 

 

###############################################
     enqueue 等待：
###############################################

 enqueues是在读取各种不同的数据库资源时在其上产生的锁，该等待意味着在访问同样的数据库资源的时候需要等待其它会话已经获取的锁。

 （典型的现象是很多session是enqueue等待，这些session等待的session的等待事件是db file sequential read,杀掉这个session后，大量enqueue等待就消失了。）

 v#session_wait如果有该事件存在，那么该视图中的p1表示锁类型和模式；p2表示锁ID1 ，以十进制表示的enqueue名称的ID1；p3表示锁ID2

 实际的等待时间依赖于锁的类型，在等待超时后数据库会检查已经获取锁的会话，如果会话仍然存活，那么其它会话会继续等待。

通过查询视图v$lock可以获取锁的相关信息，其中type列表示锁类型，id1表示锁的id1，id2表示锁的id2：
---lock_holder_waiter:
select decode(request,0,'Holder:','Waiter:')|| sid, id1,id2,lmode,request,type from v$lock
where (id1,id2,type) in (select id1,id2,type from v$lock where request>0) order by id1,request;

-- root  blocker
select *
  from v$session s, v$session_wait w, v$transaction t
 where s.sid = w.sid
   and s.TADDR = t.ADDR
   and s.sid in (select sid Session_ID
                   from v$lock
                  where block > 0
                 minus
                 select w.sid Session_ID
                   from v$session_wait w
                  where w.event = 'enqueue');
                 
                 

通过查询视图v$sysstat中的enqueue waits统计信息和statspack报告中的statistics部分可以诊断该事件实际发生的等待次数。
通过查询视图v$enqueue_stat可以诊断是哪些enqueues导致了等待：
select eq_type "lock",
       total_req# "gets",
       total_wait# "waits",
       cum_wait_time
     from v$eneueue_stat
     where total_wait#>0;

解决方法：
基于不同的锁类型有不同的解决方法，经常发生的等待类型为：
1，tx transaction lock： 通常是由于应用的原因造成的锁等待。
2，tm dml enqueue ： 通常是由于应用原因，部分是因为在外键约束上没有建立索引导致的。
3，st space management enqueue ：通常是由于过多的空间管理 （比如因为extent过小而导致extent的频繁分配/大量的排序再导致临时段的分配）而产生的。

 

 

-------------------------------------------------------------------------------------------  library cache pin/lock 等的处理

-- x$kgllk

select INST_ID,USER_NAME,KGLNAOBJ,KGLLKSNM,KGLLKUSE,KGLLKSES,KGLLKMOD,KGLLKREQ,KGLLKPNS,KGLLKHDL
  from X$KGLLK where KGLLKHDL = 'C000000122E2A6D8' order by KGLLKSNM,KGLNAOBJ

library cache lock:
SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION  WHERE SADDR in
  (SELECT KGLLKSES FROM X$KGLLK LOCK_A
   WHERE KGLLKREQ = 0
   AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
               WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
               AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)

 

--X$KGLOB

select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ
from X$KGLOB
where KGLHDADR ='52D6730C'

--x$kglpn
select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,
b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ
from v$session a,x$kglpn b
where a.saddr=b.kglpnuse and b.kglpnhdl = '52D6730C' and b.KGLPNMOD<>0

 

select sid Holder ,KGLPNUSE Sesion , KGLPNMOD Held, KGLPNREQ Req
from x$kglpn , v$session
where KGLPNHDL in (select p1raw from v$session_wait
where wait_time=0 and event like 'library%')
and KGLPNMOD <> 0
and v$session.saddr=x$kglpn.kglpnuse ;

 

 

----如果系统中有大量library cache 等待，需要查出pin和lock的holder，然后杀掉.

a.查找pin holder 并kill:

select 'alter system kill session '''||a.sid||','||a.serial#||''';'
from v$session a,x$kglpn b
where a.saddr=b.kglpnuse  and b.KGLPNMOD<>0 and b.kglpnhdl in  (  select p1raw  from  v$session_wait  where event ='library cache pin')
;

 

b.查找lock holder 并kill

select 'alter system kill session '''||a.sid||','||a.serial#||''';'
 from v$session a, X$KGLLK b where  b.kgllkmod>0  and
 b.KGLLKHDL in  (  select p1raw  from  v$session_wait  where event ='library cache lock')
 and a.sid=b.KGLLKSNM   ;

 

 

 

 

###############################################
row cache objects
###############################################

这个等待事件解释如下：
Row cache objects latch用来保护数据字典缓冲区（row cache的名字主要是因为其中的信息是按行存储的，而不是按块存储）。
进程在装载、引用或者清除数据字典缓冲区中的对象时必须获得该latch。在oracle8i之前，这是一个独立latch。
从oracle9i起，由于引入了多个子共享池的新特性，存在多个row cache objects子latch。
Oracle10g中，该latch也有了一个独立的等待事件：row cache objects。

看一下这个帖子
metalink上：Note:311615.1

http://www.itpub.net/viewthread.php?tid=717614&extra=&highlight=row%2Bcache%2Bobjects&page=1

以下调优方法来自metalink，但是明显没有太多用处

How to Tune Row Cache
---------------------

Tuning Row cache is so limited, some resources point to it as non-tunable,so
if the pct_succ_gets above is not getting closer to "1" or if you detect
a high "Row Cache Objects" Latch contention in performance tuning reports then
you need to add more shared pool by either increasing SHARED_POOL_SIZE or avoid
Sharde Pool Fragmentation.Alternatively, configuring the library cache to an
acceptable size usually ensures that the data  dictionary cache is also properly
sized. So tuning Library Cache will tune Row Cache indirectly. However , the
following tips have direct and important effect on tuning Row Cache:

1. Use Locally Managed tablespaces for your application objects especially
   indexes, this will decrease Row Cache locks in a surprising fashion and
   consequently avoid common hanging problems.

2. Review and amend your database logical design , a good example is to merge or
   decrease the number of indexes on tables with heavy inserts.

 

查询竞争激烈的rowcache：
SELECT parameter
       , sum(gets)
       , sum(getmisses)
       , 100*sum(gets - getmisses) / sum(gets)  pct_succ_gets
       , sum(modifications)                     updates
    FROM V$ROWCACHE
   WHERE gets > 0
   GROUP BY parameter
   order by 4;

 

   把undo_retention设小一些呢，应该是争用的dc_rollback_segments，我们这边前几天刚遇到过，调整undo_retention立刻latch free就消失了。

 

 

 

###############################################
PL/SQL lock timer
###############################################

PL/SQL lock timer
This event is called through the DBMSLOCK.SLEEP procedure or USERLOCK.SLEEP procedure.
 This event will most likely originate from procedures written by a user.

 

select * from dba_dependencies where referenced_name='DBMS_LOCK' and type='PACKAGE'
and referenced_owner='SYS'
and wner='<username>'
可以查询引用了dbms_lock的对象

由于业务需要,常在pl/sql中使用dbms_lock.sleep包,导致系统中的PL/SQL lock timer很高,不知道会不会对性能有影响?dbms_lock.sleep在sleep期间还会占用系统资源吗?

有人写了个存储过程,如存储过程写了锁定某表,然后sleep语句,运行这个存储过程就能看到你所说的wait event

 

 

 

 

###############################################
cursor: pin S:
###############################################

Lucd0 今天出现 cursor: pin S 等待不是该 bug 所致。
而是由于 pol_ben, pol_info 上未收集统计数据， 而查询中使用了
chs_common_function_package.get_2bit_region(b.region_code) = :1
其中 b 为 pol_info
且由于相关表上无统计数据，致对 pol_info 进行全表扫描，然后，对其返回的每一条记录去执行该函数调用导致产生 cursor: pin S 等待。
在为 pol_ben, pol_info 收集完统计数据后， 其执行行计划正常， 同时， cursor: pin S 等待消失。
同时， dbms_stats 仍在继续进行其它表的统计数据的收集。

 

 

 

 

###############################################
read by other session
###############################################

read by other session是在Oracle Database 10g中，引入的一个新事件，此前版本，这个事件在Buffer Busy Wait中记录。
read by other session是９i buffer busy wait的一部分，一个session读取一个数据块的时候，而另一个session正在从数据文件中读取这个数据块
记得好像是大家都要用到一个BLOCK，而这个BLOCK呢，还不在BUFFER CACHE，所以俩人同时要把这个BLOCK读到CACHE中去，所以一个人再读进去的时候，另一个人就在等待READ BY OTHER SESSION

根据文档，这个事件的解释如下：

This event occurs when a session requests a buffer that is currently being read into the buffer cache
by another session. Prior to release 10.1, waits for this event were grouped with the other reasons for
waiting for buffers under the 'buffer busy wait' event

这个等待也就是说明数据库存在读的竞争，所以该等待事件通常和db file sequential read或db file scattered read 同时出现。

以下是一个客户生产数据库中的Top 5 Timed Events：

Top 5 Timed Events                                        Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait  Call
Event                                Waits    Time (s)  (ms)  Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time                                          4,897          52.9
db file sequential read          1,557,308      1,887      1  20.4  User I/O
read by other session            3,818,768      1,769      0  19.1  User I/O
db file scattered read              973,678        807      1    8.7  User I/O
log file sync                        23,299          17      1    0.2    Commit

 

 

在一台10g的数据库上无意中看v$session_wait时看到这个event，这个wait event在9i里面是没有的

10g中将很多wait event都细化了，同样的，这个event来自于buffer busy waits

查了下官方文档：

read by other session

This event occurs when a session requests a buffer that is currently being read into the buffer cache by another session.
Prior to release 10.1, waits for this event were grouped with the other reasons for waiting for buffers under the ‘buffer busy wait’ event

不过看看buffer busy waits的解释还没有更新，没有将第一个原因分离出来

buffer busy waits

Wait until a buffer becomes available. This event happens because a buffer is either being read into the buffer cache by another session
(and the session is waiting for that read to complete) or the buffer is the buffer cache, but in a incompatible mode
(that is, some other session is changing the buffer).

到10g的数据库上看，大部分等待来自于第一个原因