Oracle常见等待事件说明(三)-enqueue/free buffer waits

十、enqueue

1. 排队是应用于数据库对象、重做线程、后台工作的锁，用来控制多个并发会话在锁模式相容/不相容时如何共享相同的资源。排队是事务的，由应用程序初始化。
2. 事件参数(9i环境，10g中参数二、三有所变化)

事件编号：15

事件名：enqueue

参数一：name|mode(不同的锁类型，id1与id2有不同的含义)

参数二：id1

参数三：id2

注：ORACLE9i所有排队的等待都归于enqueue中，ORACLE10g开始，将所有排队按类型分成独立的事件，所以很容量分辨出ID1和ID2的含义。

3. 受到排队锁影响的数据库资源，我们称之为"排队资源"。ORACLE使用内部数组结构来处理排队资源，可以通过x$ksqrs(内核服务排队资源)或v$resource视图来查看。

select s.ADDR as "资源对象地址", s.TYPE, s.ID1, s.ID2 from v$resource s

>> 初始化参数enqueue_resources控制被锁管理器并发锁定的最大排队资源数。出租车系统该参数设置为968。一般默认值是足够用的。

>> 并行DML操作比串行DML操作使用更多的锁。

>> 排队资源超过初始化参数enqueue_resources的设置值，则会发生"ORA-00052"错误。

>> 查询系统资源的使用情况：

SELECT s.resource_name,
       s.current_utilization AS "当前使用数",
       s.max_utilization AS "系统最大使用数",
       s.initial_allocation AS "系统初始化参数分配数",
       s.limit_value
  FROM v$resource_limit s
 WHERE s.resource_name IN ('enqueue_resources', 'enqueue_locks', 'dml_locks',
        'processes', 'processes')

4. 排队锁使用单独的数组而不是排队资源数组来管理排队锁，通过查询x$ksqeq(内核服务排队对象)或v$enqueue_lock视图来看到这种结构。

>> v$resource_limit视图可查看排队锁资源的总体使用情况。查看'enqueue_locks'行的对应列。

>> v$equeue_lock视图(除TX和TM锁)

SELECT s.addr,s.kaddr,s.sid,s.TYPE,s.id1,s.id2,s.lmode,s.request,s.ctime,s.BLOCK
FROM v$enqueue_lock s

ADDR：锁状态对象地址

KADDR：锁地址

LMODE：锁模式(0-NONE 1-NULL 2-RS 3-RX 4-S 5-SRX 6-X )

>> v$lock显示了所有的锁。

5. 各种排队锁类型下，ID1和ID2的含义：

排队锁类型	ID1	ID2
TX	回滚段号和槽号。可以在v$transaction视图的XIDUSN和XIDSLOT列中看到这两个值。	序号。可以在v$transaction视图的XIDSQN列中看到值。
TM	对象ID。可以在dba_objects.object_id中找到该值	总为0。
TS	表空间号。在以在TS$TS#中找到该值。	相对的数据库块地址(DBA)
JQ	总为0。	工作号。
MR	数据文件ID。ORACLE对每个数据文件(包括临时文件)采用一个MR排队。	总为0。
RT	重做线程号。	总为0。

6. 从equeue等待事件中，解码排队类型及模式：

SELECT s.sid,
       s.event,
       s.p1,
       s.p1raw,
       chr(bitand(s.p1, -16777216) / 16777215) ||
       chr(bitand(s.p1, 16711680) / 65535) AS "TYPE",
       MOD(s.p1, 16) AS "MODE"
  FROM v$session_wait s
 WHERE s.event = 'enqueue'

7. 表级TM锁模式的相容性：

模式(LMODE)	(T2)      -	S	X	RS	RX	SRX
(T1)      -	YES	YES	YES	YES	YES	YES
S	YES	YES	NO	YES	NO	NO
X	YES	NO	NO	NO	NO	NO
RS	YES	YES	NO	YES	YES*	YES*
RX	YES	NO	NO	YES*	YES*	NO
SRX	YES	NO	NO	YES*	NO	NO

