RE: Help : session stuck with library cache

From: Polarski, Bernard <Bernard.Polarski_at_atosorigin.com>
Date: Tue, 3 Oct 2006 11:11:52 +0200
Message-ID: <25D4919915CCF742A88EE3366D6D913D0FB9F98C@mailserver1>

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_at_gmail.com] 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_at_atosorigin.com> 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 

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 03 2006 - 04:11:52 CDT