当前位置：Oracle研究中心 > 故障案例 >
【案例】Oracle数据库查询seg$ hang住 出现enqueue (tt)阻塞的解决办法
时间:2016-10-19 22:20 来源:Oracle研究中心 作者:HTZ 点击: 次
天萃荷净
运维DBA反映生产环境Oracle数据库查询seg$ hang住 出现enqueue (tt)阻塞，结合MOS分析TT阻塞产生的原因和解决办法。
查询dba_free_space的进程一直hang住，查询不出来结果，到现场后，手动查询dba_free_space果然hang住。
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
故障现象分析：
CURTIME SESS HASH_VALUE ID1 ID2 LMODE REQUEST TYPE CTIME
---------- ------------------------- ---------- ---------- ---------- ---------------- ---------------- ------------------------- ----------
09:08:08 Waiter: stat1:67 1477532720 85 0 4||Share Temp Table 35705
09:08:08 Waiter: stat1:152 2159410169 85 0 4||Share Temp Table 1813
09:08:08 Waiter: stat1:311 4053158416 85 0 4||Share Temp Table 29645
09:08:08 Waiter: stat1:374 1477532720 85 0 4||Share Temp Table 35705
09:08:08 Waiter: stat1:582 4053158416 85 0 4||Share Temp Table 31080
09:08:08 Waiter: stat1:591 1478719543 85 0 4||Share Temp Table 105
09:08:08 Waiter: stat1:399 4053158416 85 0 4||Share Temp Table 27042
09:08:08 Waiter: stat1:31 3374778041 85 1787451954 6||Exclusive Other type 37445
09:08:08 Holder: stat1:31 3374778041 85 0 6||Exclusive Temp Table 37469
SQL> oradebug setospid 16772
Oracle pid: 47, Unix process pid: 16772, image: oracle@query1 (TNS V1-V3)
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> oradebug tracefile_name
/oracle/app/oracle/admin/stat1/udump/stat1_ora_16772.trc
WAIT #1: nam='enqueue' ela= 2939375 p1=1414791172 p2=85 p3=0
WAIT #1: nam='enqueue' ela= 2939300 p1=1414791172 p2=85 p3=0
WAIT #1: nam='enqueue' ela= 2939330 p1=1414791172 p2=85 p3=0
WAIT #1: nam='enqueue' ela= 2939432 p1=1414791172 p2=85 p3=0
WAIT #1: nam='enqueue' ela= 2939343 p1=1414791172 p2=85 p3=0
WAIT #1: nam='enqueue' ela= 2939370 p1=1414791172 p2=85 p3=0
WAIT #1: nam='enqueue' ela= 2939358 p1=1414791172 p2=85 p3=0
WAIT #1: nam='enqueue' ela= 2939362 p1=1414791172 p2=85 p3=0
WAIT #1: nam='enqueue' ela= 2939367 p1=1414791172 p2=85 p3=0
SQL> select sid,username,program,module,osuser ,sql_hash_value,prev_hash_value from v$session where sid=31;
SID USERNAME PROGRAM MODULE OSUSER SQL_HASH_VALUE
---------- ------------------------------ ------------------------------------------------ ------------------------------------------------ ------------------------------ --------------
PREV_HASH_VALUE
---------------
31 SYS sqlplus@query1 (TNS V1-V3) sqlplus@query1 (TNS V1-V3) oracle 0
3374778041
SQL> select sql_text from v$sql where hash_value=3374778041;
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------
select file#, block# from seg$ where ts# = :1
通过上面的信息，可以看出进程31自己把自己给阻塞了，锁的类型是tt，而执行的切是一个select语句，怀疑是由于bug导致的，由于tt多数都是bug导致。
通过mos查询到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
此信息来生于maclean的blog
查询的n个bug就是没有找到一个跟我们环境一样的。在数据库中没有找到其它有异常的进程，查看了一下数据库，已经有几年没有重启过，实例是10年起起来的，想想那时，我还当入dba这个行业，于是想是否能通过重启解决问题，当跟客户一说，客户直接就重启了，重启后，问题得到解决。
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_ LOGINS SHU
--------------- -------- ---------------------------------------------------------------- ----------------- ------------------- ------------ --- ---------- ------- ----------- ---------- ---
DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
----------------- ------------------ ---------
1 stat1 query1 9.2.0.8.0 2010-11-17 21:41:02 OPEN NO 1 STARTED ALLOWED NO
ACTIVE PRIMARY_INSTANCE NORMAL
本文固定链接: http://www.htz.pw/2013/06/02/9-2-0-8%e6%95%b0%e6%8d%ae%e5%ba%93%e6%9f%a5%e8%af%a2seg-hang%e4%bd%8f%ef%bc%8c%e5%87%ba%e7%8e%b0enqueue-tt%e9%98%bb%e5%a1%9e.html | 认真就输
Oracle研究中心
查询dba_free_space的进程一直hang住，查询不出来结果，到现场后，手动查询dba_free_space果然hang住。
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
故障现象分析：
1，查询v$lock视图发现如下的信息
CURTIME SESS HASH_VALUE ID1 ID2 LMODE REQUEST TYPE CTIME
---------- ------------------------- ---------- ---------- ---------- ---------------- ---------------- ------------------------- ----------
09:08:08 Waiter: stat1:67 1477532720 85 0 4||Share Temp Table 35705
09:08:08 Waiter: stat1:152 2159410169 85 0 4||Share Temp Table 1813
09:08:08 Waiter: stat1:311 4053158416 85 0 4||Share Temp Table 29645
09:08:08 Waiter: stat1:374 1477532720 85 0 4||Share Temp Table 35705
09:08:08 Waiter: stat1:582 4053158416 85 0 4||Share Temp Table 31080
09:08:08 Waiter: stat1:591 1478719543 85 0 4||Share Temp Table 105
09:08:08 Waiter: stat1:399 4053158416 85 0 4||Share Temp Table 27042
09:08:08 Waiter: stat1:31 3374778041 85 1787451954 6||Exclusive Other type 37445
09:08:08 Holder: stat1:31 3374778041 85 0 6||Exclusive Temp Table 37469
2，trace进程发现如下信息
SQL> oradebug setospid 16772
Oracle pid: 47, Unix process pid: 16772, image: oracle@query1 (TNS V1-V3)
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> oradebug tracefile_name
/oracle/app/oracle/admin/stat1/udump/stat1_ora_16772.trc
WAIT #1: nam='enqueue' ela= 2939375 p1=1414791172 p2=85 p3=0
WAIT #1: nam='enqueue' ela= 2939300 p1=1414791172 p2=85 p3=0
WAIT #1: nam='enqueue' ela= 2939330 p1=1414791172 p2=85 p3=0
WAIT #1: nam='enqueue' ela= 2939432 p1=1414791172 p2=85 p3=0
WAIT #1: nam='enqueue' ela= 2939343 p1=1414791172 p2=85 p3=0
WAIT #1: nam='enqueue' ela= 2939370 p1=1414791172 p2=85 p3=0
WAIT #1: nam='enqueue' ela= 2939358 p1=1414791172 p2=85 p3=0
WAIT #1: nam='enqueue' ela= 2939362 p1=1414791172 p2=85 p3=0
WAIT #1: nam='enqueue' ela= 2939367 p1=1414791172 p2=85 p3=0
3，查询当前的sql
SQL> select sid,username,program,module,osuser ,sql_hash_value,prev_hash_value from v$session where sid=31;
SID USERNAME PROGRAM MODULE OSUSER SQL_HASH_VALUE
---------- ------------------------------ ------------------------------------------------ ------------------------------------------------ ------------------------------ --------------
PREV_HASH_VALUE
---------------
31 SYS sqlplus@query1 (TNS V1-V3) sqlplus@query1 (TNS V1-V3) oracle 0
3374778041
SQL> select sql_text from v$sql where hash_value=3374778041;
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------
select file#, block# from seg$ where ts# = :1
通过上面的信息，可以看出进程31自己把自己给阻塞了，锁的类型是tt，而执行的切是一个select语句，怀疑是由于bug导致的，由于tt多数都是bug导致。
通过mos查询到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
此信息来生于maclean的blog
查询的n个bug就是没有找到一个跟我们环境一样的。在数据库中没有找到其它有异常的进程，查看了一下数据库，已经有几年没有重启过，实例是10年起起来的，想想那时，我还当入dba这个行业，于是想是否能通过重启解决问题，当跟客户一说，客户直接就重启了，重启后，问题得到解决。
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_ LOGINS SHU
--------------- -------- ---------------------------------------------------------------- ----------------- ------------------- ------------ --- ---------- ------- ----------- ---------- ---
DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
----------------- ------------------ ---------
1 stat1 query1 9.2.0.8.0 2010-11-17 21:41:02 OPEN NO 1 STARTED ALLOWED NO
ACTIVE PRIMARY_INSTANCE NORMAL
本文固定链接: http://www.htz.pw/2013/06/02/9-2-0-8%e6%95%b0%e6%8d%ae%e5%ba%93%e6%9f%a5%e8%af%a2seg-hang%e4%bd%8f%ef%bc%8c%e5%87%ba%e7%8e%b0enqueue-tt%e9%98%bb%e5%a1%9e.html | 认真就输
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【案例】Oracle数据库查询seg$ hang住 出现enqueue (tt)阻塞的解决办法
本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/1008.html
