Resolving Slow Client Server Architecture In Oracle

Resolving A Slow Client/Server Application By Increasing the array processing size  

If A client server application takes several seconds to bring up a certain screen indicates that some thing is slow and the reasons should be explored. Developers gets frustrated because the delay was occurring before the user had any chance to start the application which involved some query operation. 
If A client server application takes several seconds to bring up a certain screen indicates that some thing is slow and the reasons should be explored. Developers gets frustrated because the delay was occurring before the user had any chance to start the application which involved some query operation. The only thing happening in the form on startup was some fetching of basic reference data. All of the SQL had been tuned and was known to work very quickly. Being frequently accessed reference data, the data blocks were likely to be in the buffer cache--eliminating slow I/O as a possible bottleneck.

If we Query the v$session_event for a specific user which complain for slow loading who had just logged on to the application but not yet begun any activities and for this user the v$session_event can show waits on the SQL*Net message from client event, and can have around 5 Minutes of waiting. This is an idle event, which we would typically ignore, but there can be other things which are not proper and is to be de bugged..

A process is qued with waits on the SQL*Net message from client event when it has completed a request from a user and now needs to wait for the next request. One can typically see high wait times on an event in front-end applications that spend a lot of time waiting for a user to submit a query or initiate some other action that requires database activity. So the some time 5 minutes seconds of wait time here did not seem unusual, but then higher number of waits did not seem right.

If the application code is modified in a test environment so that it would disable timed statistics for the session after all initialization is complete. This caused the time information in v$session_event to reflect wait times during the form startup phase only; the time spent waiting for a user to initiate an action would be excluded because timed statistics would be disabled at that point.

V$SESSION_EVENT shows following information after modification:

EVENT TOTAL_WAITS TIME_WAITED

------------------------------ -------------------- --------------------

SQL*Net message to client 18560 7

SQL*Net message from client 8540 1064

Some Information In The V$SESSAT for the relevant session showed the following:

NAME VALUE
------------------------------ ----------
session logical reads 9545
CPU used by this session 84
physical reads 0

The above data shows that when the application started up, it was spending over 10 seconds performing more than 18,000 network roundtrips. The database server was using a little under one second of CPU time to perform 9,545 logical reads of data blocks which existed in the buffer cache.

A closer inspection of the reference data being retrieved by the application revealed 9,545 rows of reference data. The application code was fetching the data from cursors one row at a time. This resulted in two network roundtrips and one logical read for each row fetched.

On Further modifying he application by using Oracle's array processing interface in order to fetch 100 rows at a time. This enhanced the performance drastically by reducing network roundtrips. An ad on benefits observed was the reduction of CPU usage on the database server as only about one logical read per data block was required, instead of one logical read per row retrieved.

When fetching 100 rows at a time, the waits and session statistics looked like this:

EVENT TOTAL_WAITS TIME_WAITED
------------------------------ -------------------- --------------------
SQL*Net message to client 200 0
SQL*Net message from client 199 28


NAME VALUE
------------------------------ ----------
session logical reads 135
CPU used by this session 3
physical reads 0

By fetching rows up to 100 at a time, a dramatically improve response time by reducing network roundtrips and reduction of CPU usage on the database server at the same time.