LMODE	SQL
-	SELECT .. FROM ...
RS	SELECT * FROM ... FOR UPDATE LOCK TABLE TABLE_NAME IN ROW SHARE MODE
RX	INSERT.. UPDATE.. DELETE.. LOCK TABLE TABLE_NAME IN ROW EXCLUSIVE MODE
S	LOCK TABLE TABLE_NAME IN SHARE MODE
SRX	LOCK TABLE TABLE_NAME IN SHARE ROW EXCLUSIVE MODE
X	LOCK TABLE TALBE_NAME IN EXCLUSIVE MODE

8. ORACLE9i中v$enqueue_stat视图中保留实例级别的各种队列锁的请求数量和等待时间。
9. 排队体系结构：

组成：enqueue hash chains锁存器、排队散列表、排队散列链、排队资源

>> 组成形式：

         

>> enqueu hash chains锁存器数目等于CPU_COUNT。这个锁存的数量可以通过初始化参数_enqueue_hash_chain_latches来进行调整。

>> 排队散列表的默认长度来源于SESSIONS参数，可通过_ENQUEUE_HASH参数调整。

>> 排队资源根据其资源类型和标识符被散列到相应的排队散列表，放置于排队散列链中。

>> 如果调整过并发排队资源数的初始化参数enqueue_resources，则需要调整_enqueue_hash初始化参数。因为散列表的长度由SESSIONS参数来决定。

>> 缺省排队散列表的长度=((sessions-10)*2)+55

>> 使用下列命令将排队结构转储到一个跟踪文件：

   alter session set events 'immediate trace name equeues level 3'

---

10. 模式6中的TX enqueue等待(ORACLE10g enq:TX-row lock contention)

>> v$lock视图中的TYPE=TX并且REQUEST=6。

>> 发生在一个事务尝试更新或删除当前被另一个事务锁定的行时发生该等待事件。

>> 查询"谁是阻塞者以及是否存在相同资源的多个等待者"

SELECT /*+ ORDERED */
       a.sid blocker_sid,
       a.username blocker_username,
       a.serial#,
       a.logon_time,
       b.TYPE,
       b.lmode mode_held,
       b.ctime time_held,
       c.sid waiter_sid,
       c.request request_mode,
       c.ctime time_waited
  FROM v$lock b, v$enqueue_lock c, v$session a
 WHERE a.sid = b.sid
   AND b.id1 = c.id1(+)
   AND b.id2 = c.id2(+)
   AND c.TYPE(+) = 'TX'
   AND b.TYPE = 'TX'
   AND b.BLOCK = 1
 ORDER BY time_held, time_waited

说明：

ORACLE要花费很多时间分析多表连接，以决定表的连接顺序。如果SQL语句的连接包括七个以上的表时，因为ORALCE必须评估所有连接的可能，有时会花费30分钟的分析时间。在仅仅八个表的情况下就有4000种可能的顺序。而使用ORDERED标签可以直接给出一个正确的查询连接顺序。实际上就是指定一个驱动表，驱动表的行通常很少。

示例：

-- 强制使用nested loop join 和 4路并行查询
select /*+ ordered use_nl(bonus) parallel(e, 4) */  
   e.ename,
   hiredate,
   b.comm
 from
   emp e,
   bonus b
 where
   e.ename = b.ename

注：在这个查询中有个非常高明的hint-ORDERED，使SQL分析时间大大缩短。

>> 查询enqueue事件等待的资源：

SELECT c.sid waiter_sid, a.object_name, a.object_type
  FROM dba_objects a, v$session b, v$session_wait c
 WHERE (a.object_id = b.row_wait_obj# OR a.data_object_id = b.row_wait_obj#)
   AND b.sid = c.sid
   AND chr(bitand(c.p1, -16777216) / 16777215) || chr(bitand(c.p1, 16711680) / 65535) = 'TX'
   AND c.event = 'enqueue'

---

11. 模式4中的TX enqueue等待

>> 原因一：ITL不足

1) ITL是"感兴趣的事务列表"的缩写，这是一个数据块的事务槽(SLOT)，槽的初始数据由INITRANS子句定义，并且由MAXTRANS子句限制。默认情况下，表所包含的每个数据块中有1个ITL，索引有2个ITL。每个ITL占据24个字节，以USN.SLOT#.WRAP#包含事务ID。

