部分等待事件说明一

最新推荐文章于 2022-10-24 17:20:45 发布

cuibofen7296

最新推荐文章于 2022-10-24 17:20:45 发布

阅读量161

点赞数

一、buffer busy waits

当进程需要存取SGA中的buffer的时候，它会依次执行如下步骤的操作：

1.获得cache buffers chains latch，遍历那条buffer chain直到找到需要的buffer header
2.根据需要进行的操作类型(读或写)，它需要在buffer header上获得一个共享或独占模式的buffer pin或者buffer lock
3.若进程获得buffer header pin，它会释放获得的cache buffers chains latch，然后执行对buffer block的操作
4.若进程无法获得buffer header pin，它就会在buffer busy waits事件上等待

进程之所以无法获得buffer header pin，是因为为了保证数据的一致性，同一时刻一个block只能被一个进程pin住进行存取，因此当一个进程需要存取buffer cache中一个被其他进程使用的block的时候，这个进程就会产生对该block的buffer busy waits事件。

截至Oracle 9i，buffer busy waits事件的p1,p2,p3三个参数分别是file#,block#和id，分别表示等待的buffer block所在的文件编号，块编号和具体的等待原因编号，到了Oracle 10g，前两个参数没变，第3个参数变成了块类型编号，这一点可以通过查询v$event_name视图来进行验证：

PHP code:

Oracle 9i

SQL> select parameter1,parameter2,parameter3 from v$event_name where name='buffer busy waits';

PARAMETER1 PARAMETER2 PARAMETER3

------------------------ ------------------------ ------------------------

file# block# id

Oracle 10g

PARAMETER1 PARAMETER2 PARAMETER3

------------------------ ------------------------ ------------------------

file# block# class#

在诊断buffer busy waits事件的过程中，获取如下信息会很有用：
1.获取产生buffer busy waits事件的等待原因编号，这可以通过查询该事件的p3参数值获得
2.获取产生此事件的SQL语句，可以通过如下的查询获得：
select sql_text from v$sql t1,v$session t2,v$session_wait t3
where t1.address=t2.sql_address and t1.hash_value=t2.sql_hash_value
and t2.sid=t3.sid and t3.event='buffer busy waits';
3.获取等待的块的类型以及所在的segment，可以通过如下查询获得：

PHP code:

select 'Segment Header' class,a.segment_type,a.segment_name,a.partition_name from dba_segments a,v$session_wait b

where a.header_file=b.p1 and a.header_block=b.p2 and b.event='buffer busy waits'

union

select 'Freelist Groups' class,a.segment_type,a.segment_name,a.partition_name from dba_segments a,v$session_wait b

where a.header_file=b.p1 and b.p2 between a.header_block+1 and (a.header_block+a.freelist_groups) and a.freelist_groups>1 and b.event='buffer busy waits'

union

select a.segment_type||' block' class,a.segment_type,a.segment_name,a.partition_name from dba_extents a,v$session_wait b

where a.file_id=b.p1 and b.p2 between a.block_id and a.block_id+a.blocks-1 and b.event='buffer busy waits' and not exists(select 1 from dba_segments where

header_file=b.p1 and header_block= b.p2);

查询的第一部分：如果等待的块类型是segment header，那么可以直接拿buffer busy waits事件的p1和p2参数去dba_segments视图中匹配header_file和header_block字段即可找到等待的segment名称和segment类型，进行相应调整
查询的第二部分：如果等待的块类型是freelist groups，也可以在dba_segments视图中找出对应的segment名称和segment类型，注意这里的参数p2表示的freelist groups的位置是在segment的header_block+1到header_block+freelist groups组数之间，并且freelist groups组数大于1
查询的第三部分：如果等待的块类型是普通的数据块，那么可以用p1、p2参数和dba_extents进行联合查询得到block所在的segment名称和segment类型

