10 Replies Latest reply: Jun 1, 2007 5:24 AM by -K- RSS

    TCP Socket (KGAS)?

    -K-
      I'm not sure posting here is the right place, but since it happens using SQL Developer...

      While debugging a stored procedure, network use on the database goes waaaaay up to taking up more than 3/4 of the resources. Analyzing the wait events of the debugging session, almost all the time waited is for "TCP Socket (KGAS)".
      After running the procedure for about a minute, the session hangs in one of the "TCP Socket (KGAS)" events, counting eternally the "Seconds in wait", not advancing anymore. If during this minute I hit a breakpoint and debug step by step, the minute-to-hang gets paused also. So after about a minute of summed running time, the session hangs.
      SQL Dev isn't aware of this hung session, and appears to continue running the procedure (only that it's not advancing). The only thing I can do is kill the session on the database OS (killing through the database won't free the acquired locks and stuff). This wakes up SQL Dev instantly (saying the session has been killed), so I can start another debug run.
      And so on...
      Debugging big slow procedures is really no fun like this. I constantly have to monitor the wait events and if there's a "TCP Socket (KGAS)" that takes more than 5 seconds, I know the session is lost in socketland.

      Does anyone know something about the "TCP Socket (KGAS)" wait event? Did anyone notice the same behaviour? Does anyone has a suggestion for more solid debugging? Anything?

      Hoping for some kind of magical solution proposed by some kind of Oracle/networking guru,
      K.
        • 1. Re: TCP Socket (KGAS)?
          SueHarper
          Looking into this.

          Sue
          • 2. Re: TCP Socket (KGAS)?
            -K-
            It started happening when upgraded to 10.2 on Linux 64 bit, connecting from sqldev on Win2000/XP. Judging from the little response, I guess it doesn't happen in other versions/platforms...

            Thanks!
            K.

            Message was edited by: K.
            • 3. Re: TCP Socket (KGAS)?
              Kris Rice-Oracle
              If this is still happening, can you post a reproducible case here so I can send it on to the right team?

              -kris
              • 4. Re: TCP Socket (KGAS)?
                -K-
                Hi Kris,

                All cases reproduce this. Putting a breakpoint at the very first line of a package is all it needs.

                Regards,
                K.

                (SQLDev 1.1.2 - Oracle 10g Release 10.2.0.2.0 - 64bit Production on Linux)
                • 5. Re: TCP Socket (KGAS)?
                  andriy
                  • 6. Re: TCP Socket (KGAS)?
                    -K-
                    Cool... and not so cool.
                    Cool to know it shouldn't affect performance, not so cool I don't have any other clue as to why I lost the debug connection so often...

                    Will keep the threat updated when I get back to extensive debugging and it still happens.

                    Thanks,
                    K.
                    • 7. Re: TCP Socket (KGAS)?
                      rpang
                      Hi K

                      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;
                      begin
                      loop
                      i := i + 1;
                      exit when i < 0;
                      end loop;
                      end;
                      /

                      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.

                      Thanks.

                      Rob
                      • 8. Re: TCP Socket (KGAS)?
                        -K-
                        Hi Rob,

                        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.

                        Thanks again,
                        K.
                        • 9. Re: TCP Socket (KGAS)?
                          SueHarper
                          Just to update. This bug was passed on to the Server Tech team. As neither you nor the ST folk can reproduce this, we have closed the bug. Please reopen a database bug if you encounter this in the future.

                          Thanks
                          Sue
                          • 10. Re: TCP Socket (KGAS)?
                            -K-
                            Sure, thanks,

                            K.