RAC enq: TS - contention 等待事件

newcom

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
1、查询等待事件
SQL> select w.inst_id,w.sid,w.p1,w.p2,w.p3,w.event from gv$session_wait w where event like '%enq%';

   INST_ID        SID         P1         P2         P3 EVENT
---------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------
         2         12 1414725635        996          2 enq: TS - contention
         2        106 1414725635        996          2 enq: TS - contention
         2        505 1414725635        996          2 enq: TS - contention
         2        595 1414725635        996          2 enq: TS - contention

2、查询lock
SQL> SELECT DECODE (request, 0, 'Holder: ', 'Waiter: ') status, SID, inst_id,ctime, id1, id2, lmode, request, TYPE
  2  FROM gv$lock WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM gv$lock WHERE request > 0);
  

STATUS          SID    INST_ID      CTIME        ID1        ID2      LMODE    REQUEST TYPE
-------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----
Waiter:          12          2       7045        996          2          0          3 TS
Waiter:         106          2       7045        996          2          0          3 TS
Holder:         296          2    2346382        996          2          3          0 TS
Waiter:         505          2       7045        996          2          0          3 TS
Waiter:         595          2       7045        996          2          0          3 TS

3、查询等待进程
SQL> select inst_id,sid,username,type,PROCESS,PROGRAM,EVENT from gv$session where sid=296 and inst_id=2;

   INST_ID        SID USERNAME                       TYPE       PROCESS                  PROGRAM                                          EVENT
---------- ---------- ------------------------------ ---------- ------------------------ ------------------------------------------------ ----------------------------------------------------------------
         2        296                                BACKGROUND 28518                    oracle@xsjrac02 (SMON)                           smon timer

4、查询临时表空间大小
SQL> SELECT A.tablespace_name tablespace, D.mb_total,
  2  SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
  3  D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
  4  FROM v$sort_segment A,
  5  (
  6  SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
  7  FROM v$tablespace B, v$tempfile C
  8  WHERE B.ts#= C.ts#
  9  GROUP BY B.name, C.block_size
10  ) D
11  WHERE A.tablespace_name = D.name
12  GROUP by A.tablespace_name, D.mb_total;

TABLESPACE                        MB_TOTAL    MB_USED    MB_FREE
------------------------------- ---------- ---------- ----------
TEMP01                          163839.921       1955 161884.921

SQL> select ts# from sys.ts$ where name = 'TEMP01' and online$ != 3;

       TS#
----------
       996
5、手动触发SMON进程回收临时段
SQL> ALTER SESSION SET events 'immediate trace name DROP_SEGMENTS level 997';

Session altered
6、等待事件依然存在
SQL> select w.inst_id,w.sid,w.p1,w.p2,w.p3,w.event from gv$session_wait w where event like '%enq%';

   INST_ID        SID         P1         P2         P3 EVENT
---------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------
         2         12 1414725635        996          2 enq: TS - contention
         2        106 1414725635        996          2 enq: TS - contention
         2        505 1414725635        996          2 enq: TS - contention
         2        595 1414725635        996          2 enq: TS - contention

6、临时表空间的使用情况
SQL> select INST_ID,TABLESPACE_NAME TSNAME,TOTAL_EXTENTS   TOTAL,USED_EXTENTS USED,FREE_EXTENTS    FREE  from gv$sort_segment  order by inst_id;

   INST_ID TSNAME                               TOTAL       USED       FREE
---------- ------------------------------- ---------- ---------- ----------
         1 TEMP01                               31915         57      31858
         2 TEMP01                               29903        711      29192

问题：
  1、临时表空间的空间大小是没问题。
  2、两个节点分配临时表空间大小几乎相等，而且USED < FREE 。
  按照这样来分析的话那就不应该有enq: TS - contention等待事件。
  3、假如以上分析不正确的话，按照Oracle提供解决enq: TS - contention的方法ALTER SESSION SET events 'immediate trace name DROP_SEGMENTS level tablespace#';
  执行完后enq: TS - contention并没有消失，一直存在。


请教高手指点。

谢谢！

newcom

谢谢  刘大！

Liu Maclean(刘相兵

SQL> select w.inst_id,w.sid,w.p1,w.p2,w.p3,w.event from gv$session_wait w where event like '%enq%';

   INST_ID        SID         P1         P2         P3 EVENT
---------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------
         2         12 1414725635        996          2 enq: TS - contention
         2        106 1414725635        996          2 enq: TS - contention
         2        505 1414725635        996          2 enq: TS - contention
         2        595 1414725635        996          2 enq: TS - contention



看下 这几个是不是parallel slave进程，以及他们运行的SQL


enq: TS - contention在11.2.0.3上还有一些 BUG

ODM FINDING:

SMON blocking Drop Temporary Tablespace (Doc ID 1500044.1)

Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.
Symptoms

The following steps have been performed successfully:

- Create a new temporary tablespace

- Declare as the new default temp

- Make sure that no users have the old temp as their default

Now trying to drop the old temporary tablespace, the session hangs.

Analyzing the problem shows the following:

- Tracing the session using 10046 shows that the session is waiting for event "enq: TS - contention'"

- System state dump shows that the session is blocked by SMON

- SMON is waiting for event "SMON TIMER"


Cause

This seems to be caused by Bug 13028161: DROP TEMP TABLESPACE HANGS DUE TO TS ENQUEUE CONTENTION, still being worked by the Development Team.

Solution

The following workaround could be used until a permanent fix for the bug is released
- Complete all the needed prerequisites (create the new temp tablespace and set as default and no user is assigned to the old tablespace)
- Bounce the DB
- Once the DB starts up disconnect from SQLPLUS completely and close the console
- Open a new console and launch SQLPLUS
- Run the drop command as the VERY FIRST COMMAND
In case of temporary tablespace groups being used, create a new temporary tablespace Group and drop the old temporary tablespace group.

weiranth

NOWAIT lock requests could hang (like Parallel Queries may hang "enq: TS - contention") on a RAC environment..

djmilo

方便的话上传一份有针对性的AWR，大家一起来看看。