对于不同的等待块类型，我们采取不同的处理办法：
1.data segment header：
进程经常性的访问data segment header通常有两个原因：获取或修改process freelists信息、扩展高水位标记，针对第一种情况，进程频繁访问process freelists信息导致freelist争用，我们可以增大相应的segment对象的存储参数freelist或者freelist groups；若由于数据块频繁进出freelist而导致进程经常要修改freelist，则可以将pctfree值和pctused值设置较大的差距，从而避免数据块频繁进出freelist；对于第二种情况，由于该segment空间消耗很快，而设置的next extent过小，导致频繁扩展高水位标记，解决的办法是增大segment对象的存储参数next extent或者直接在创建表空间的时候设置extent size uniform
2.data block：
某一或某些数据块被多个进程同时读写，成为热点块，可以通过如下这些办法来解决这个问题：
(1)降低程序的并发度，如果程序中使用了parallel查询，降低parallel degree，以免多个parallel slave同时访问同样的数据对象而形成等待降低性能
(2)调整应用程序使之能读取较少的数据块就能获取所需的数据，减少buffer gets和physical reads
(3)减少同一个block中的记录数，使记录分布于更多的数据块中，这可以通过若干途径实现：可以调整segment对象的pctfree值，可以将segment重建到block size较小的表空间中，还可以用alter table minimize records_per_block语句减少每块中的记录数
(4)若热点块对象是类似自增id字段的索引，则可以将索引转换为反转索引，打散数据分布，分散热点块
3.undo segment header：
undo segment header争用是因为系统中undo segment不够，需要增加足够的undo segment，根据undo segment的管理方法，若是手工管理模式，需要修改rollback_segments初始化参数来增加rollback segment，若是自动管理模式，可以减小transactions_per_rollback_segment初始化参数的值来使oracle自动增多rollback segment的数量
4.undo block：
undo block争用是由于应用程序中存在对数据的读和写同时进行，读进程需要到undo segment中去获得一致性数据，解决办法是错开应用程序修改数据和大量查询数据的时间

小结：buffer busy waits事件是oracle等待事件中比较复杂的一个，其形成原因很多，需要根据p3参数对照Oracle提供的原因代码表进行相应的诊断，10g以后则需要根据等待的block类型结合引起等待时间的具体SQL进行分析，采取相应的调整措施

二、db file parallel write

处理此事件时，需要注意

1）db file parallel write事件只属于DBWR进程。

2）缓慢的DBWR可能影响前台进程。

3）大量的db file parallel write等待时间很可能是I/O问题引起的。（在确认os支持异步io的前提下，你可以在系统中检查disk_asynch_io参数，保证为TRUE。可以通过设置db_writer_processes来提高DBWR进程数量，当然前提是不要超过cpu的数量。）
     DBWR进程执行经过SGA的所有数据库写入，当开始写入时，DBWR进程编译一组脏块（dirty block），并且将系统写入调用发布到操作系统。DBWR进程查找在各个时间内写入的块，包括每隔3秒的一次查找，当前台进程提交以清除缓冲区中的内容时：在检查点处查找，当满足_DB_LARGE_DIRTY_QUEUE、_DB_BLOCK_MAX_DIRTY_TARGET和FAST_START_MTTR_TARGET阀值时，等等。
     虽然用户会话从来没有经历过db file parallel write等待事件，但这并不意味着它们不会受到这种事件的影响。缓慢的DBWR写入性能可以造成前台会话在write complete waits或free buffer waits事件上等待。DBWR写入性能可能受到如下方面的影响：I/O操作的类型（同步或异步）、存储设备（裸设备或成熟的文件系统）、数据库布局和I/O子系统配置。需要查看的关键数据库统计是当db file parallel write、free buffer waits和write complete waits等待事件互相关联时，系统范围内的TIME_WAITED和AVERAGE_WAIT。
     如果db file parallel write平均等待时间大于10cs（或者100ms），则通常表明缓慢的I/O吞吐量。可以通过很多方法来改善平均等待时间。主要的方法是使用正确类型的I/O操作。如果数据文件位于裸设备（raw device）上，并且平台支持异步I/O，就应该使用异步写入。但是，如果数据库位于文件系统上，则应该使用同步写入和直接I/O（这是操作系统直接I/O）。除了确保正在使用正确类型的I/O操作，还应该检查你的数据库布局并使用常见的命令监控来自操作系统的I/O吞吐量。例如sar -d或iostat -dxnC。
     当db file parallel write平均等待时间高并且系统繁忙时，用户会话可能开始在free buffer waits事件上等待。这是因为DBWR进程不能满足释放缓冲区的需求。如果free buffer waits事件的TIME_WAITED高，则应该在高速缓存中增加缓冲区数量之前说明DBWR I/O吞吐量的问题。
     高db file parallel write平均等待时间的另一个反响是在write complete waits等待事件上的高TIME_WAITED。前台进程不允许修改正在传输到磁盘的块。换句话说，也就是位于DBWR批量写入中的块。前台的会话在write complete waits等待事件上等待。因此，write complete waits事件的出现，一定标志着缓慢的DBWR进程，可以通过改进DBWR I/O吞吐量修正这种延迟。

