Tuning Oracle Network Usage


A network is a bottleneck for the simple reason that network cables are much slower than the speed of machines on nodes of that network. The following points apply in the speeding up of the use of a network from the perspective of how the database is accessed over that network.

The query below shows a method of assessing network performance from the perspective of Oracle where the average waiting time for the client or the server are recorded based on time waited for a message from the server or the client respectively.

select event,average_wait from v$session_event where event in ('SQL*Net message from client','SQL*Net message to client');

The query below shows the average waiting time for client and server processes and the bytes actually transferred.

select v$session_event.event "Event",v$session_event.average_wait "Avg Wait",v$sesstat.value "Bytes"
from v$session_event,v$sesstat
where v$session_event.event in ('SQL*Net message from client','SQL*Net message to client')
and v$session_event.sid = v$sesstat.sid
and v$sesstat.value > 0;

EVENT                                                            AVERAGE_WAIT     VALUE
---------------------------------------------------------------- ------------ ---------
SQL*Net message to client                                                   0      4758
SQL*Net message to client                                                   0        45
SQL*Net message to client                                                   0         0
SQL*Net message to client                                                   0         0
SQL*Net message to client                                                   0         1
SQL*Net message to client                                                   0       158
SQL*Net message to client                                                   0         1
SQL*Net message from client                                                 0         1
SQL*Net message from client                                                 0        50
SQL*Net message from client                                                 0         0
SQL*Net message from client                                                 0        50
SQL*Net message from client                                                 0         0
SQL*Net message from client                                                 0         0
SQL*Net message from client                                                 0         0
SQL*Net message from client                                                 0    591076
SQL*Net message from client                                                 0    591076
SQL*Net message from client                                                 0     75688
SQL*Net message from client                                                 0         0
SQL*Net message from client                                                 0         4
SQL*Net message from client                                                 0       267
SQL*Net message from client                                                 0      1406

Tuning network usage by Oracle is all a matter of using the network as little as possible. Probably the most important factor is grouping of SQL statements together into transactions. For instance, if ten rows are to be fetched it is more efficient to send a single request for ten rows at once rather than ten requests for each row. Since the network is much slower than the database server or a client machine this should be obvious.

In the same mold sharing of connections with connection pooling can help decrease network traffic substantially since a connection request must wait for the starting of new processes and memory area allocations on a server, ie. database or application level. Shared server processes (MTS - multi-threaded server) will also limit network activity or atleast the connection time waiting by sharing connections, ie. connection pooling. Dedicated server processes will not share connections and result in longer waits from network requests to the database for connections.

Net8 software buffers data into a Session Data Buffer (SDU) prior to passing over the network, this buffer is passed over the network when full or read by an application. The Oracle Network Manager can be used to change SDU size. Setting tracing to the most intense can help packets passed over the network, if there is excessive communication then increase the size of the SDU to decrease fragmentation of data transfers in multiple packets.

The listener process on the database server listens on the network for requests to the database. Many concurrent connection requests can swamp the listener. One solution is multiple listeners, another is increasing a listening process listening queue. The TCP.NODELAY option will move packets over the network faster, speeding up larger transactions. Also when setting TNSNAMES entries on the client use the IP-Address for the host name instead of the host name itself such that the IP-Address can be altered. On NT the IP-Address seems to be much faster, on Solaris I am not as yet sure of. Of course changing IP-Addresses will cause problems.