This content has been marked as final. Show 10 replies
I set up Oracle 10.2.0.3 on 64-bit Linux and tried to reproduce this hang without success. Without the actual test case, I created a procedure that has an infinite loop inside like the following instead:
create or replace procedure ploop is
i number := 0;
i := i + 1;
exit when i < 0;
I am able to run the procedure, suspend it at will (by hitting the yellow "suspend' button inside the debugger), change the loop variable to a negative value from the debugger to break out from the loop.
In your case, are you able to suspend your hanging application from the debugger? Please note that the debugger can suspend freely inside PL/SQL execution but not in a long-running SQL statement.
Also, to isolate the issue, does your application also hang when run outside of the debugger?
BTW, to set the record straight, those "TCP Socket (KGAS)" events only indicate that the server is waiting on the input from the debugger over the TCP debug connection, which can happen when the application is suspended and is waiting for debug commands from the debugger and may not indicate a real issue.
Thank you so much for taking interest.
I tried several things today:
The debugger behaves as expected, able to run freely, break, continue, all without hanging.
The "TCP Socket (KGAS)" event is still there, but the resources spent has reduced drastically - less than scattered and sequential reads. I remember it was the other way around.
On breaking execution, the time waited on "TCP Socket (KGAS)" increases accordingly (as expected), and the total waits stay the same.
On resuming, the time waited on "TCP Socket (KGAS)" and the total waits increase little by little.
What I remember out of the ordinary was a huge number of total waits on the event (millions instead of thousands), eventually probably choking up the session.
Don't know what the reason was for it happening (DB or sqldev), but I can't get it to reproduce anymore. The only thing changed over the months is the package's code and the version of sqldev. It might be that I wrote really bad code for debugging, and fixed it without knowing (although unlikely ;-P), or sqldev handles debugging somewhat differently now.
In any case, the problems seem to be gone. I'll definitely keep an eye out to see if it happens again.