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,
Re: How to track down session waiting?
Quote:
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.