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
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
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.
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.
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.
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 --
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.
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 ?
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.ipv4.tcp_wmem can also make a difference.
Any suggestions on this.
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.