Can someone explain this, pl?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Can someone explain this, pl?

  1. #1
    Join Date
    Aug 2000
    Posts
    194
    I have the following situation. I have two session 10, 40 having the same OS process ID (24905). Session 40 is holding lock on a resource and other sessions are waiting for it.

    On tracing the session 40 (spid= 24905), I get the following lines.

    WAIT #1: nam='enqueue' ela= 16 p1=1415053316 p2=196628 p3=1397
    WAIT #1: nam='enqueue' ela= 307 p1=1415053316 p2=196628 p3=1397


    select s.sid sid, p.spid from v$session s, v$process p where s.sid in (10,40) and s.paddr = p.addr ;

    SID SPID
    ---- ------
    10 24905
    40 24905

    select * from v$session ;

    SADDR SID SERIAL# AUDSID PADDR COMMAND OWNERID TADDR LOCKWAIT STATUS SERVER PROCESS SQL_ADDR
    -------- --- ------- ------ -------- ------- ---------- -------- -------- -------- --------- ------- --------
    580A7DBC 10 13499 329589 5808C554 2 2147483644 586B9554 5812C3EC ACTIVE DEDICATED 8132 589186E8

    580B753C 40 56156 329590 5808C554 0 2147483644 586BB82C INACTIVE DEDICATED 8132 58979DA8


    On tracing the parent process (Apache, it seems it is waiting on Oracle). But Quesry on Oracle (V$session_wait) shows its waiting for the response from the client.

    Now my main question is,

    Can anyone tell me, when does more than one session (SIDs) have the same OS process id?

    How to find who is actually waiting for who? (from the trace Oradebug and os
    process trace it looks to me Oracle is waiting for the Apache/Client process
    and Apache is waiting for the Oracle Process)

    Any suggestion of you will be greatly appreciated.

    Thanks,

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    It is possible in the MTS setting.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I don't think it is related to MTS, as the v$session.server was DEDICATED.

    What process was running at PID 24905?

    To help you diagnose your enqueue problem, look at [url]http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=102925.1[/url]
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Aug 2000
    Posts
    194
    The process for 24905 is an "oracle" process.
    (from ps -ef: oracle 24905 1 0 Feb28 ? 00:00:01 oracleORCL oracleORCL (DESCRIPTION=(LOCAL=n

    Can someone tell me whether the following could be a cause and could it be possible? (Just my theory).

    There may be a client process, which has two connection open at the same time.
    The first connection holds an exclusive row lock (from v$sqlarea, its an insert stmt) and the other connection is holding exclusive lock on another table and wants the lock on the former's table.
    Since its within the same process, oracle may not be able to detect it as a time out/dead lock. (These processes are waiting for more than a day)

    Thank you for any of your feedback.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width