三、db file scattered read

这种情况通常显示与全表扫描相关的等待。
当数据库进行全表扫时，基于性能的考虑，数据会分散(scattered)读入Buffer Cache。如果这个等待事件比较显著，可能说明对于某些全表扫描的表，没有创建索引或者没有创建合适的索引，我们可能需要检查这些数据表已确定是否进行了正确的设置。

然而这个等待事件不一定意味着性能低下，在某些条件下Oracle会主动使用全表扫描来替换索引扫描以提高性能，这和访问的数据量有关，在CBO下Oracle会进行更为智能的选择，在RBO下Oracle更倾向于使用索引。

因为全表扫描被置于LRU（Least Recently Used，最近最少适用）列表的冷端（cold end），对于频繁访问的较小的数据表，可以选择把他们Cache到内存中，以避免反复读取。

当这个等待事件比较显著时，可以结合v$session_longops动态性能视图来进行诊断，该视图中记录了长时间（运行时间超过6秒的）运行的事物，可能很多是全表扫描操作（不管怎样，这部分信息都是值得我们注意的）。

四、db file sequential read

如果这个等待事件比较显著，可能表示在多表连接中，表的连接顺序存在问题，没有正确地使用驱动表；或者可能索引的使用存在问题，并非索引总是最好的选择。在大多数情况下，通过索引可以更为快速地获取记录，所以对于编码规范、调整良好的数据库，这个等待事件很大通常是正常的。有时候这个等待过高和存储分布不连续、连续数据块中部分被缓存有关，特别对于DML频繁的数据表，数据以及存储空间的不连续可能导致过量的单块读，定期的数据整理和空间回收有时候是必须的。

需要注意在很多情况下，使用索引并不是最佳的选择，比如读取较大表中大量的数据，全表扫描可能会明显快于索引扫描，所以在开发中就应该注意，对于这样的查询应该进行避免使用索引扫描。

五、db file single write

对数据文件头的更新，以刷新数据文件记录的检查点信息，典型的情况是在数据文件编号紊乱的环境中

六、direct path read

与直接读取相关联的等待事件。当ORACLE将数据块直接读入会话的PGA（进程全局区）中，同时绕过SGA（系统全局区）。PGA中的数据并不和其他的会话共享。即表明，读入的这部分数据该会话独自使用，不放于共享的SGA中。

在排序操作(order by/group by/union/distinct/rollup/合并连接)时，由于PGA中的SORT_AREA_SIZE空间不足，造成需要使用临时表空间来保存中间结果，当从临时表空间读入排序结果时，产生direct path read等待事件。

