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:
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.
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
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:
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.
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.
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
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
- 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
- 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
- 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:
(SDU = 4202)
(TDU = 4202)
(SID_NAME = ORCL)
(GLOBAL_DBNAME = ORCL.WORLD)
(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:
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.
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.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.