Oracle
databases are often shared across geographical areas, so
it's imperative that the Oracle professional understand
how database performance is affected by network
communications. The Transparent Network Substrate (TNS),
provided by Oracle, allows distributed communications
between databases.
The TNS, which is a distributed protocol, allows for
transparent database communications between remote
systems. The TNS serves as an insulator between Oracle's
logical data request and the physical communications
between the remote servers. As such, the network
administrator is able to control much of the network
performance tuning. The Oracle administrator, then, has
little control over the network settings that can affect
overall database performance:
You can improve the performance of distributed
transactions by using some important settings, several of
which are discussed in this article. The init.ora
parameters relate to distributed communications, while the
TCP parameters, such as tcp.nodelay, can be used to
change the packet-shipping mechanisms within the Oracle
database.
I also discuss the parameters within the sqlnet.ora,
tnsnames.ora, and protocol.ora files. You
can use these tools to change the configuration and size
of TCP packets, and they can have a profound impact on the
underlying network transport layer to improve the
throughput of all Oracle transactions.
Oracle*Net does not allow the Oracle administrator to tune
Oracle network parameters to improve network performance.
In fact, the majority of network traffic can't be tuned
from within the Oracle environment. Oracle*Net is a layer
in the OSI model that resides above the network-specific
protocol stack. Almost all network tuning, then, is
external to the Oracle environment.
In response to a data request, Oracle*Net gets the data
and hands it over to the protocol stack for transmission.
The protocol stack then creates a packet from this data
and transmits it over the network. Oracle*Net's sole task
is to pass data to the protocol stack, leaving little
means for the DBA to improve network performance.
The DBA can, however, control the frequency and size of
network packets. There exists in Oracle a wealth of tools
to change packet frequency and size. A simple example
involves changing the refresh interval for a snapshot to
ship larger amounts at less frequent intervals.
Oracle*Net connections between servers can be tuned using
several parameters. Keep in mind, though, that network
tuning is outside the scope of Oracle, and a qualified
network administrator should be consulted for tuning the
network. The frequency and size of packet shipping across
the network can be affected by using settings contained in
the following parameter files:
- protocol.ora file—tcp.nodelay
- sqlnet.ora server file—automatic_ipc
- sqlnet.ora client file—break_poll_skip
- tnsnames.ora file—SDU, TDU
- listener.ora file—SDU, TDU, and
queuesize
These tuning parameters will affect the performance of the
Oracle*Net layer only. Let's examine them in detail and
see how they can be adjusted to improve Oracle*Net
throughput.
The tcp.nodelay parameter in
protocol.ora
Oracle*Net, by default, waits until the buffer is full
before transmitting data. Therefore, requests are not
always sent immediately to their destinations. This is
most common when large amounts of data are streamed from
one end to another, and Oracle*Net does not transmit the
packet until the buffer is full. Adding a protocol.ora
file, and specifying a tcp.nodelay to stop buffer
flushing delays, can remedy this problem.
The protocol.ora file can be specified to indicate
no data buffering for all TCP/IP implementations. The
parameter can be used both on the client and server. The
protocol.ora statement is:
tcp.nodelay = yes
Specifying this parameter causes TCP buffering to be
skipped so that every request is sent immediately. Keep in
mind, however, that network traffic can increase due to
smaller and more frequent packet transmission, therefore
causing slowdowns in the network.
The parameter tcp.nodelay should be used only if
TCP timeouts are encountered. Setting tcp.nodelay
can cause a huge performance improvement in high-volume
traffic between database servers.
The
automatic_ipc parameter of
sqlnet.ora
The
automatic_ipc parameter bypasses the network
layer, thereby speeding local connections to the database.
When automatic_ipc=on, Oracle*Net checks to see if
a local database is defined by the same alias. If so,
network layers are bypassed as the connection is
translated directly to the local IPC connections. This is
useful on database servers, but absolutely useless for
Oracle*Net clients.
The automatic_ipc parameter should be used on the
database server only when an Oracle*Net connection must be
made to the local database. If local connections are not
needed or required, set this parameter to off, improving
the performance of all Oracle*Net clients.
SDU
and TDU parameters in tnsnames.ora and
listener.ora
The session data unit (SDU) and transport date unit
(TDU) parameters are located in the tnsnames.ora
and listener.ora files. SDU specifies the
size of the packets to send over the network. Ideally, SDU
should not surpass the size of the maximum transmission
unit (MTU). MTU is a fixed value that
depends on the actual network implementation used. Oracle
recommends that SDU be set equal to MTU.
Note:
Prior to release 7.3.3, both
SDU and TDU were fixed at 2K and couldn't be changed.
The TDU is the default packet size used within
Oracle*Net to group data together. The TDU
parameter should ideally be a multiple of the SDU
parameter. The default value for both SDU and
TDU is 2,048, and the maximum value is 32,767 bytes.
The following guidelines apply for SDU and
TDU:
- You should never set the SDU greater than
TDU because you'll waste network resources by
shipping wasted space in each packet.
- If your users are connecting via dial-up modem
lines, you may want to set SDU and TDU to
smaller values because of the frequent resends that
occur over modem connections.
- 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 bytes.
- If a Multi-Threaded Server (MTS) is used, you must
also set the mts_dispatchers with the proper
MTU TDU configuration.
- For Streams
replication and for using Oracle in a WAN environment,
Oracle recommends that you increase the SDU to 32k.
Also, the Oracle docs note:
To take
advantage of an increased SDU for Streams
propagation, the receiving side sqlnet.ora file
must include the DEFAULT_SDU_SIZE parameter. The
receiving side listener.ora file must indicate
the SDU change for the system identifier (SID).
The sending side tnsnames.ora file connect
string must also include the SDU modification
for the particular service.
For Streams, the SEND_BUF_SIZE and RECV_BUF_SIZE
parameters in the listener.ora file increase the
performance of propagation on your system.
Oracle internal testing of Oracle Data Guard on
a WAN has demonstrated that the maximum setting
of 32K (32768) performs best on a WAN. The
primary gain in performance when setting the SDU
is a result of the reduced number of calls to
packet the data.
In addition to setting the SDU parameter,
network throughput can often be substantially
improved by using the SQLNET.SEND_BUF_SIZE and
SQLNET.RECV_BUF_SIZE Oracle Net parameters to
increase the size of the network TCP send and
receive I/O buffers.
The listing below shows an example of these parameters on
a token ring network with an MTU of 4,202 bytes.
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))
)
The SDU and TDU settings are a direct
function of the connection speed between the hosts. For
fast T1 lines, set SDU=TDU=MTU. For
slower modem lines, experiment with smaller values of
SDU and TDU.
Since Oracle8i, the database will automatically
register instances in the listener.ora file unless
you implement one of the following actions:
- Disable automatic service registration. To do this,
you must set the init.ora parameter
local_listener to use a TCP port other than the one
defined in your listener.ora file.
- Implement the MTS (not
recommended unless absolutely necessary) and define the mts_dispatchers
in your init.ora file, like this:
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.
The queuesize
parameter in
listener.ora
The number of requests the listener can store while Oracle
is working to establish a connection is determined by the
undocumented queuesize parameter. This parameter is
used only for very high-volume databases, where the
listener spawns thousands of connections per hour. The
number of expected simultaneous connections should be
equal to the size of the queuesize parameter.
Here's an example of this parameter in the listener.ora
file:
LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = marvin)
(PORT = 1521)
(QUEUESIZE = 32)
)
)
A disadvantage of this parameter is that it preallocates
resources for anticipated requests, therefore using more
system memory and resources. You may want to consider
using MTS and prespawned Oracle connections if you have
high-volume connections into a dedicated listener. Also,
note that some versions of UNIX do not allow queues
greater than five, and there are some restrictions of the
MTS queue size.
The break_poll_skip
parameter of sqlnet.ora
This client-only sqlnet.ora parameter indicates the
number of packets to be skipped before checking for a
user-initiated break. This parameter affects the amount of
CPU resources used by the Oracle*Net client and functions
only with servers that support in-band breaks. The default
value is four, and the results of modifying the value are
as follows:
- The higher the break_poll_skip value is set,
the less frequently the checking for CTRL-C occurs and
the less CPU is consumed.
- Conversely, the lower the value is set, the more
frequently the CTRL-C checking is done and the more CPU
is consumed.
External network
tuning
In this brief discussion of network-related parameters,
I've given you a sense of the scope and complexity of
network tuning. It's important to understand that
Oracle*Net is simply a layer in the OSI model lying above
the network-specific protocol stack, and that virtually
all network tuning is therefore external to Oracle.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
