Help : session stuck with library cache

From: Polarski, Bernard <Bernard.Polarski_at_atosorigin.com>
Date: Tue, 3 Oct 2006 10:45:40 +0200
Message-ID: <25D4919915CCF742A88EE3366D6D913D0D56871A@mailserver1>

I have a connection in perl stuck with latch free latch on 'library cache' latch# 106:

 Sid       SEQ# Event type                       waits  Time waited
P1         P2         P3 State


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

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

   26       7823 latch free                           0             0
1.7199E+10        106          1 WAITING
   60      13128 db file sequential read              0             0
64     151890          1 WAITING
   25         38 SQL*Net message to client           -1             0
1650815232          1          0 WAITED SHORT TIME

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production JServer Release 8.1.7.4.0 - Production

SQL> select name from v$latchname where latch#=106 ; NAME

library cache

Both SQL_HASH_VALUE and PREV_HASH_VALUE are filled in v$session :

SQL Hash value Sql
---- -----------

Curr 4174678299 select to_char(hash_value) hv, to_char(piece) pi, sql_text st from v$sqltext

                                  where hash_value=:hv order by piece

Prev 4174678299 select to_char(hash_value) hv, to_char(piece) pi, sql_text st from v$sqltext

                                  where hash_value=:hv order by piece

In v$latchholder I got this :

SID Latch Address Latch name
--- --------------- -----------

26 000000040123AB50 library cache:

I am a bit stuck, as I can't determine what's happening. The rest of the DB is advancing. This is on a SAP system with lot of parsing.

Any idea where to search? I would like to determine why session is not performing the above query or if this query is stuck somewhere.

Regards,

B. Polarski
Oracle DBA

--

http://www.freelists.org/webpage/oracle-l Received on Tue Oct 03 2006 - 03:45:40 CDT