Systems Engineering and RDBMS

Some SQL *Net wait events

Posted by decipherinfosys on November 9, 2007

We have talked about the waits and queues methodology of tuning in some of our previous posts. You can search for waits on this site and get those posts. There are some wait events though that are not very clearly explained like the “SQL*Net message from client” wait event that appears in Oracle. This wait event is used in both the IDLE as well as NON-IDLE conditions and thus can cause confusion. In order to understand what is the difference between an IDLE vs a NON-IDLE event, consider that you open up a SQL *PLUS session and start tracing that session and then you give a pause of say 1 minute in between typing and executing two commands – that time of 1 minute is a true IDLE event since it just shows that no activity was done by you for that duration. The NON-IDLE wait events are those where the process is waiting for the task to finish before invoking another one.

So, the SQL *Net Message from client can be both an IDLE wait event as well as a NON-IDLE wait event. Understanding how it falls under the IDLE category is pretty straight forward, however the scenarios where it can fall under the NON-IDLE category is a bit confusing. Suppose you have network issues and because of the network latency between the application and the database, there is a time lag between the calls that are made to the database, in that scenario – this will fall into the NON-IDLE category. Likewise, suppose your Java or C++ or C# application program is taking too much time to run and is doing processing logic on the application side before invoking another call to the DB, in that case as well you will see this wait event to have a high number in your session trace. In this case as well it means that the database is not the bottleneck, it is merely waiting on the application to tell it to do something. So, one has to look at the entire process – client application and the database to ascertain whether the wait event falls under the IDLE or the NON-IDLE category.

You will also see other SQL *Net wait events like: “SQL *Net message to client” – this means that the database is sending message to the client but there are waits because of probable network issues. Another one is: “SQL *Net more data from client” which means that the database is waiting on the read operation from the client – the call has been initiated but it is taking a long time to get the entire message from the application to the DB.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: