Oracle7 Server Tuning

Tuning Networks

Tuning Networks
How to Detect Network Problems
How to Solve Network Problems

Tuning Networks

This chapter introduces networking issues which affect tuning. Topics in this chapter include

How to Detect Network Problems

Networks entail overhead which adds a certain amount of delay to processing. To optimize performance, you must ensure that your network throughput is fast, and that you reduce the number of messages that must be sent over the network.

It can be difficult to measure the amount of delay which the network adds to performance. There are three useful dynamic performance views in this regard: V$SESSION_EVENT, V$SESSION_WAIT, and V$SESSTAT.

In V$SESSION_EVENT, the AVERAGE_WAIT column indicates the amount of time that Oracle waits between messages. You can use this statistic as a yardstick to evaluate the effectiveness of the network.

V$SESSION_WAIT contains an EVENT column which lists the events for which active sessions are waiting. The "sqlnet message from client" wait event indicates that the shared or foreground process is waiting on a message from a client. If this wait event has occurred, the DBA can check to see whether the message has been sent by the user or received by Oracle.

You can investigate hangups by looking at V$SESSION_WAIT to see what it is that sessions are waiting for. If a client has sent a message, you can determine whether Oracle is responding to it, or is still waiting for it.

In V$SESSTAT you can see the number of bytes that have been received from the client, the number of bytes sent to the client, and the number of calls the client has made.

How to Solve Network Problems

Use the following strategies to solve network problems.

Using Array Interfaces

Reduce network calls by using array interfaces. Instead of fetching one row at a time, it is more efficient to fetch 10 rows with a single network round trip.

See Also: Programmer's Guide to the Oracle Precompilers for more information on array interfaces.

Using Out-of-band Breaks

Use out-of-band breaks by default. Out-of-band breaks are much faster than in-band breaks, which must continually check the connection for break messages resulting from a user trying to interrupt. If you select many rows, the system will check for interrupts many, many times. Since in-band breaks are so expensive, queries can run almost twice as fast if you have out-of-band breaks enabled. Out-of-band breaks signal the process to asynchronously send notification of a break. Checking for interrupts is thus much cheaper.

Using Listener Load Balancing

When many connection requests are made to a server, you can speed up connection time by having more than one listener receive the requests. Listener load balancing is also useful in a multiple server environment, where multiple listeners can handle replicated servers.

See Also: Understanding SQL*Net

Using Prestarted Processes

Prestarting processes can improve connect time with a dedicated server. This is particularly true of heavily loaded systems not using multi-threaded servers, where connect time is slow. If prestarted processes are enabled, the listener can hand off the connection to an existing process with no wait time whenever a connection request arrives. Connection requests do not have to wait for new processes to be started.

See Also: Understanding SQL*Net

Adjusting SDU Buffer Size

Before sending data across the network, SQL Net buffers data into the Session Data Unit (SDU). It sends the data stored in this buffer when the buffer is full or when an application tries to read the data. When large amounts of data are being retrieved, and when packet size is consistently the same, it may speed retrieval to adjust the default SDU size.

Optimal SDU size depends on the normal packet size. Use a sniffer to find out the frame size, or set tracing on to its highest level to check the number of packets sent and received, and to see if they are fragmented. Tune your system to limit the amount of fragmentation.

Use Oracle Network Manager to configure a change to the default SDU size on both the client and the server; SDU size should generally be the same on both.

See Also: Understanding SQL*Net

Copyright © 1996 Oracle Corporation.
All Rights Reserved.