使用HASH连接的SQL语句，将不适合位于内存中的散列分区刷新到临时表空间中。为了查明匹配SQL谓词的行，临时表空间中的散列分区被读回到内存中(目的是为了查明匹配SQL谓词的行)，ORALCE会话在direct path read等待事件上等待。

使用并行扫描的SQL语句也会影响系统范围的direct path read等待事件。在并行执行过程中，direct path read等待事件与从属查询有关，而与父查询无关，运行父查询的会话基本上会在PX Deq:Execute Reply上等待，从属查询会产生direct path read等待事件。

直接读取可能按照同步或异步的方式执行，取决于平台和初始化参数disk_asynch_io参数的值。使用异步I/O时，系统范围的等待的事件的统计可能不准确，会造成误导作用。

七、direct path write

在DSS系统中，存在大量的direct path read是很正常的，但是在OLTP系统中，通常显著的直接路径读（direct path read）都意味着系统应用存在问题，从而导致大量的磁盘排序读取操作。

直接路径写（direct paht write）通常发生在Oracle直接从PGA写数据到数据文件或临时文件，这个写操作可以绕过SGA。

这类写入操作通常在以下情况被使用：
·直接路径加载；
·并行DML操作；
·磁盘排序；
·对未缓存的“LOB”段的写入，随后会记录为direct path write(lob)等待。

最为常见的直接路径写，多数因为磁盘排序导致。对于这一写入等待，我们应该找到I/O操作最为频繁的数据文件（如果有过多的排序操作，很有可能就是临时文件），分散负载，加快其写入操作。

八、enqueue

当处理enqueue等待时,熟记这些要点:

1 Enqueues 是应用到数据库对象的locks.与latch(应用于SGA内存)不同.
2 Enqueues 是由应用程序发起,具有事务性.(具体参见下文)
3 Oracle session 正在等候获取一个特定的enqueue. enqueue的名字和mode被记录在P1参数里.
针对不同类型的enqueue竞争应采用不同的方式解决.
4 一直到Oracle 9i,enqueue wait event 代表所有enqueue waits；从Oracle 10g开始enqueue waits被分类定义(下文详细介绍).

What Is an Enqueue?
一个enqueue是什么由具体环境决定. 如果它被看成是动词,则表示将一个lock请求置于一个队列的动作.如果被看成是名词,它表示一个特定的lock,比如TX事务enqueue.

Enqueues 是一种非常精密的锁定机制,用来管理访问数据库共享资源,比如(objects, background jobs, and redo threads).Oracle使用enqueues出于两个目的: 第一,当enqueue为不兼容模式(mode)时,enqueues防止并发sessions共享数据库资源. 第二,当enqueue为兼容模式时,enqueues容许并发sessions共享数据库资源.

当session A请求一个数据库对象上的锁资源时,
如果被请求的锁模式为不兼容模式,且该数据库对象已经被另一个session 以不兼容模式锁持有,则session A将它的锁请求放置于一个队列里并且按顺序等待被唤起.这个事件被称为enqueue wait.

Enqueue waits 除了包括buffer locks (discussed in the “Buffer Busy Waits” section), library cache locks, row cache locks, and PCM (Parallel Cache Management) locks,也是对各种local locks的等待.

What Is an Enqueue Resource?
An enqueue resource是由于受到一个enqueue lock的数据库资源.
Oracle通过内部数组结构进行管理.
通过X$KSQRS (kernel service enqueue resource) or V$RESOURCE 视图可以看到具体条目.
SQL> select * from v$resource;

ADDR    TY       ID1       ID2
-------- -- ---------- ----------
6D304434 XR       4       0
6D304594 CF       0       0
6D3046F4 RT       1       0
6D30474C RS       25       1
6D304904 MR       3       0
6D30495C MR       4       0
6D3049B4 MR       5       0
6D304B24 MR       1       0
6D304B7C MR       2       0
6D304BD4 MR       201       0
6D305634 TA       6       1

