Browse oracle-l: This Month's Archive • Main Archive Page • Related posts • Previous by Date • Next by Date

RE: Help : session stuck with library cache

From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
To: <knecht.stefan@xxxxxxxxx>,<oracle-l@xxxxxxxxxxxxx>
Date: Tue, 3 Oct 2006 11:11:52 +0200

The problem is that v$sesson_io show no activity, so I set the session
on trace mode and no trace file create since nothing moves :
 
Date              -  Tuesday   03rd October   2006  11:09:25
Username          -  SYS
                                                 User           Hard
Blocks   Consistent Consistent  Physical
 Sid username  osuser    ospid    Com HASH_VALUE Type   Cpu     Parse
Changes   Changes      Gets      Reads    %Hit
---- -------- -------- ---------- --- ---------- ---- -------- -------
--------- ---------- ---------- ---------- ----
  26 SYSTEM   oracle   324799     3   4174678299 USER   564.81      17
4588         20        912        387 92.9
 
 
 
Date              -  Tuesday   03rd October   2006  11:09:26
Username          -  SYS

                                                 User           Hard
Blocks   Consistent Consistent  Physical
 Sid username  osuser    ospid    Com HASH_VALUE Type   Cpu     Parse
Changes   Changes      Gets      Reads    %Hit
---- -------- -------- ---------- --- ---------- ---- -------- -------
--------- ---------- ---------- ---------- ----
  26 SYSTEM   oracle   324799     3   4174678299 USER   564.81      17
4588         20        912        387 92.9


  _____  

From: Stefan Knecht [mailto:knecht.stefan@xxxxxxxxx] 
Sent: Tuesday, 03 October, 2006 10:51 AM
To: Polarski, Bernard
Cc: oracle-l
Subject: Re: Help : session stuck with library cache


Hello Bernard

try setting event 10046 for the session in question - you should then be
able to see in the trace file what is really going on under the hood.

Stefan



On 10/3/06, Polarski, Bernard <Bernard.Polarski@xxxxxxxxxxxxxx> wrote: 

        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

Follow-Ups:

RE: Help : session stuck with library cache
From: Jesse, Rich

RE: Help : session stuck with library cache

Other related posts: