FAQ
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

Search Discussions

  • Stefan Knecht at Oct 3, 2006 at 8:50 am
    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 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
  • Sinardy Xing at Oct 3, 2006 at 8:51 am
    With your SID go to v$transaction take a look
    On 10/3/06, Polarski, Bernard 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*
    --
    regards,

    Sinardy

    --
    http://www.freelists.org/webpage/oracle-l
  • Polarski, Bernard at Oct 3, 2006 at 9:11 am
    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
    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 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
  • Jesse, Rich at Oct 4, 2006 at 2:29 pm
    Funny, I had this exact same thing happen Monday ( I was off yesterday)
    in a 9.2.0.5 dev DB. Turned out that a dev was trying to recompile a
    package (at my request), but a sniped process had a lock on a dependant
    object. Ended up bouncing the instance when PMON refused to clean it up
    (my main reason for never ever killing a session from within Oracle --
    always kill at the OS level).


    GL!


    Rich


    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Polarski, Bernard
    Sent: Tuesday, October 03, 2006 4:12 AM
    To: knecht.stefan_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: Help : session stuck with library cache

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 3, '06 at 8:45a
activeOct 4, '06 at 2:29p
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase