Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1434374
  • 博文数量: 556
  • 博客积分: 12626
  • 博客等级: 上将
  • 技术积分: 5799
  • 用 户 组: 普通用户
  • 注册时间: 2006-01-11 15:56
个人简介

从事IT基础架构多年,发现自己原来更合适去当老师……喜欢关注新鲜事物,不仅限于IT领域。

文章分类

全部博文(556)

文章存档

2019年(6)

2018年(15)

2017年(17)

2016年(11)

2015年(2)

2014年(2)

2013年(36)

2012年(54)

2011年(100)

2010年(41)

2009年(72)

2008年(14)

2007年(82)

2006年(104)

分类: Oracle

2006-03-06 13:14:30

"enqueue" Reference Note

This is a reference note for the wait event "enqueue" which includes the following subsections:
  • (eg: For waits seen in )
  • (eg: For waits seen in )
See for an introduction to Wait Events.

Definition:

  • Versions:7.0 - 9.2 Documentation: 9.0
  • Enqueues are local locks that serialize access to various resources. This wait event indicates a wait for a lock that is held by another session (or sessions) in an incompatible mode to the requested mode. See (about V$LOCK) for details of which lock modes are compatible with which.

    Enqueues are usually represented in the format "TYPE-ID1-ID2" where

    • "TYPE" is a 2 character text string
    • "ID1" is a 4 byte hexadecimal number
    • "ID2" is a 4 byte hexadecimal number
  • P2 =
  • P3 =
  •   Wait Time:

    The actual wait time depends on the lock type. In most cases Oracle waits for up to 3 seconds or until posted that the enqueue resource is now available (whichever occurs first). When the wait event times out Oracle will check that the session holding the lock is still alive and if so wait again. Deadlock detection is also performed against deadlock sensitive locks.

      Finding Blockers:

    If a session is stuck waiting on an "enqueue" wait one can use to find the blocker/s and other waiters: It can often be simpler just to look at waiters in V$LOCK directly:
      SELECT * FROM v$lock WHERE request > 0;
    or to see blockers as well:
      SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, 
             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
      ;
    " above). To qualify how many waits have really occurred you need the enqueue waits statistic from , or from the "Statistics" section of the StatsPack or Estat report.
    Eg: Assume
            Wait Events show enqueue time_waited=3000 total_waits=10
            Statistics  show enqueue waits has a count of 2
        This means there were 2 actual waits whose individual wait times totalled 
        to 3000 hundredths of a second (ie: 30 seconds).
    
    To determine which enqueues are causing the most waits system-wide look at (Oracle9i onwards only) thus:
      SELECT  eq_type "Lock", 
              total_req# "Gets",
              total_wait# "Waits",
    	  cum_wait_time
        FROM V$enqueue_stat 
        WHERE Total_wait# > 0
      ;
    
    In Oracle8i or earlier look at instead thus:
      SELECT  ksqsttyp "Lock", 
              ksqstget "Gets",
              ksqstwat "Waits"
        FROM X$KSQST where KSQSTWAT > 0;
    
    The above give the system wide number of waits for each lock type. Remember that it only takes one long wait to distort the average wait time figures. One can also look at:
    • Sessions with high numbers of "enqueue waits" in
    • Sampling of to find waiting / blocking sessions
    for example scenarios which can cause TX lock waits. TM DML enqueue Generally due to application issues, particularly if foreign key constraints have not been indexed. The following two articles describe referential integrity issues related to TM locking: Example TM locks During Referential Integrity Enforcement TM locks and Foreign Key Constraints ST Space management enqueue Usually caused by too much space management occurring (Eg: small extent sizes, lots of sorting etc..) See for more information about the ST enqueue.
    补充一个RAC下查询锁的语句
      SELECT DECODE(request,0,'Holder: ','Waiter: ')||inst_id||':'||sid sess,
             id1, id2, lmode, request, type
        FROM GV$LOCK
       WHERE (id1, id2, type) IN
                 (SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
       ORDER BY id1, request
      ;
    阅读(1623) | 评论(0) | 转发(0) |
    给主人留下些什么吧!~~