2) 在可以操作数据前，每个DML事务需要在块中获取它自己的ITL空间，当某个块中所有可用的ITL都在使用中，并且PCTFREE中没有空间让ORACLE动态分配一个新的ITL槽时，ITL争用就会发生，在这种情况下，会话将持续等待，直到一个事务被提交或回滚。

3) 可以使用alter system dump datafile block 命令来查看ITL的使用情况，ITL的--U-标识该ITL正在使用。

4) 查询系统所有对象的ITL等待状态，可以使用查询

SELECT s.owner,
       s.object_name,
       s.subobject_name,
       s.object_type,
       s.tablespace_name,
       s.VALUE,
       s.statistic_name
  FROM v$segment_statistics s
 WHERE s.statistic_name = 'ITL waits'
   AND s.VALUE > 0
 ORDER by VALUE DESC

>> 原因二：唯一键实施

>> 原因三：位映射索引条目

---

12. ST enqueue等待

>> 每个数据库只有一个ST锁。

>> 修改UET$(用户范围表)和FET$(空闲范围表)的数据库操作需要ST锁，这包括删除、截取、结合等动作。

>> ST锁争用表明有多个活动会话在字典管理的表空间中执行动态磁盘空间分配或解除分配。不是使用TEMPORARY子句创建的临时表空间和经历大范围分配和解除分配的字典管理的表空间是ST锁争用的主要原因。

>> 减少ST锁争用的方法：

1) 使用本地管理表空间，在ORACLE9i中所有表空间均可本地化管理。

2) 使用CREATE TEMPORARY TABLESPACE TEMPFILE...命令创建所有临时表空间。

3) 

13. 模式3的TM equeue等待

 

十一、free buffer waits (Configuration)

1. 在将一个块读入到缓冲存储器之前，Oracle进程必须发现并获得用于这个块的空闲缓冲区。如果找不到LRU列表上的空闲缓冲区，或缓冲区不可用，那么会话就会在free buffer waits事件上等待。
2. DBWR进程负责在LRU列表上建立清洁缓冲区。
3. ORACLE前台进程扫描LRU列表，要到预先定义的限度才停止扫描，这个限度通常是LRU列表的一定百分比，在ORACLE9i中，这个限度是40%，这个值在视图x$kvit中可以查看到：

select * from x$kvit where kvittag = 'kcbfsp'

KVITTAG	KVITDSC	KVITVAL
kcbfsp	Max percentage of LRU list foreground can scan for free	40

如果扫描LRU列表至该限度依旧未找到合适的空闲缓冲区，前台进程则向DBWR提交，DBWR建立清洁的缓冲区，在DBWR建立清洁缓冲区的工作中，ORACLE进程在free buffer waits事件上等待。

4. ORACLE保持对每个空闲缓冲区请求的计数。可以从v$sysstat视图中查询而得：

select * from v$sysstat s where s.NAME = 'free buffer requested'

5. ORACLE保持对空闲缓冲区请求失败的次数：

select * from v$system_event s where s.EVENT = 'free buffer waits'

注：字段total_waits则表明总等待次数。

6. 可以从v$sysstat视图中查询而得进程必须查看多少缓冲区以获得所要求的free buffer：

select * from v$sysstat s where s.NAME in ('free buffer inspected','free buffer requested')

注：如果inspected数量远远大于requested数量，则意味着进程需要扫描更多的LRU列表以获得可用的缓冲区。

7. 产生该等待事件的原因：

原因	说明
低效率的SQL语句

8. 参数说明：

事件号：137

事件名：free buffer waits        

参数一：ORACLE读取块的文件号file#

参数二：块号block#

参数三：LRU列表的set-id#

9. 等待时间：最多1秒，然后再次查找。