How to track down session waiting?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to track down session waiting?

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Posts
    257

    How to track down session waiting?

    I have a java program that process a file and insert data into the db. It's been working fine until recently when we have a new file with larger number of rows to insert (not sure if this is an issue). I am sure the program commits if it can reach the end of transaction so 'forgetting to commit' is not the case here. V$session shows TADDR with a value while LOCK_WAIT is NULL. The associated SQL for the session is an INSERT statement. There is NO CPU usage on the server while it's waiting.

    When I query v$session_wait, I get:

    SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
    38 18222 SQL*Net message from client driver id 675365956 28414444 #bytes 1 00000001 0 00 -1 10625 WAITED KNOWN TIME

    After some researches, my intepretation of this is Oracle server is waiting for the client to pass something. The SECONDS_IN_WAIT value keeps increasing. Is this the source of the problem? I just don't understand, if this is the case, what is it waiting for, because for other files, it does not have to wait?

    Could someone give some suggestions as to how to troubleshoot this kind of problem?

    Thanks,
    Last edited by a128; 06-15-2004 at 11:51 PM.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    SQL*Net message from client is not usually an active wait, it's more of an idle event which means your session is idle

  3. #3
    Join Date
    Apr 2001
    Posts
    257
    But then what does it mean when V$SESSION shows a value in TADDR and the SQL statement selected from joining V$SESSION and V$SQLTEXT shows it's an INSERT statement? There is no CPU activities and it seems the transaction can not finish because it's waiting for something. But V$SESSION_WAIT shows only the "SQL*Net message from client" for the session.

    Any helps are appreciated!

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: How to track down session waiting?

    Originally posted by a128

    Could someone give some suggestions as to how to troubleshoot this kind of problem?
    trace the session, look at the log file.
    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."

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well if you dont commit then of course you see an entry in v$transaction or TADDR in v$session

    if I do

    insert into x values(1);

    then stop there if I look v$session_wait from another session I see this SQLNET wait for my session. Idle event

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