-
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 10:51 PM.
-
SQL*Net message from client is not usually an active wait, it's more of an idle event which means your session is idle
-
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!
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|