Remote DBA Plans
Remote DBA Service

Optimizing Oracle NET Configuration

Oracle Tips by Burleson Consulting

There are several tuning parameters that will affect the performance of Oracle NET connections between servers. However, we must always remember that the tuning of the network is outside the scope of Oracle, and the services of a qualified network administrator should be used for tuning the network. The following parameter files contain settings that affect the size and frequency of packet shipping across the network:

sqlnet.ora server file
- automatic_ipc

sqlnet.ora client file
- break_poll_skip

tnsnames.ora file
- SDU, TDU

listener.ora file
- SDU, TDU

protocol.ora file
- tcp.nodelay

Remember, these are the only tuning parameters that will affect the performance
of the Oracle NET layer. Let's discuss these parameters and see how they can be adjusted to improve Oracle NET throughput.

The tcp.nodelay Parameter in protocol.ora

By default, Oracle NET waits before transmitting a request until the buffer is filled up.
This can mean on some occasions that a request is not sent immediately to its destination. Most often, this behavior occurs when large amounts of data are streamed from one end to another, and Oracle NET waits until the buffer is full before transmitting the packet. To remedy this problem, you can add a protocol.ora file and specify tcp.nodelay to stop delays in the buffer flushing process.

For all TCP/IP implementations, the protocol.ora file can be specified to indicate no data buffering. This parameter can be used on both the client and the server. The statement in protocol.ora is:

tcp.nodelay = yes

By specifying this parameter, TCP buffering is skipped and every request is sent immediately. In some cases, setting this parameter can cause network slowdowns. The network traffic can increase due to the smaller (and more frequent) network packets being transmitted between the client and the server.

Oracle recommends that tcp.nodelay should only be used if TCP timeouts are encountered. However, in conditions of high-volume traffic between database servers, setting tcp.nodelay can have a huge improvement in performance.

The automatic_ipc Parameter of sqlnet.ora

The automatic_ipc parameter speeds local connections to a database because it bypasses the network layer. If automatic_ipc=on, Oracle NET will first check to see if a local database with the same alias definition exists. If so, the connection will be translated to a local IPC connection and will therefore bypass the network layers. This is, of course, only useful on database servers, and is a completely useless feature on Oracle NET clients.

On the database server, the automatic_ipc parameter should only be used in cases where an Oracle NET connection must be made to the local database. If no local database connections are needed or required, put this parameter to off, and all Oracle NET clients should have this setting to improve performance.

SDU and TDU Parameters in tnsnames.ora

The SDU and TDU parameters are placed in the tnsnames.ora and listener.ora files. SDU is the session data unit, and specifies the size of the packets to send over the network. Ideally, this size should not surpass the size of the MTU (maximum transmission unit). This MTU value is fixed and depends on the actual network implementation used. Oracle recommends that SDU should be set to MTU.

The TDU (transport data unit) is the default packet size used within Oracle NET to group data together. Ideally, the TDU parameter should be a multiple of the SDU parameter. The default values for SDU and TDU are 2,048, and the maximum value is 32,767 bytes.

The following guidelines apply for SDU and TDU:

On fast network connections (T1 or T3 lines), you should set SDU and
TDU equal to the MTU for your network. On standard Ethernet networks, the default MTU size is set to 1,514 bytes. On standard token ring networks, the default MTU size is 4,202.

The SDU should never be set greater than TDU because you will waste network resources by shipping wasted space in each packet.

If your users are connecting via modem lines, you may want to set SDU and TDU to smaller values because of the frequent resends that occur over modem lines.

If the Multi-Threaded Server (MTS) is used, you must also set the mts_dispatchers with the proper MTU TDU configuration.

Here is an example of these parameters on a token ring network with an
MTU of 4,202:

listener.ora

   SID_LIST_LISTENER =
      (SID_LIST =
         (SID_DESC =
            (SDU = 4202)
            (TDU = 4202)
            (SID_NAME = ORCL)
            (GLOBAL_DBNAME = ORCL.WORLD)
         )
      )

tnsnames.ora

   ORCL.WORLD =
      (DESCRIPTION =
         (SDU=4202)
         (TDU=4202)
         (ADDRESS =
            (PROTOCOL = TCP)
            (HOST = fu.bar)
            (PORT = 1521)
         )
         (CONNECT_DATA = (SID = ORCL))
      )

Again, we must remember that the SDU and TDU settings are a direct function of the connection speed between the hosts. If you have a fast T1 line, set SDU=TDU=MTU. For slower modem lines, you need to experiment with smaller values of SDU and TDU.

For Oracle version prior to 9i, the database automatically registered instances in the listener.ora file unless you took one of the following actions:

Implement the Multi-Threaded Server (MTS) and define the mts_dispatchers in your init.ora file:

MTS_DISPATCHERS="(DESCRIPTION=(SDU=8192)(TDU=8192)\
ADDRESS=(PARTIAL=TRUE)(PROTOCOL=TCP)(HOST=supsund3)))\

(DISPATCHERS=1)"

Use service_name=global_dbname in the Connect_Data section of the tnsnames.ora file, where global_dbname is configured in listener.ora. Note that this setting will disable the use of Transparent Application Failover (TAF), which is not supported using global_dbname. For details, see “Configuring Transparent Application Failover” in the Oracle NET Administrator's Guide.

Do not use automatic service registration. To do this, you must set the init.ora parameter local_listener to use a different TCP port than the one defined in your listener.ora file.

Next, let's look at the queuesize parameter and see how it affects network performance.

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.