ADDR    TY       ID1       ID2
-------- -- ---------- ----------
6D305B5C PW       1       0
6D3069DC TS       3       1

已选择13行。

SQL>
根据以上查询输出,我们可以看到enqueue resource structure 由lock type和两个参数组成. Oracle用两个字符符号例如(TX, TM, TS, MR, RT, etc)表示lock type. ID1,ID2两个参数由于lock type的不同所以具有不同含义.
具体可以参见<>第六章 Enqueue部分 Table6-3.
在Oracle10g之前,ID1和ID2对于不同类型lock的含义并未公开.
在Oracle10g中可以通过如下查询轻松得到.
col name for a20
col parameter2 for a20
col parameter3 for a20
select name,parameter2,parameter3 from v$event_name
/
稍后在本文enqueue资源争用部分再对ID1与ID2作详细介绍.

可以被lock manager并行锁的enqueue resources的最大数量由ENQUEUE_RESOURCES初始化参数控制.该参数默认值通常来讲已经足够,如果在应用程序中使用并行DML操作,可以考虑增加ENQUEUE_RESOURCES的值.
关于ENQUEUE_RESOURCES [10..65535]
At database startup time, Oracle allocates the number of enqueues specified by the ENQUEUE_RESOURCES parameter. The default value of ENQUEUE_RESOURCES is derived from the SESSIONS parameter and is usually adequate, as long as its value is greater than DML_LOCKS + 20. For three or fewer sessions, the default value is the number of database files + 20. For 4 to 10 sessions, the default value is the number of database files + ((SESSIONS - 3) * 5) + 20. For more than 10 sessions, it is the number of database files + ((SESSIONS - 10) * 2) + 55.
If you explicitly set ENQUEUE_RESOURCES to a value higher than DML_LOCKS + 20, then Oracle uses the value you provide. Oracle will automatically allocate additional enqueues from the shared pool as needed if the number specified by ENQUEUE_RESOURCES is exceeded. You can check resource usage by querying V$RESOURCE_LIMIT.
不能获取an enqueue resource 的进程会报ORA-00052: "maximum number of enqueue resources exceeded" error.

V$RESOURCE_LIMIT视图提供系统资源使用的信息.通过这个视图可以监控很多数据库资源(resources, locks, or processes)的消耗情况.
注意一下这个视图的几个字段:
MAX_UTILIZATION 表示实例启动后该类资源的最大消耗值
以下监控与enqueue有关的资源消耗情况:

col name for a18
col limit_usage for a15
select RESOURCE_NAME name,
         CURRENT_UTILIZATION cur_usage,
         MAX_UTILIZATION max_usage,
         LIMIT_VALUE limit_usage,
   INITIAL_ALLOCATION init_allo
  from v$resource_limit
where resource_name in ('enqueue_resources','enqueue_locks',
                     'dml_locks','processes','sessions')
/
NAME             CUR_USAGE  MAX_USAGE LIMIT_USAGE    INIT_ALLO
------------------ ---------- ---------- --------------- -----------
processes                19       24       90             90
sessions                23       30       104          104
enqueue_locks             13       24    1480          1480
enqueue_resources       13       13  UNLIMITED          676
dml_locks                0       7  UNLIMITED          456

What Is an Enqueue Lock?

An enqueue lock 是lock本身.Oracle用一个单独的数组管理.可以通过X$KSQEQ (kernel service enqueue object) 或者 V$ENQUEUE_LOCK 试图察看.隐含参数_ENQUEUE_LOCKS影响这个数组的大小.

Oracle使用不同的结构管理TX and TM 队列.
X$KTCXB (kernel transaction control transaction object—the base view for V$TRANSACTION_ENQUEUE) and X$KTADM (kernel transaction access definition dml lock). TRANSACTIONS and DML_LOCKS 初始化参数会决定管理TX and TM 队列的结构大小.
当然通过查询V$LOCK可以获得所有数据库locks的信息.也是我们诊断enqueue争用的一个重要视图.

