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
Other related posts:
