遇到 enq: TS - contention

今天我要做一个SKID translation(不要问我什么是SKID translation).

我用BW9518账号干活。处于习惯，我会监控等待事件

SQL> select inst_id,sid,serial#,event,p1,p2,p3,program from gv$session where username='BW9518';

INST_ID SID SERIAL# EVENT P1 P2 P3 PROGRAM

---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ------------------------------------------------

2 4576 19086 SQL*Net message from client 1413697536 1 0 plsqldev.exe

2 4776 30492 PX Deq: Execution Msg 268632063 1 0 oracle@bdhp4464 (PZ93)

1 4617 34773 enq: TS - contention 1414725636 3 3 sqlplus.exe

1 4645 24446 PX Deq: reap credit 0 0 0 oracle@bdhp4463 (PZ93)

3 4654 25663 PX Deq: reap credit 0 0 0 oracle@bdhp4624 (PZ93)

3 4748 10251 SQL*Net message from client 1413697536 1 0 plsqldev.exe

4 4884 15389 PX Deq: reap credit 0 0 0 oracle@bdhp4627 (PZ93)

7 rows selected

奶奶的，过了不久，一直出现enq: TS -contention等待，ok, 既然是enq 等待，我查询GV$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)

3 ;

STATUS SID INST_ID CTIME ID1 ID2 LMODE REQUEST TYPE

-------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----

Holder: 4931 4 1005810 3 4 3 0 TS

Holder: 4931 2 1005807 3 2 3 0 TS

Waiter: 4680 1 3 3 2 0 4 TS

Waiter: 4769 1 3 3 2 0 4 TS

Waiter: 4617 1 3 3 4 0 4 TS

SQL> select inst_id,sid,username,type,PROCESS,PROGRAM,EVENT from gv$session where sid=4931 and inst_id=2;

INST_ID SID USERNAME TYPE PROCESS PROGRAM EVENT

---------- ---------- ------------------------------ ---------- ------------ ------------------------------------------------ ----------------------------------------------------------------

2 4931 BACKGROUND 17946 oracle@bdhp4464 (SMON)

SQL> select inst_id,sid,username,type,PROCESS,PROGRAM,EVENT from gv$session where sid=4931 and inst_id=4;

INST_ID SID USERNAME TYPE PROCESS PROGRAM EVENT

---------- ---------- ------------------------------ ---------- ------------ ------------------------------------------------ ----------------------------------------------------------------

4 4931 BACKGROUND 8320 oracle@bdhp4627 (SMON) smon timer

最后发现enq 等待罪魁祸首居然是smon， smon是系统监视进程，应该是smon正在清理/回收temp表空间

SQL> select name, parameter1, parameter2, parameter3,wait_class from v$event_name where name ='enq: TS - contention';

NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS

------------------------------ -------------------- -------------------- -------------------- --------------------------------------------

enq: TS - contention name|mode tablespace ID dba Other

parameter2 表示tablespace id,Oracle提供了一个命令

alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1' ，所以这里 ts#=3

好，我手动清理

SQL> alter session set events 'immediate trace name DROP_SEGMENTS level 4';

alter session set events 'immediate trace name DROP_SEGMENTS level 4'

ORA-01031: insufficient privileges

他奶奶的，老子没权限，该死的system dba 太抠门了，不给俺权限，没办法等吧 ，只有等 smon 清理完了 enq: TS -contention也就不在了。

更新----------------------------------------------------------------

今天早上终于拿到一个牛逼 账户

SQL> select inst_id,sid,serial#,event ,p1,p2,p3,program ,status from gv$session where username='BW9518';

INST_ID SID SERIAL# EVENT P1 P2 P3 PROGRAM STATUS

---------- ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ------------------------------------------------ --------

1 4584 60888 SQL*Net message from client 1413697536 1 0 plsqldev.exe INACTIVE

1 4662 57133 PX Deq: Execution Msg 268566527 1 0 oracle@bdhp4463 (PZ99) ACTIVE

1 4687 42262 SQL*Net message from client 1413697536 1 0 plsqldev.exe INACTIVE

1 4863 29426 enq: TS - contention 1414725636 3 4 sqlplus.exe ACTIVE

4 4805 35973 PX Deq: reap credit 0 0 0 oracle@bdhp4627 (PZ99) ACTIVE

3 4624 49517 PX Deq: reap credit 0 0 0 oracle@bdhp4624 (PZ99) ACTIVE

2 4596 10273 PX Deq: reap credit 0 0 0 oracle@bdhp4464 (PZ99) ACTIVE

7 rows selected

SQL> alter session set events 'immediate trace name DROP_SEGMENTS level 4';

Session altered

SQL> select inst_id,sid,serial#,event ,p1,p2,p3,program ,status from gv$session where username='BW9518';

INST_ID SID SERIAL# EVENT P1 P2 P3 PROGRAM STATUS

---------- ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ------------------------------------------------ --------

1 4863 29426 direct path read temp 20023 2771404 1 sqlplus.exe ACTIVE

当执行drop_segments 命令之后， 发现前面的四个僵死进程居然也消失了。

先来讲讲这四个僵尸进程怎么产生的。昨天晚上我要进行SKID translation,开始忘了unusable index,所以 kill 了 session

结果 导致了4个并行的影子进程变成了 僵尸进程(也就是上面program为PZ99的进程)。这几个进程应该占用了temp 表空间，SMON没有把他们释放掉。要等SMON去释放，那得猴年马月了。此处再次证明了 drop segments这个命令的强大之处。

下面贴出Metalink中关于该event的解释

The DROP_SEGMENTS event
~~~~~~~~~~~~~~~~~~~~~~~
 Available from 8.0 onwards.
 
   DESCRIPTION
     Finds all the temporary segments in a tablespace which are not
     currently locked and drops them.
     For the purpose of this event a "temp" segment is defined as a 
     segment (seg$ entry) with TYPE#=3. Sort space in a TEMPORARY
     tablespace does not qualify under this definition as such
     space is managed independently of SEG$ entries.

   PARAMETERS
     level - tablespace number+1. If the value is 2147483647 then
             temp segments in ALL tablespaces are dropped, otherwise, only
             segments in a tablespace whose number is equal to the LEVEL
             specification are dropped.

   NOTES
     This routine does what SMON does in the background, i.e. drops
     temporary segments. It is provided as a manual intervention tool which
     the user may invoke if SMON misses the post and does not get to
     clean the temp segments for another 2 hours. We do not know whether
     missed post is a real possibility or more theoretical situation, so
     we provide this event as an insurance against SMON misbehaviour.

     Under normal operation there is no need to use this event.

     It may be a good idea to 
        alter tablespace <tablespace> coalesce;
     after dropping lots of extents to tidy things up.

 *SQL Session (if you can connect to the database):      
    alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1';

     The TS# can be obtained from v$tablespace view:
     select ts# from v$tablespace where name = '<Tablespace name>';

     Or from SYS.TS$:

     select ts# from sys.ts$ where name = '<Tablespace name>' and online$ != 3;
     
     If ts# is 5, an example of dropping the temporary segments in that tablespace 
     would be:

    alter session set events 'immediate trace name DROP_SEGMENTS level 6';