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 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.
The performance of distributed transactions can be
improved using some important settings that I'll
illustrate below. These include parameters within the
sqlnet.ora, tnsnames.ora, and protocol.ora
files. These parameters can be used to change the
configuration and size of TCP packets, and adjusting
these parameters can have a profound impact on the
underlying network transport layer to improve the
throughput of all Oracle transactions.
As I noted, Oracle*Net does not allow the Oracle
professional the ability to tune the underlying network
layer, and the majority of network traffic cannot be
tuned from within the Oracle environment. Remember,
Oracle Net is a layer in the OSI model that resides
above the network-specific protocol stack.
The frequency and size of network packets, however, can
be controlled by the Oracle DBA. Oracle has 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.
Tuning for throughput vs response time
The DBA is faced with two approaches to network
tuning, optimizing for either fast individual response time vs.
optimizing packet shipment for maximum throughput:
- Optimal network throughput: Oracle
suggests setting recv_buf_size and send_buf_size
sqlnet.ora parameters equal to 3 times the Bandwidth Delay
Product (BDP). Also, set tcp.nodelay=yes.
- Optimal network response time: For optimal
response time for individual transactions, see the notes below
for getting fast, small packets.
Oracle*Net connections between servers can be tuned
using several parameters. Keep in mind, however, 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:
- The sqlnet.ora server file—The
automatic_ipc parameter
- The sqlnet.ora client file—The
break_poll_skip parameter
- The tnsnames.ora and listener.ora
files—The SDU and TDU parameters
- The protocol.ora file—The tcp.nodelay
parameter
These tuning parameters will affect only the performance
of the Oracle Net layer. Let's examine them in detail
and see how they can be adjusted to improve Oracle Net
throughput.
The
tcp.nodelay parameter in the protocol.ora
file
Oracle Net, by default, waits until the buffer is filled
before transmitting data. Therefore, requests aren't
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 on both 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,
causing slowdowns in the network.
The tcp.nodelay parameter should be used only if
TCP timeouts are encountered. Setting tcp.nodelay
can cause a huge improvement in performance when there
is high-volume traffic between database servers.
The
recv_buf_size and send_buf_size in the
sqlnet.ora file
If you are on 10g and beyond and you are tuning your network to
maximize throughput (as opposed to tuning for fast response time),
Oracle suggests setting recv_buf_size and send_buf_size
sqlnet.ora parameters equal to 3 times the Bandwidth Delay
Product (BDP). See
setting recv_buf_size and send_buf_size.
As the name implies, the recv_buf_size and
send_buf_size parameters in the sqlnet.ora file
determine the size (in bytes) of the socket receive and send buffers
for a SQL*Net connection.
To prevent network contention, the values for recv_buf_size
and send_buf_size should be set to the maximum amount of
data that might be sent, at least 3x the value of your bandwidth
delay product (BDP).
See these important notes on setting
send_buf_size and recv_buf_size. and these tips for finding your Oracle
BDP value.
The
automatic_ipc parameter of the sqlnet.ora
file
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
it's absolutely useless for Oracle Net clients.
The automatic_ipc parameter should be used only
on the database server 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;
with this setting, all Oracle Net clients can improve
performance.
The SDU and TDU
parameters in the tnsnames.ora file
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.
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.
Note: In Oracle 10g and beyond,
Oracle recommends an SDU value of 32k (32767).
The following guidelines apply to SDU and TDU:
- The SDU should never be set greater than TDU
because you'll 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.
- 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.
- If the 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 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.
The
queuesize parameter in the listener.ora file
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 pre-allocates
resources for anticipated requests, therefore using more
system memory and resources. You may want to consider
using MTS and pre-spawned 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.
Performance
While most of the network packet traffic is tuned at the
network level, proper settings for Oracle Net parameters
can have a great impact on the performance of
distributed systems. It's the job of the Oracle
professional to fully understand and optimize these
important parameters.
Oracle-l notes this on tcp.nodelay and it's
relationship to the SDU and TDU parameters:
"Setting tcp.nodelay
disables the Nagle algorithm in the tcp stack that
tries to efficiently balance the data load of a
packet with the delay in dispatching it.
Effectively, you're saying "to
xxx with optimizing the data payload ... send those
babies now!". The complete antithesis of what the
SDU/TDU settings are trying to do for you.
You'll end up with a larger
number of smaller packets on your WAN, and if it's
latency that's your problem, this will make matters
worse, not better."
For more information, check my notes on
Oracle Network Tuning.
|
|
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.
|