This discussion is archived
6 Replies Latest reply: Aug 6, 2012 6:16 AM by BillyVerreynne RSS

TCP socket(KGAS) wait event

830567 Newbie
Currently Being Moderated
hello

TCP socket(KGAS) wait event, it creates 20% wait, is there any way to reduce, this happend when we use UTL_SMTP package.

tks
  • 1. Re: TCP socket(KGAS) wait event
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    usr080678 wrote:

    TCP socket(KGAS) wait event, it creates 20% wait, is there any way to reduce, this happend when we use UTL_SMTP package.
    This indicates that an Oracle process, as a TCP client in a network client-server conversation, is waiting on a response from the server. That could be due to the TCP server being slow. Could be due to network congestion. Could be due to a poorly configured network (e.g. NIC running half duplex). Etc.

    It is NOT an Oracle RAC or Oracle database issue. It is not the Oracle process that is the problem - the problem is Oracle processes having to wait to be serviced by that TCP server (in your case, whatever mail server is used).
  • 2. Re: TCP socket(KGAS) wait event
    830567 Newbie
    Currently Being Moderated
    hi, thanks for your reply, i did SMTP server response test manually by using perl script, 0.003 seconds test email were delivered even with 6MB attachment, that's why am looking from oracle stake to address.

    tc
  • 3. Re: TCP socket(KGAS) wait event
    onedbguru Pro
    Currently Being Moderated
    How many emails are being sent that it takes up 20% processing time? Is this a recent development? Or recent change?

    Can you create a quick test from that production system and execute if interactively?
  • 4. Re: TCP socket(KGAS) wait event
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    usr080678 wrote:
    hi, thanks for your reply, i did SMTP server response test manually by using perl script, 0.003 seconds test email were delivered even with 6MB attachment, that's why am looking from oracle stake to address.
    You are looking at the wrong software. TCP sockets are TCP socket. Oracle uses the exact same client TCP socket interface from UTL_TCP (on which UTL_SMTP runs), than what Perl uses.

    Also, network infrastructure can, and often do, result in inconsistent behaviour. A client-server test can work fine now. The same test in an hour's time can fail due to network congestion, and other factors. I have seen this numerous time - and as recent as last week.

    Oracle is not too blame for the wait state you are seeing. It simply says that a synchronous socket call was made by that Oracle process - and that the process is still waiting for the protocol stack to complete and return the result of that call.

    If the client network connection to the server fails due to some kind of error, and the client IP stack does not receive a TCP packet for that connection with the FIN or RST bit set, the client will NOT know that the communication pipe has failed.
  • 5. Re: TCP socket(KGAS) wait event
    830567 Newbie
    Currently Being Moderated
    thanks for you all reply

    below is wait event and tuning recommendation, Actually SMTP Server response is very fast and tested, i need to explain this to application team if they have to correct their code



    RECOMMENDATION 1: Application Analysis, 99% benefit (7003 seconds)
    ACTION: Investigate the cause for high "TCP Socket (KGAS)" waits. Refer
    to Oracle's "Database Reference" for the description of this wait
    event. Use given SQL for further investigation.
    RATIONALE: The SQL statement with SQL_ID "anwkukvsrg26k" was found
    waiting for "TCP Socket (KGAS)" wait event.
    RELEVANT OBJECT: SQL statement with SQL_ID anwkukvsrg26k
    DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
    broken BOOLEAN := FALSE; BEGIN
    Carrier_Master_Syncho_DB.ib_location_syncho('MSQ'); :mydate :=
    next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
    RATIONALE: The SQL statement with SQL_ID "66yva2k31qknb" was found
    waiting for "TCP Socket (KGAS)" wait event.
    RELEVANT OBJECT: SQL statement with SQL_ID 66yva2k31qknb
    DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
    broken BOOLEAN := FALSE; BEGIN
    product_Master_Syncho_DB.ib_vessel_syncho('MSQ'); :mydate :=
    next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

    RECOMMENDATION 2: Application Analysis, 99% benefit (7003 seconds)
    ACTION: Investigate the cause for high "TCP Socket (KGAS)" waits in
    Service "SYS$USERS".
  • 6. Re: TCP socket(KGAS) wait event
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    usr080678 wrote:

    Actually SMTP Server response is very fast and tested,
    No it is not. The PL/SQL code using client socket connection very clearly states that the code is waiting on server TCP responses.

    And you need to determine where in the code this happens (did you verify what TCP connections this code makes) - and you need to test it within the very same o/s environment that the Oracle PL/SQL code is executed in.
    i need to explain this to application team if they have to correct their code
    From Oracle® Database Reference guide:
    >
    TCP Socket (KGAS)

    A session is waiting for an external host to provide requested data over a network socket. The time that this wait event tracks does not indicate a problem, and even a long wait time is not a reason to contact Oracle Support. It naturally takes time for data to flow between hosts over a network, and for the remote aspect of an application to process any request made to it. An application that communicates with a remote host must wait until the data it will read has arrived. In addition, on Microsoft Windows, a separate thread monitors the arrival of traffic. This thread spends most of its life in waits tracked by the TCP Socket (KGAS) wait event.

    Wait Time: The total elapsed time for the network connection to be established or for data to arrive from over the network

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points