Author: mac

This wait event is experienced whenever the database needs to open a file. Wait time is recorded beginning just prior to when the open request is issued until the time the request is returned, having succeeded or failed, from the operating system. Problem When this wait is significantly impacting end user performance, you will see…

Enqueues are sophisticated locks for managing access to shared resources like tables, rows, jobs, and redo threads. An enqueue can be requested in different levels/mode: null, row share, row exclusive, share, share row exclusive or exclusive. This wait event indicates a wait for a lock that is held by another session (or sessions) in an…

This event indicates the session is currently waiting on the Undo Segments and is often related to using system managed undo with an auto tuned undo retention period. In 10g, Oracle added the _undo_autotune parameter which allows the database instance to extend and override “UNDO_RETENTION” settings. This is especically true when the datafiles for the…

SQL> select name from v$event_name where name like ‘%TX%’; NAME —————————————————————- enq: TX – row lock contention enq: TX – allocate ITL entry enq: TX – index contention enq: TX – contention     如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复! 诗檀软件专业数据库修复团队 服务热线 ： 13764045638   QQ号:47079569    邮箱：[email protected] enqueue TX事务锁 transaction enqueue，顾名思义这个队列锁用来保护事务信息。   当进程修改某块中的一行数据，则Oracle必须将该事务信息与被改变的这一行联系起来，做法是在块中的row piece的lk上标记ITL位，而实际的ITL记录了这个事务相关的回滚段号USN，以便能够定位其撤销链。 这样做的目的有几个： 允许用户手动rollback或者因为dead transaction的发生而后台(PMON or SMON 取决于_cleanup_rollback_entries)回滚该事务。…

TT 队列锁在官方文档中介绍为TT, Temporary Table，但是实际在版本8i之后该队列锁更多参与在表空间管理事务中。 也可以称enqueue TT为tablespace lock。 作用 该enqueue TT队列锁用以在各种类型的表空间操作执行过程中避免出现死锁dead lock。 该enqueue lock的 ID2表明正在执行的操作种类，ID1表明该操作对应的表空间号V$Tablespace.TS#。 ID1/ ID2的含义 id1是tablespace number V$Tablespace.TS#，ID2表明执行中的操作类型。以下是操作类型对应代码;   0- 用以避免在drop tablespace和创建rollback segment之间发生死锁 1- 用以串行化在给定表空间上创建数据文件 2- 用以避免在TSPITR tablespace point in time recovery期间发生其他类型操作 4- 用以在创建tablespace时锁住该tablespace id 8- 用以避免在ALTER TABLESPACE期间发生死锁 16- 即16进制的0x10，用以同步 分配和回收盘区，allocation and deallocation of extents. 32+ 用在增加数据文件add datafile和创建表空间时，其中ID2为 32(十进制的)+相对文件号relative file number     最常见的TT enqueue争用是…

介绍 TM enqueue ，这里的TM 是指Table Manipulation,最常见的enqueue 之一， enq: TM – contention 最常见的enqueue 并发争用等待之一。   使用 TM 锁在下列场景中被申请：  在OPS(早期的RAC)中LGWR会以ID1=0 &  ID2=0去申请该队列锁来检查 DML_LOCKS 在所有实例中是全0还是全非0 当一个单表或分区 需要做不同的表/分区操作时，ORACLE需要协调这些操作，所以需要申请该队列锁。包括： 启用参考约束 referential constraints 修改约束从DIASABLE NOVALIDATE 到DISABLE VALIDATE 重建IOT 创建视图或者修改ALTER视图时可能需要申请该队列锁 分析表统计信息或validate structure时 一些PDML并行DML操作 所有可能调用kkdllk()函数的操作 太多太多了。。。   减少ENQ: TM – Contention   我们可以通过ID1作为object_id来确认大家争用的到底是什么对象，之后我们可以确定具体如何减少这些争用，具体到SQL。   ID1 ID2的含义 ID1 要么是0(LGWR)  要么是object_number， 即DBA_OBJECTS.OBJECT_ID，对应锁住对象的对象号。Id2 is 0 for a normal table…

New to Oracle 10g is the individual tracking of over 180 specific enqueues or internal Oracle locking mechanisms. One of these is the RO enqueue wait event for Object Reuse, and is used to synchronize the work required between a foreground process and background process such as DBWR or CKPT. This enqueue is most often…

Sessions waiting on this event are waiting on locks held during materialized view operations (such as refresh, alter) to prevent concurrent operations on the same materialized view. Solutions A materialized view cannot be fast refreshed more than once in a given period because it is serialized during the commit phase. Ensure that only one session…

Waits on High Water enqueue occur when allocating space beyond the high water mark of a segment during insert operations. Also, this wait occurs when reclaiming free space in lob segments after delete or update operations.  When lob segments are deleted or updated, the space being used is not immediately freed up so consistent reads…

During Direct Path operations, the data is asynchronously written to the database files. At some stage the session needs to make sure that all outstanding asynchronous I/O have been completed to disk. This can also happen if, during a direct write, no more slots are available to store outstanding load requests (a load request could…