enq: TT – contention等待事件，enqcontention

enq: TT – contention等待事件，enqcontention

客戶資料庫刪除資料表空間異常，說各種辦法都嘗試過，我也想好好跟蹤下：

刪除的時候出現等待事件

enq: TT – contention等待事件

本來想跟蹤先，看看等待事件問題，結果由於需要要重啟資料庫，重啟後，資料表空間可以正常刪除，感覺略微有點失落，

現對該等待時間做簡單分析：

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進位的0×10，用以同步 分配和回收盤區，allocation and deallocation of extents.

32+ 用在增加資料檔案add datafile和建立資料表空間時，其中ID2為 32(十進位的)+相對檔案號relative file number

 

最常見的TT enqueue爭用是 TT-00000x-0000010  即allocation and deallocation of extents.

如果10g以後遇到類似問題 ， 建議先瞭解一下extent management、segment management(ASSM、MSSM?)管理方式等，

是否開啟了資源回收筒recyclebin功能，以及該資料表空間上資源回收筒對象的extent數量。

 

enqueue TT相關的一些BUG列表如下：

 
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 assistance.

http://www.itpub.net/forum.php?mod=viewthread&tid=1136919&highlight=【應急解決辦法】【有問題不可以過早重啟，不然很難跟蹤到根本原因】