This content has been marked as final. Show 18 replies
Thanks a lot for your effort and tips. I hope we have found the right scenario:
(finally I have the simplest model to simulate the problem: I have created two procedures, first writes "I'm running", then waits 70 minutes - thanks to Marcelo Ochoa, Buenos Aires - then writes "Finished". Second procedure just writes "Gone". Next, I prepare script which executes this two procedures. And result: first one is finished, but the second not.)
sqlplus runs the script, so it sends to server "exec proc1". While server executes proc1 server keernel decides to close tcp session in state "idle" over 3600 seconds between server and client. Oracle finished proc1 and tries to send result back to client, but tcp is over. client (sqlplus) expects result, but gots nothing and waits - hangs.
Does this sound reasonable?
> sqlplus runs the script, so it sends to server "exec proc1". While server executes proc1 server
keernel decides to close tcp session in state "idle" over 3600 seconds between server and client.
This does not sound correct. Typically what should happen:
1. client makes connect to oracle
2. a V$SESSION entry is created for the session in Oracle
3. after the connection, this sesion is idle and wait state "SQL*Net message from client" (Oracle server is waiting for the client to give it work to do)
4. client sends a command (e.g. SQL*Plus exec proc1)
5. the session is now ACTIVE as it is doing stuff
6. the session's events and wait state will show what this proc1 execution is doing and what the session is waiting for (e.g. waiting for I/O, waiting on a latch, etc)
7. the client waits.. (it does not "hang" - it is merely waiting for a response from Oracle)
8. when the session has completed servicing that requests it sends the a completed return code to the session (and the session goes idle)
9. the client now "wakes up" and starts to send "give me data" (fetch) commands to the session to get the result if there are any, as is the case with a SQL SELECT command (for a stored proc exec, there is nothing for the client to fetch)
If there is an error along the way in the network connection, the Oracle session will realise that in step 8 - when it attempts to inform the client that the request has been completed. The session will see that the network connection to the client is gone. It will rollback and terminate.
If the Oracle session crashed (internal error) during step 5, the client will only realise that when it "checks" its network connection to Oracle, or when it attempts to re-use that connection to Oracle to provide the session with new work to do. In this case, the client's OCI driver will return an ORA-03113: end-of-file on communication channel.
What you need to determine is
a) does the Oracle session crash?
b) if not, what is it doing? (looking at V$SESSION, V$SESSION_EVENT and V$SESSION_WAIT)
Monitoring the client in this case is of little use - as it is waiting on the Oracle server session to service its request.
If you determine that the Oracle session is idle and it says "SQL*Net message from client" , then it is waiting on the client. In this case you need to look at the client and determine why it is hanging - as the Oracle sever session saying that it has serviced the client and it is now idle, waiting for the next set of instructions from the client.
Jacek, please do not resurrect old threads (this one dates to 2008) and then "+hijack+" it to deal with your problem.
It is appreciated that you went to the effort of researching your problem and discovering an old thread that deals with a similar one. But instead of adding to it, rather start a brand new thread (that ensures that everyone in the forum sees it) and refer to the old thread in your posting.
That way, you will get a better "service". Very few will take notice of an old thread and provide assistance.