RAC enq: TS - contention 等待事件
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.01、查询等待事件
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并没有消失，一直存在。
请教高手指点。
谢谢！ 方便的话上传一份有针对性的AWR，大家一起来看看。 NOWAIT lock requests could hang (like Parallel Queries may hang "enq: TS - contention") on a RAC environment.. 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.
谢谢 刘大！
页:
[1]