通过v$session、v$session_wait_class,v$system_event,v$event_histogram来确定锁等待

整理自：http://blog.csdn.net/soulcq/article/details/5444361

先分别介绍着四个动态性能视图：

v$session：显示每个当前会话的信息。

v$sesssion_wait_class：显示每个会话中，每个等待事件花费的时间。

v$system_event：

v$event_hisogram：

--1  获取等待事件
select sid,username,
     event,blocking_session,
     seconds_in_wait, wait_time
from v$session where state in ('WAITING')
     and wait_class != 'Idle';

--2  根据上面获得SID和blocking_session (即正在占用锁的SID)两个会话执行的sql
select sid, sql_text
 from v$session s, v$sql q
     where sid in (85,87)
 and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id);
--说明上面的85是sid，87是block_session
--enq: TX - row lock contention   tx事务锁方式block

--3  从v$session_wait_class中获得等待的相关信息
 select wait_class_id,wait_class,total_waits,time_waited
 from v$session_wait_class where sid=87;

结果如下：
WAIT_CLASS_ID    WAIT_CLASS    TOTAL_WAITS    TIME_WAITED
1893977003          Other               1                        0
4217450380          Application       176                    47471
3386400367          Commit            4                         6
2723168908          Idle                  52                      108030
2000153315          Network           53                      0
1740759767          User I/O           7                        5
4108307767          System I/O       5                        0
在这个结果中，显示了每个类中会话的等待事件的次数，还有等待的时间，我们看到application这一级别中，的等待次数是176,而time_waited时间是474.71秒(原来的6986631单位是厘秒单位，百分之一秒).那么这个时候我们还可以从application等待类中寻找引起等待的原因。

--4  v$system_event视图中我们可以获得每种等待的出现次数。
我们这里的application的id为4217450380
select event,total_waits,time_waited
  from v$system_event e,v$event_name n
 where n.event_id=e.event_id
and n.wait_class_id=4217450380;

结果如下：
EVENT    TOTAL_WAITS    TIME_WAITED
enq: RO - fast object reuse    319    133
enq: TM - contention    2    0
enq: TX - row lock contention    162    47470
SQL*Net break/reset to client    14498    2284

假如我们并不知道是什么原因，导致了这个锁的争用，那我们该怎么去定位这个TX-row lock contention的问题呢？
这些数据仅仅告诉了我们ok,用户经历了162次的锁的竞争，共花费了47470厘秒，有可能大多数的等待只有1到2厘秒,那么如何在进行进一步的诊断呢？

--5  Oracle 10g还为我们提供了另外一个视图叫v$event_histogram
它标市了等待事件的周期以及会话等待某一特定事件周期的频度
select wait_time_milli,wait_count
 from v$event_histogram
where event='enq: TX - row lock contention';

结果如下:
WAIT_TIME_MILLI    WAIT_COUNT
1                              0
2                              0
4                              0
8                              0
16                            0
32                            0
64                            0
128                          0
256                          0
512                          0
1024                        0
2048                        0
4096                        162

v$event_histogram视图显示等待时间段以及在这期间会话等待某一特定事件--在这个例子中就是事务锁争用--的次数。
例如，会话等待少于4096毫秒（ms）的事件共162次。WAIT_COUNT列值之和为162.v$event_histogram视图显示，
大多数等待发生在4096毫秒的事件上，这就充分证明了该应用程序正在经历锁的争用问题，如果视图显示等待发生在1毫秒的范围内，我们就不应该认为这是锁争用的问题，因为这样短时间的等待似乎是正常的。