Enq: TT-contention wait event, enqcontention

Enq: TT-contention wait event, enqcontention

An error occurred while deleting the tablespace in the customer database. I have tried various methods and want to keep track of them:

A waiting event occurs during deletion.

Enq: TT-contention wait event

I wanted to track the issue first and check the wait event. As a result, the tablespace can be deleted normally after the database needs to be restarted, and it feels a little lost,

The following is a simple analysis of the wait time:

The TT queue lock is described as TT and Temporary Table in the official documents. However, after version 8i, more queue locks are involved in tablespace management transactions.

It can also be called enqueue TT as tablespace lock.

Function

The enqueue TT queue lock is used to avoid deadlock during various types of tablespace operations.

The enqueue lock'sID2Indicates the type of operation being performed,ID1The Tablespace number V $ Tablespace. TS # corresponding to this operation #.

ID1/ID2

Id1 is tablespace number V $ Tablespace. TS #. ID2 indicates the operation type in execution. The following code corresponds to the operation type;

 

0-used to avoid deadlocks between drop tablespace and rollback segment Creation

1-create a data file in a specified tablespace in a serialized manner

2-avoid other operations during TSPITR tablespace point in time recovery

4-used to lock the tablespace id when creating a tablespace

8-used to avoid deadlocks during ALTER TABLESPACE

16-that is, the hexadecimal 0 × 10, used to synchronously allocate and recycle disk areas, allocation and deallocation of extents.

32 + is used to add a data file and create a tablespace. ID2 is 32 (decimal) + relative file number

 

The most common TT enqueue contention is the TT-00000x-0000010, namely, allocation and deallocation of extents.

If you encounter a similar problem after 10 Gb, we recommend that you first understand extent management, segment management (ASSM, MSSM ?) Management methods, etc,

Whether the recycle bin recyclebin function is enabled, and the number of extent objects in the recycle bin on the tablespace.

 

The following lists some bugs related to enqueue TT:

 
Bug 8313598-ORA-60 on TT enqueue from DBMS_SPACE.ISDATAFILEDROPPABLE_NAME [ID 8313598.8]
Bug 4732503-Self-deadlock on TT enqueue [ID 4732503.8]
Bug 6809093-Hang due to TT enqueue waits on an UNDO tablespace [ID 6809093.8]
Bug 14055559 System hang due to TT enqueue contention with BIGFILE tablespace resize
Bug 3833893 SMON may hold the TT enqueue preventing alter of TEMPORARY TABLESPACE
Sessions Hang Due to Self Deadlock on TT Enqueue [ID 948668.1]
Bug 3397983-Sessions may hang waiting for TT enqueue [ID 3397983.8]
Bug 3467364-Adding datafiles serializes on the TT enqueue [ID 3467364.8]
Bug 8332021-Cannot add a datafiles when sessions reporting ORA-1653/TT enqueue contention between datafile addition and other segment extension operations [ID 8332021.8]
Bug 2272671-DEADLOCK possible on create index online (KGL lock v. TT enqueue) [ID 2272671.8]
Bug 6762619: ENQUEUE "TT" remains when accessing DBA_FREE_SPACE
Bug 6858962: TT AND US ENQUEUE DEADLOCK
Bug 9439759: TT ENQUEUE IS NOT RELEASED WHEN CANCEL QUERY
Bug 6265482: tt enqueue remains after cancel "SELECT * FROM DBA_EXTENTS"
Bug 7217723: ORA-00060 DEADLOCK DETECTED DUE TO TT AND US ENQUEUE
Bug 4732503: SELF-deadlock tt enqueue on 9.2.0.7 similar to 3425298
Bug 9403168: wait for TT-ENQUEUE FOR INSERT TO BASCIFILES DURING ADDING DATAFILE TO SAME TBS
Bug 9451566: deadlock on TT-ENQUEUE
Bug 3833893: smon gets "tt enqueue" of temp if datefile of temp doesn't EXIST.
Bug 9948775: SELF-DEADLOCK ON TT ENQUEUE
 

The TT portion of the wait event name stands for Temporary Table and typically indicates issues with the temporary tablespace or related storage.

Look for other process that are manipulating temporary tablespaces or tempfiles during the time when these waits were noticed.

Solutions

If an alter database tempfile drop hangs due to a TT enqueue that is retained by the SMON process in Oracle 9.2.0.6 or lower, Oracle bug 3833893 is responsible. this problem has been solved in Oracle 9.2.0.7. for more information, see Note 867681.

Longer waits for the TT enqueue are generally caused by Oracle bugs. If you experience problems with this enqueue type, you should contact Oracle Support for further maintenance ance.

Http://www.itpub.net/forum.php? Mod = viewthread & tid = 1136919 & highlight = [emergency solution] [if any problem occurs, restart it too early. Otherwise, it is difficult to track the root cause]