Weird WAIT(ENQ) waits iin v$shared_server.status after server crash

From: NetComrade <netcomradeNSPAM_at_bookexchange.net>
Date: Tue, 25 Oct 2005 17:10:21 GMT
Message-ID: <435e641b.1297847015@localhost>

We had a server crash last night, and as the db came up, I was seeing these in the status column of v$shared_server.

These would usually indicate a session waiting for a lock, but I would see nothing unusual in v$lock (the majority were MR locks per file, there were few others, but I have not found any 'blocking' locks by using the query below ) The locks kept creeping up and taking over more and more servers, but eventually they went away. Any query on v$session_wait would hang, so I couldn't gather more info.

I didn't run statspack either.. Any idea on the potential cause, based on this limited information?

Query to determine 'blocking' locks:

select

        b.sid,
        decode(b.type,


                'MR', 'Media Recovery',

                'RT', 'Redo Thread',

                'UN', 'User Name',

                'TX', 'Transaction',

                'TM', 'DML',

                'UL', 'PL/SQL User Lock',

                'DX', 'Distributed Xaction',

                'CF', 'Control File',

                'IS', 'Instance State',

                'FS', 'File Set',

                'IR', 'Instance Recovery',

                'ST', 'Disk Space Transaction',

                'TS', 'Temp Segment',

                'IV', 'Library Cache Invalidation',

                'LS', 'Log Start or Switch',

                'RW', 'Row Wait',

                'SQ', 'Sequence Number',

                'TE', 'Extend Table',

                'TT', 'Temp Table',

                b.type) lock_type,
        decode(b.lmode,
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* NOT */
                2, 'Row-SELECT (SS)',     /* LIKE */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* SELECT */
                5, 'SELECT/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */
                to_char(b.lmode)) mode_held,
         decode(b.request,
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* NOT */
                2, 'Row-SELECT (SS)',     /* LIKE */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* SELECT */
                5, 'SELECT/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */
                to_char(b.request)) mode_requested

from sys.v_$lock b
where request != 0

        OR block != 0
order by sid
.......
We use Oracle 8.1.7.4 and 9.2.0.6/7 on Solaris 2.7 and RH4 remove NSPAM to email Received on Tue Oct 25 2005 - 12:10:21 CDT