This discussion is archived
11 Replies Latest reply: Oct 9, 2013 5:50 AM by Jonathan Lewis RSS

SQL*Net more data from client wait event

Fahim5 Newbie
Currently Being Moderated

Hi all,

DB 11.2.0.2

AIX 6

 

I am getting following two top wait events from AWR report

1)SQL*Net more data from client

2)log file sync

 

Does it hints towards network latency and hardware configuration?

what should i do for first wait event?

  • 1. Re: SQL*Net more data from client wait event
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    1) Maybe each "row" inserted by the client is too large to fit into 1 packet.

     

    2) Maybe the LGWR process is unable to get onto the CPU frequently enough  OR   the log writes are too slow  (then look at the background wait events to see if log writes are slow)

     

    How much *relative* time is lost on these two wait events ?

     

    Hemant K Chitale


  • 2. Re: SQL*Net more data from client wait event
    Alvaro Pro
    Currently Being Moderated

    1) Usually points to network latency issues. The user's background shadow process is waiting for the user foreground process over the network to receive the last send. It is wating for the last send to complete while still having to deliver more data, it raises this event. I would start with an investigation regarding network latency between the DB and the client host.

     

    2) Either application is commiting too frequently or you have an slow I/O disk hosting your redo log files. Check MOSC note 1376916.1

     

    Regards

  • 3. Re: SQL*Net more data from client wait event
    Fahim5 Newbie
    Currently Being Moderated

    1)For the log sync wait the application they are using have too many commits for each and every inserts i have told them to commit in between some records for procedure( Is there any other way?)

     

    2)SQL*Net more data from client this wait event is taking 77% of the db time

    Since i dont have the direct access to the system i have told to check the network bandwidth and other network issues.

    In detween

    What will be the impact of mentioning SDU  units in tnsnames and listener file, does it makes sense.What sould be done so that most of the data from the client passes in one call and in same packets.

  • 4. Re: SQL*Net more data from client wait event
    Jan-Marten Spit Explorer
    Currently Being Moderated

    "SQL*Net more data from client" may point to network, but could also be a consequence of the application having trouble delivering the data as fast as oracle (and the network) can process it.

     

    impossible to say with the data than you provided.

  • 5. Re: SQL*Net more data from client wait event
    Alvaro Pro
    Currently Being Moderated

    Setting the SDU sizes to the maximum sizes has no negative impact as far as I can tell. However, it will only help if indeed the issue is the application generating large messages to the DB which would cause more SDU to be used for a single message. Does this issue affect several statements or just one?

     

    By the way SDU size is set on sqlnet.ora on both the DB and client.

  • 6. Re: SQL*Net more data from client wait event
    Mark D Powell Guru
    Currently Being Moderated

    The "SQL*Net more data from client" can also just be the result of how the application is designed especially if connection pooling is used and more connections exist than the application user load can make consistent use of.  You have idle connections and Oracle is waiting on a request to do something.

     

    The wait on log file sync is more likely of interest though it may also not really be of interest since no information on the work load, time of sample, etc ... is included in the initial post which mean all the answers given so far may not apply to the actual situation.  In general you reduce log file sync waits by committing as infrequently as practical.   Concurrent demand for the same rows is what usually determines how frequently an update process needs to commit.

     

    HTH -- Mark D Powell --


  • 7. Re: SQL*Net more data from client wait event
    jgarry Guru
    Currently Being Moderated

    SQL*Net message to client vs SQL*Net more data to client | Tanel Poders blog seems to say that is really just measuring the time to get the data from the packet to the OS, rather than over the net.

  • 8. Re: SQL*Net more data from client wait event
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    Something has to be top of the list.

    There's no point in trying to answer your question until we have a better idea of the scale.

    At least show us the whole top 5 for a standard 1 hour interval

     

    Regards

    Jonathan Lewis


  • 9. Re: SQL*Net more data from client wait event
    Alvaro Pro
    Currently Being Moderated

    Hi,

     

    Indeed I came across that post by Mr. Poder. However, kindly look at this: SQL*Net more data to client (%)

     

    Docs seem to imply it is wait time between the user shadow process on the server sending and the foreground client server acknowledging the receive, i.e. "The Shadow process waits for the client to receive the last send".

     

    Furthermore, it lists further down on possible user actions, investigate Network Latency, which further implies that the wait indeed includes TCP latency.

     

    What do you make of it ?

  • 10. Re: SQL*Net more data from client wait event
    Fahim5 Newbie
    Currently Being Moderated

    Thanks all for your replies.

    Unfortunatlly i dont have the access to the server.All i get from them is these 2 top wait events message and the SQL*Net more data from client contributing 77% of the db time. They have asked only suggestion from us.And after this they will check in their environment as what to do.

    I have suggested them to check the network latency  and they have not replied yet.

     

    In between for these types of wait events i found out that

    1) redhat linux we can tune by using "tuned-adm profile latency-performance" package.

    2)Adjusting the tcp buffer values alias kernel parameters for TCP  net.core.rmem_max,net.core.wmem_max,net.ipv4.tcp_rmem
    net.ipv4.tcp_wmem can also make a difference.

     

    Any suggestions on this.

  • 11. Re: SQL*Net more data from client wait event
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    This may simple mean that they are doing massive data loads with a very large array size that the Oracle server is handling very efficiently but necessarily waiting for lots of data to come from the client and large log file parallel writes to complete on commit.  It's possible that setting a large SDU and large tcp transmiat and receive buffers would help if that happens to be what they are doing; similarly, if that's what they're doing, then reducing their array size may also have some effect.

     

    Regards

    Jonathan Lewis

Legend

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