TSI_DB_SK

1 Sessions waiting >1000 for enq
haccbdb (ccbdb)

Tue Feb 24 10:49:08 2009
Starting control autobackup
Control autobackup written to SBT_TAPE device
        comment 'API Version 2.0,MMS Version 4.1.0.0',
        media '500361'
        handle 'c-2997659783-20090224-0b'
Tue Feb 24 10:52:32 2009
Restarting dead background process QMN0
QMN0 started with pid=719, OS id=11715
Tue Feb 24 10:52:32 2009
Restarting dead background process QMN1
QMN1 started with pid=748, OS id=11719
Tue Feb 24 10:57:35 2009
Restarting dead background process QMN0
QMN0 started with pid=1137, OS id=15911
Tue Feb 24 10:57:35 2009
Restarting dead background process QMN1
QMN1 started with pid=1138, OS id=15913
Tue Feb 24 10:59:34 2009
Thread 1 advanced to log sequence 350627
  Current log# 1 seq# 350627 mem# 0: /dev/vx/rdsk/oracledg/t3_1_l1_redo_3_001
  Current log# 1 seq# 350627 mem# 1: /dev/vx/rdsk/oracledg/t3_2_l1_redo_3_001
Tue Feb 24 10:59:34 2009
ARC0: Evaluating archive   log 4 thread 1 sequence 350626
ARC0: Beginning to archive log 4 thread 1 sequence 350626
Creating archive destination LOG_ARCHIVE_DEST_1: '/svc/app/oracle/admin/ccbaq/arch/ccbaq_0000350626.arc'
Tue Feb 24 11:00:03 2009
ARC1: Evaluating archive   log 4 thread 1 sequence 350626
ARC1: Unable to archive log 4 thread 1 sequence 350626
      Log actively being archived by another process
Tue Feb 24 11:00:27 2009
ARC0: Completed archiving  log 4 thread 1 sequence 350626
Tue Feb 24 11:03:24 2009
Restarting dead background process QMN0
QMN0 started with pid=586, OS id=28763
Tue Feb 24 11:03:25 2009
Restarting dead background process QMN1
QMN1 started with pid=1053, OS id=28766
Tue Feb 24 11:08:25 2009
Restarting dead background process QMN0
QMN0 started with pid=142, OS id=2929
Tue Feb 24 11:08:25 2009
Restarting dead background process QMN1
QMN1 started with pid=115, OS id=2941

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

AQ_TM_PROCESSES should be set to 0 , if you are not using Advanced Queuing.
Also it seems taking the EXAMPLE tablespace OFFLINE and back again to ONLINE gets rid of this !

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

SQL> show parameter aq

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     2

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

AQ_TM_PROCESSES controls time monitoring on queue messages and controls processing of messages with
delay and expiration properties specified.You do not need to specify a value for this parameter
because Oracle Database automatically determines the number of processes and autotunes them,
as necessary. Therefore, Oracle highly recommends that you leave the AQ_TM_PROCESSES parameter
unspecified and let the system autotune.

If you want to disable the Queue Monitor Coordinator, then you must set AQ_TM_PROCESSES = 0
in your pfile or spfile. Oracle strongly recommends that you do NOT set AQ_TM_PROCESSES = 0.
If you are using Oracle Streams, then setting this parameter to zero (which Oracle Database respects no matter what)
can cause serious problems.

------------
Na fore bolo:
I would NOT recommend setting this value to 2. We saw drastic issues when we had this value set.

This can be considered a noise error, nothing wrong, just annoying.

-----------
Susen sa nato pozrie:
You should not set aq_tm_processes to 0.
I'll look at the problem.
-----------

V tomto pripade je nutne urobet toto:
1. select count(*)  || ' Sessions waiting >1000  for enq ' ANz
 from v$session_wait
 where event like 'enq%' and SECONDS_IN_WAIT >1000;
ANZ
-------------------------------------------------------------------------
0 Sessions waiting >1000  for enq

-Ak je 0 potom je to OK
2. ccbdb2 ccbaq /svc/home/oracle $ ps -ef|grep ora_qmn* |grep -v grep;date
  oracle 25132     1  0   Feb 24 ?       5479:23 ora_qmn0_ccbaq
  oracle 25136     1  2   Feb 24 ?       5409:41 ora_qmn1_ccbaq
-ak su viac alebo menej ako 2 tak volat.

//////////

Ak je napr. 1 session waiting potom : (F. Spinler)

The enqueue was cause by a lock from another session on object 318 ( sys.result$):

 select * from v$session_wait where event like 'enq%' and SECONDS_IN_WAIT >1000;
       SID       SEQ# EVENT
