With your SID go to v$transaction take a look
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*
-- regards,