SQL*Net Wait Events

What is a Wait Event ?

During the normal functioning of a database, an Oracle process will occasionally have to wait for something. These waits are recorded the RDBMS performance tables (V$views) where they are categorized into wait events.

There are two types of wait events, idle waits and non-idle waits. Non-idle waits indicate that a process is waiting for a resource that is temporarily unavailable, while idle waits simply mean that process has no work to do.

What is the meaning of SQL*Net Idle events ?

There are two type of SQL*Net events, SQL*Net client events and SQL*Net dblink events.

Often SQL*Net idle events are raised as a possible problem, due the large values seen for the events in database performance reports.

Elapsed times include waiting on following events: 
Event waited on              Times  Max. Wait  Total Waited 
-----------------------------Waited ---------- ------------ 
SQL*Net message to client    10000        0.00         0.00 
SQL*Net message from client  10000        0.01         4.91

SQL*Net client
Generally, the SQL*Net client events represent idle events. That is when the client is infact waiting / doing no work and can be useful in indicating what is not the bottleneck. An example of this type of event is the most commonly encountered idle wait-event 'SQL*Net message from client'.

One possible exception to this is the SQL*Net break/reset to client event.

SQL*Net break/reset to client
The server is sending a break or reset message to the client. The session running on the server is waiting for a reply from the client.These waits are caused by an application attempting to:

• Select from a closed cursor

• Select on a cursor after the last row has already been fetched and no data has been returned

• Select on a non-existent table

• Insert a duplicate row into a uniquely indexed table

• Issuing a query with invalid syntax

• If the value, v$session_wait.p2, for this parameter equals 0, it means a reset was sent to the client. A non-zero value means that the break was sent to the client.

SQL*Net message from dblink
This event signifies that the session has sent a message to the remote node and is waiting for a response from the database link. This time could go up because of the following:

• Network bottleneck, For information, see "SQL*Net message from client".

• Time taken to execute the SQL on the remote node

It is useful to see the SQL being run on the remote node. Login to the remote database, find the session created by the database link, and examine the SQL statement being run by it.

• Number of round trip messages

Each message between the session and the remote node adds latency time and processing overhead. To reduce the number of messages exchanged, use array fetches and array inserts.

Oracle Net server tracing can also assist in confirming what is happening for the dblink.
Note 746917.1 How to Enable Oracle Net Tracing for Database links.


In summary, Client events should not be considered for performance measure unless values are extremely high.

For more information on RDBMS performance and events see Oracle documentation and articles such as

http://www.oracle.com/technology/products/manageability/database/pdf/OWPerformanceMgmtPaper.pdf

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm#sthref3189