---------- ---------- ----------------------------------------------------------------
P1TEXT                                                                   P1 P1RAW
---------------------------------------------------------------- ---------- ----------------
P2TEXT                                                                   P2 P2RAW
---------------------------------------------------------------- ---------- ----------------
P3TEXT                                                                   P3 P3RAW             WAIT_TIME SECONDS_IN_WAIT
---------------------------------------------------------------- ---------- ---------------- ---------- ---------------
STATE
-------------------
      2199      12788 enqueue
name|mode                                                        1146617860 0000000044580004
id1                                                                     318 000000000000013E
id2                                                                       0 00                        0            10460         
WAITING

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
BLOCKING_STATUS
------------------------------------------------------------------------------------------------------------------------
CCMS_DATA@wsp-app1 ( SID=1500 )  is blocking AQ_ADMIN@wsp-app1 ( SID=2199 )

select * from v$enqueue_lock where sid=2199
  2  ;
ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000060D3CAC20 000000060D3CAC40       2199 DX        318          0          0          4      10460          0

select * from v$enqueue_lock where id1=318
ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000060D3B4890 000000060D3B48B0          2 MR        318          0          4          0    4552884          0
000000060D3B2528 000000060D3B2548       1500 DX        318          0          6          0      10569          1
000000060D3CAC20 000000060D3CAC40       2199 DX        318          0          0          4      10569          0
Please see, where Block =1:  This is the blocking session ob object 318.

SQL> select * from v$session where sid=1500;
SADDR                   SID    SERIAL#     AUDSID PADDR                 USER# USERNAME                          COMMAND
---------------- ---------- ---------- ---------- ---------------- ---------- ------------------------------ ----------
   OWNERID TADDR            LOCKWAIT         STATUS   SERVER       SCHEMA# SCHEMANAME
---------- ---------------- ---------------- -------- --------- ---------- ------------------------------
OSUSER                         PROCESS      MACHINE
------------------------------ ------------ ----------------------------------------------------------------
TERMINAL                       PROGRAM                                          TYPE       SQL_ADDRESS
------------------------------ ------------------------------------------------ ---------- ----------------
SQL_HASH_VALUE PREV_SQL_ADDR    PREV_HASH_VALUE MODULE                                           MODULE_HASH
-------------- ---------------- --------------- ------------------------------------------------ -----------
ACTION                           ACTION_HASH CLIENT_INFO
-------------------------------- ----------- ----------------------------------------------------------------
FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIME          LAST_CALL_ET PDM
-------------------- ------------- -------------- --------------- ------------- ------------------- ------------ ---
FAILOVER_TYPE FAILOVER_M FAI RESOURCE_CONSUMER_GROUP          PDML_STA PDDL_STA PQ_STATU CURRENT_QUEUE_DURATION
------------- ---------- --- -------------------------------- -------- -------- -------- ----------------------
CLIENT_IDENTIFIER
----------------------------------------------------------------
000000060CD5EE40       1500      37584  276743632 000000060C67C6B8        124 CCMS_DATA                               0
2147483644 00000006196F3818                  KILLED   DEDICATED        124 CCMS_DATA
                               1234         wsp-app1
                                                                                USER       00000006477E6590
    3075099269 00000006477E6590      3075099269                                                            0
                                           0
            25940932        158200            845               0             0 2009-03-27 08:10:50        10522 NO
NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0



I looked what SQL was done by this session, and it seems that it was not very important.

So there was the only chance to kill the session.
I did this and also killed the belonging Unix-Pid.
Then the enqueue was resolved:

SQL>  select count(*)  || ' Sessions waiting >1000  for enq ' ANz
 from v$session_wait
 where event like 'enq%' and SECONDS_IN_WAIT >1000;  2    3
ANZ
-------------------------------------------------------------------------
0 Sessions waiting >1000  for enq

Now you can close this Incident.


//////////// od erica:

ccbdb2 ccbaq /svc/app/oracle/admin/ccbaq/scripts/check $ s+

SQL*Plus: Release 9.2.0.8.0 - Production on Wed May 6 00:47:47 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> @Holder.sql

SESS                                                    ID1        ID2      LMODE    REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 208                                               3          0          6          0 DX

SQL> alter system kill session '208,41282';
alter system kill session '208,41282'
*
ERROR at line 1:
ORA-00031: session marked for kill


SQL>
SQL> SELECT p.spid,s.sid, s.serial#, s.program, s.username, s.status
FROM v$process p, v$session s
WHERE p.addr = s.paddr
and s.sid = 208
  2    3    4    5  ;

SPID                SID    SERIAL# PROGRAM                                          USERNAME
------------ ---------- ---------- ------------------------------------------------ ------------------------------
STATUS
--------
27336               208      41282                                                  CCMS_DATA
KILLED/


SQL> select * from v$lock where block > 0;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000060D3CD060 000000060D3CD080        208 DX          3          0          6          0      19606          1



ccbdb2 ccbaq /svc/app/oracle/admin/ccbaq/scripts/check $ kill -9 27336
ccbdb2 ccbaq /svc/app/oracle/admin/ccbaq/scripts/check $