Enqueue Architecture

在数据库内部,enqueue architecture 和 sga中cache buffers architecture十分近似. (读过eygle  <入门,进阶与诊断案例>>这本书的朋友可以参照学习enqueue architecture)
enqueue architecture 的最主要组件由enqueue hash chains latches, enqueue hash table, enqueue hash chains, and enqueue resources组成.
它们之间的数量关系:
enqueue hash chains latch (1:m) a  hash bucket(1:1) a enqueue hash chain .

子enqueue hash chains latches 保护enqueue hash table and hash chains.默认状态下enqueue hash chains latches的个数等于CPU_COUNT,这个数量可以通过隐含参数_ENQUEUE_HASH_CHAIN_LATCHES调整.

根据resource type and identifiers  v$resource(TY,ID1,ID2) Enqueue resources 被hash到enqueue hash table 并且被置于相应enqueue hash chains.要使用某个enqueue resource必须获取相应enqueue hash chain. enqueue hash table 的大小来源于SESSIONS初始化参数,也可以通过设置_ENQUEUE_HASH设置.
If you ever need to increase the ENQUEUE_RESOURCES parameter significantly from its default value, you might want to keep an eye on the sleep rate of the enqueue hash chains latches. This is because the enqueue hash table length will remain the same because it is derived from SESSIONS, not from ENQUEUE_RESOURCES. The combination of a high demand for enqueue resources and a small enqueue hash table will result in a higher hash collision rate and potentially lengthy hash chains. This problem manifests itself as latch contentions for the enqueue hash chains latches. In this case, you need to increase the _ENQUEUE_HASH.
enqueue hash table length = ((SESSIONS – 10) * 2) + 55
通过dump enqueue 结构进一步学习:
alter session set events ’immediate trace name enqueues level 3’;

Decoding Enqueue Type and Mode

Oracle 通过对P1列的解码,我们可以得知enqueue type 和 mode.

select sid, event, p1, p1raw,
   chr(bitand(P1,-16777216)/16777215)||chr(bitand(P1,16711680)/65535) type,
   mod(P1, 16) "MODE"
  from v$session_wait
where event ='enqueue'
/

另一种方法.通过v$session_wait(p1raw)列也可以.
The values from the above example are from a 64-bit Oracle Database. You can ignore the leading zeros and focus on the last 4 bytes (that is, the last eight numbers). The high order 2 bytes give the enqueue type. Using 54580006 as an example, the 2 high order bytes are 0x5458Hex. Now, 54Hex is decimal 84 and 58Hex is decimal 88, so the enqueue type can be discovered as follows: (Appendix B has a complete list of enqueue types.)
select chr(84) || chr(88) from dual;CH--TX

Oracle 10g后enqueue wait event name 可以通过v$session_wait(event)直接获得.

Processes request enqueue locks in one of these six modes: Null (N), Row Share (RS), Row Exclusive (RX), Share (S), Share Row Exclusive (SRX), or Exclusive (X)
这部分可参见Oracle相关文档.

Common Causes, Diagnosis, and Actions

由于enqueue type种类繁多,产生an enqueue wait event的原因也不同.我们可以根据多个session正在竞争的enqueue的type和mode找到不同的解决方法.
对于每一种type的enqueue,Oracle内部通过X$KSQST 维护一条相应的记录.在Oracle9i中,通过V$ENQUEUE_STAT视图, 可以具体获得不同type enqueue的请求统计信息(实例启动后的统计值).

-- Oracle 7.1.6 to 8.1.7
select inst_id,
   ksqsttyp inst_lock,
   ksqstget inst_Gets,
   ksqstwat inst_Waits
  from x$ksqst
where ksqstwat > 0
order by inst_id,ksqstwat
/

