Tuning Oracle Net - Expert Oracle RAC Performance Diagnostics and Tuning - page 345

Step 3(f )
After the array size is set to 200, execute the query from Step 3(b) once again and analyze the trace file using the Oracle
trace analyzer .(illustrated in Step 3(c)). A subset of the report is illustrated in Figure 11-4 .
As seen in Figure 11-4 , the overall response time has improved several folds. The total response time is 1,076
seconds compared to 17,308 seconds (array size of 15). Whereas the total number of rows processed remains the
same, the number of fetches performed on the database server has dropped significantly. The SQL Net related wait
times were also reduced, indicating that increasing the array size helps in the overall response time of the operation.
Wait Events related to SQL*Net
The following are the list of wait events related to SQL Net. Depending on the type of operation, some of the events are
treated as idle events and can be ignored.
SQL*Net Message to Client
This event occurs when the database needs to send a message to the participating client and the client is busy
processing its requests and is not ready to accept new data or messages from the database server. This indicates that
the database is waiting for the client to be ready and is not able to complete its operation. This event does not indicate
that the database is busy. Because the operation cannot be completed, it affects the response time of the operation.
In Figure 11-3 and in Figure 11-4 , this is indicated as a network related wait event. The overall wait time changed
when the array size was increased from the default value of 15 to 200.
SQL*Net Message from Client
This is the contrary to the previous wait event. This event is triggered when the database is waiting to receive messages
from the client. The client has initiated a session and has started to send data and/or messages to the database server
but has paused. This paused time is measured in seconds is recorded (SECONDS_IN_WAIT column in V$SESSION) as
wait times in the database. Oracle records these wait events as idle wait events because the database does not perform
any operation during this time. High waits on this event could indicate poor response time to the end user and cannot
be ignored. High waits on this event normally trigger some kind of network problem that needs to be investigated
further.