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