-- Oracle9i Database and above
select *
  from v$enqueue_stat
where cum_wait_time > 0
order by inst_id, cum_wait_time
/

下面就最常见的一些enqueue等待事件进行讨论.

Wait for TX Enqueue in Mode 6

对于 TX enqueue in mode 6 的等待是最常见的enqueue wait
(In Oracle Database 10g, the wait event name is enq: TX—row lock contention.) .当一个事物试图update 或 delete 一行或多行已经被另一个事物锁定的记录时这个等待事件发生.通常这是由应用程序引起的.
eg.
在Session A中:
update a set object_id=0 where object_id=11;
与此同时在Session B中执行相同的语句,Session B is hung:
update a set object_id=0 where object_id=11;
Session B一直会等待session A 提交或回滚此事物.
没有其他方法可以释放Session A持有的row exclusive lock(Kill session A会引起事物被pmon回滚并释放相应lock资源)

The following listing shows an example of TX enqueue wait in mode 6 as seen in the V$LOCK view:

ADDR    KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK                               -------- -------- --- -- ------ ------ ----- ------- ----- -----                                                                A3950688 A395069C  10 TM 188154    0    3    0    3    0
A304E2A0 A304E2B0  10 TX  65585 147836    0    6    3    0
01AD23D4 01AD24A4  20 TX  65585 147836    6    0 10    1A3950A28 A3950A3C  20 TM 188154    0    3    0 10    0

每当你发现TX enqueue等待事件时,首要任务是找出哪个session是锁阻塞者(session A),哪些session是锁等待者(session B),在一个繁忙的系统中,很科能伴有多个锁等待者.
使用以下sql定位即可:
/*
BLOCKER_SID: 锁持有者sid(阻塞sid)
WAITER_SID: 等待者sid
MODE_HELD: 锁持有模式
REQUEST_MODE: 请求模式
*/
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
/

你也可通过以下查询定位,哪些数据库资源正在被征用.
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 like 'enq%'
/

当发生TX Enqueue in Mode 6等待事件时
获取到waiting session 和 blocking session正在执行的sql语句十分重要.我们可以通过这个sql语句定位应用程序可能出现问题(1 很有可能应用程序没有commit 2 没有及时commit 或 rollback DML操作这就需要结合应用需求而调整了)的大致范围.

Wait for TX Enqueue in Mode 4—ITL Shortage

TX enqueue in mode 4 的等待事件我们分成3部分讨论.
1> ITL (interested transaction list) shortage
2 > Unique key enforcement
3 > Bitmap index entry
Ok 我们先从ITL Shortage说起.

SQL> select  dbms_rowid.ROWID_RELATIVE_FNO(rowid) file_no,
  2       dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blk_no
  3 from a
  4 where rownum<2
  5  /

FILE_NO    BLK_NO
---------- ----------
      4    1550

SQL> alter system dump datafile 4 block 1550;

系统已更改。

Itl          Xid                                              Uba          Flag  Lck       Scn/Fsc
0x01 0x000a.017.00000128  0x00802ee0.004b.2a  C--- 0  scn 0x0000.00076ef1
0x02 0x0005.01c.00000133  0x00803f33.0059.28  --U- 9  fsc 0x0009.00079e2b
0x03 0x0002.02c.0000012a  0x00800605.005b.1b  C--- 0  scn 0x0000.00076d65

ITL是数据块内"感兴趣的事物列表",是数据块内事物插槽(transaction slot).由建表时INITRANS 和 MAXTRANS子句决定. "--U-"表示此ITL正在被使用.
在默认情况下,表所包含的每个数据块中有1个ITL,索引有2个ITL,每个ITL占据24个字节,以USN.SLOT#.WRAP#. 形式包含事务ID. 每一个DML事物被处理之前必须占有数据块内ITL空间,当某个数据库块内所有可用的ITL都在使用中，并且PCTFREE中没有空间让ORACLE动态分配一个新的ITL事物插槽时，ITL争用就会发生,在这种情况下,会话将持续等待,直到一个事务被提交或回滚.

