Oracle学习之路【六】确定当前ORACLE的性能瓶颈

确定当前ORACLE的性能瓶颈
首先，利用V$SYSTEM_EVENT视图执行下面的查询查看数据库中某些常见的等待事件
select * from v$system_event where event in
      ('buffer busy waits','db file sequential read',
       'db file scattered read','enqueue',
       'free buffer waits','latch free',
       'log file parallel write','log file sync');

接着，利用下面对V$SESSION和V$SESSION_EVENT视图进行查询，研究具有对上面显示的内容有贡献的等待事件的会话

select se.sid,s.username,se.event,
       se.total_waits,se.time_waited,se.average_wait
from v$session s,v$session_event se
where s.username is not null
  and se.sid=s.sid
  and s.status='ACTIVE'
  and se.event not like '%SQL*Net%';

为了找到与所连接的会话有关的当前等待事件，使用下面的查询。这些信息是动态的，为了查看一个会话的等待最多的事件是什么，需要多次执行此查询。

select sw.sid,s.username,sw.event,sw.wait_time,
       sw.state,sw.seconds_in_wait
from v$session s,v$session_wait sw
where s.username is not null
  and sw.sid=s.sid
  and sw.event not like '%SQL*Net%'
  order by sw.wait_time desc;

SID  USERNAME   EVENT             WAIT_TIME  STATE        SEC_IN_WAIT
29   OLUSER14    db file sequential read          12   WAITED KNOWN TIME     1
32   ID2USER      db file scattered read           0    WAITING                 0
51   VENDOR1     log file sync                  0    WAITING                 0

会话29正等待db file sequential read。下面的查询显示了有关该试验的等待的其他信息。
select sid,event,p1text,p1,p2text,p2,p3text,p3 
from v$session_wait 
where sid between 28 and 52
  and event not like '%SQL%'
  and event not like '%rdbms%';
SID  EVENT             P1TEXT   P1   P2TEXT   P2      P3TEXT   P3
29   db file sequential read     file#   67     block#   19718      blocks   1
32   db file scattered read      file#   67     block#   17140      blocks   32
51   db file sequential read     file#   63     block#   7556       blocks   1
其中可以看出，有两个会话正访问相同的数据文件，而且有可能是相同的段。P1清楚地显示了这个事实。有一个会话正在执行权表扫描，而另一个正使用索引扫描。利用P1、P2的信息，很容易发现这个段是什么段。下面的查询将做这件事情。
select owner,segment_name,segment_type,tablespace_name
from dba_extents
where file_id=&filed_in
  and &blockid_in between block_id and block_id+blocks-1;

Enter value for filed_in: 67
Enter value for blockid_in:19718

OWNER  SEGMENT_NAME     SEGMENT_TYPE            TABLESPACE_NAME
OYWJ1       STUDENT            TABLE                    USER01
查到访问表STUDENT的查询导致了某些等待。

下面来看看是什么样的查询语句导致等待
select sql_text from v$sqltext where (hash_value,address)= 
(select sql_hash_value,sql_address from v$session where sid=150);






某些常见的等待事件及说明
buffer busy waits	指出等待数据库缓冲区高速缓存中的缓冲区，这表示某个会话正把这个缓冲区读入高速缓存或修改它，也可能是支持许多并发INSERT操作的表上缺乏足够的空闲列表
db file parallel write	指出与dbwr进程有关的等待。可能与dbwr进程或配置的dbwr i/o进程的数目有关，还可以表示较低或较高争用的设备
Db file scattered read	指出与全盘扫描有关的等待。可能表示I/O争用或太多的I/O
Db file sequential read	指出与索引扫描有关的等待。可能表示I/O争用或太多的I/O
Db single write	指出与在一个检查点种标题写入有关的等待。典型的情况是在数据文件编号紊乱的环境中
Direct path read	指示并允许直接I/O相关的等待。一般在设备上的I/O争用
Direct path write	与 Direct path read相同。只不过对应于写入
enqueue	指出与锁定各种Oracle资源和组件的内部排队机制有关的等待。
Free buffer inspected	指出并确定数据库缓冲区高速缓存中空闲缓冲区以便把数据放入该高速缓存的进程有关的等待
Free buffer waits	指出数据库缓冲区高速缓存中缺乏空闲缓冲区。这可能表示数据库缓冲区高速缓存大小灰列表（高速缓存中修改过的快的列表）写到磁盘的速度不够快。如果是这样，可配置更多的dbwr进程或I/O进程。这个事件在空闲缓冲区检查事件未发现任何空闲缓冲区时产生
Latch free	指出正等待的某号匝锁的争用。应该保证已经通过设置相应的init.ora参数，将匝锁数目调整为所允许的最大数目。如果问题继续存在，应该确定是什么引起该匝锁的争用并处理基本问题。要确定找到问题的根源。匝锁空闲事件是大问题的表征。
Library cache load lock	这是把对象装入库高速缓存所要求的。此等待事件可在发生大量重新装载（一般由缺乏重用sql语句或尺寸不合适的共享池区所引起的）时产出
Library cache lock	指出与访问数据库高速缓存的多个进程的并发性有关的等待。可指出尺寸设置不合适的共享池区，因为需要获得这个锁以便在库高速缓存中定位对象
Library cache pin	此等待事件也与库高速缓存并发性有关，并且在给定对象需要在库高速缓存中修改或检查时产生此事件
Log buffer space	指出不能跟上服务器进程写入重做日志缓冲区的速率的lgrw的可能问题。一般表示日志缓冲区尺寸问题或者联机重做日志所处的设备较慢或争用
Log file parallel write	指出从重做日志缓冲将重做记录写入磁盘的相关等待。一般表示联机重做日志志所处的设备较慢或争用	
Log file single write	指出写入到日志文件的标题块。可能表示检查点种的等待
Log file switch
(archiving needed)	指出arch跟不上lgwr的等待。可能是因为联机重做日志大小、设备较慢或设备上的争用较多（一般由日志文件位于数据文件所在的设备上引起）。还可能需要研究多个arch进程或I/O从进程的可能性
Log file sync	指出用户提交时重做日志缓冲区清洗有关的等待。如果此等待持续不变，可能表示放置联机重做日志文件的设备争用或设备较慢
SQL*Net message from/to client	指出用户进程和服务器进程通信中占用的时间，在某些不常见的情况下，可清楚地显示出网络传输问题，但多数情况下可以忽略。如果应用程序支持ARRAYSIZE（如Oracle Forms、SQL*Plus、Pro*C等）配置，则配置ARRAYSIZE为一个大于默认值的值可能会减少这个事件的等待
SQL*Net message from dblink	指出与分布式处理（从其他数据库中select）有关的等待。这个事件通过DBLINKS联机查找其他数据时产生。如果查找的数据多数是静态的，则移动这些数据到本地表并根据需要刷新它，会在性能上出现很大的差别
Tiemr in sksawat	指出一个较慢的arch进程，这或者是由于数据库的多个组件争用，或者是由于没有执行归档的足够的I/O进程
transaction	指出与回退操作阻塞有关的等待
Undo segment extension	指出回退段的区和扩充的动态分配。这可能表示回退段的数目不是最佳，或者这些回退段的MINEXTENTS数不是最佳
Write complete waits	指示与写入到磁盘缓冲区有关的等待，这种写入可能有数据库缓冲区高速缓存的正常老化引起