The following listing shows an example of the TX enqueue wait in mode 4 that is caused by ITL shortage, as seen in the V$LOCK view.

ADDR    KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK                            -------- -------- --- -- ------ ------ ----- ------- ----- -----                                                                               8A2B6400 8A2B6414 8 TM 3172    0    3    0 248    0
89EF3A0C 89EF3A1C 8 TX 131147    13    0    4 248    0
01A4177C 01A41848 9 TX 131147    13    6    0 376    18                                           A2B6388 8A2B639C 9 TM 3172    0    3    0 376    0

从Oracle 9i开始可以通过以下sql定位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
/

Wait for TX Enqueue in Mode 4—Unique Key Enforcement

Unique or primary key enforcement is yet another reason you might see contention for the TX enqueue in mode 4. (In Oracle Database 10g, the wait event name is enq: TX—row lock contention.)
This only occurs when multiple concurrent sessions insert the same key value into a table that has one or more unique key constraints. The first session to insert the value succeeds, but the rest freeze and wait until the first session commits or rolls back to see if “ORA-00001 unique constraint (%s.%s) violated” should be raised.

The following listing shows an example of a TX enqueue wait in mode 4 as seen in the V$LOCK view that is due to unique key enforcement. What is the difference between this listing and the one caused by ITL shortage? Notice that the waiter (SID=8) has two TX entries in the V$LOCK view. This doesn’t mean that it owns two transactions. In fact, the V$TRANSACTION view shows only two transactions—one for SID 8 and another for SID 9. This shows SID 8 is waiting for the TX lock held by SID 9, and it wants a share lock (mode 4) on the object. SID 8 also holds a TX lock for its own transaction. Another thing you should be aware of is the object ID that is recorded in ID1 of the DML transaction (TM) is always the table ID, not the index ID, although a unique key is enforced through an index.

Wait for TX Enqueue in Mode 4—Bitmap Index Entry

A wait for the TX enqueue in mode 4 can also occur when multiple sessions try to update or delete different rows that are covered by the same bitmap entry. Of course, this does not apply if the application does not use bitmap indexes.

Unlike the B-tree index entry, which contains a single ROWID, a bitmap entry can potentially cover a range of ROWIDs. So when a bitmap index entry is locked, all the ROWIDs that are covered by the entry are also locked.

The following listing shows an example of a TX enqueue wait in mode 4 as seen in the V$LOCK view due to bitmap entry. What is the difference between this listing and the preceding one in the unique key enforcement? Can you tell if you are dealing with a bitmap index entry or unique key enforcement issue by looking at the V$LOCK view output? No, you can’t. The object ID in the TM lock doesn’t help either, as it is the object ID of the table and not the index. That is why it is very important for you to capture the SQL statement of the waiting and blocking sessions.

If the waiting session is attempting an insert, you are dealing with a unique key enforcement issue.
如果waiting session正在试图作insert操作.
If the waiting session is attempting an update or delete, most likely you are dealing with a bitmap entry issue.如果waiting session正在试图作update or delete操作

In order to resolve the contention, you have to hunt down the offending user. However, the offending user is not always the user who holds the lock. That user was there first, for crying out loud. If the user has a legitimate reason to hold the lock, the waiters should back out of their transactions.

Wait for ST Enqueue

每个数据库只有一个ST锁。
修改UET$(用户范围表)和FET$(空闲范围表)的数据库操作需要ST锁，这包括删除、截取、结合等动作。
ST锁争用表明有多个活动会话在字典管理的表空间中执行动态磁盘空间分配或解除分配。不是使用TEMPORARY子句创建的临时表空间和经历大范围分配和解除分配的字典管理的表空间是ST锁争用的主要原因。

减少ST锁争用的方法：

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

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