I recently looked at the database performance of a production database. This post will explain the network performance parameters which I implemented as a result of this work.
Here's the best SQLNET.ORA (server) I could come up for our OLTP application:
SQLNET.AUTHENTICATION_SERVICES=(NTS)
NAMES.DIRECTORY_PATH=(TNSNAMES)
DISABLE_OOB=ON
TCP.NO_DELAY=YES
DEFAULT_SDU_SIZE=32120
USE_DEDICATED_SERVER=ON
SQLNET.EXPIRE_TIME=10
And here's the relevant part of the LISTENER.ORA:
LISTENER =
(DESCRIPTION =
(SDU = 32120) (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER)(PORT = 1521)(SEND_BUF_SIZE = 65535)(RECV_BUF_SIZE = 65535))
)
Now, these settings are specifically chosen for an OLTP application, where response time is more important than throughput. I'll explain the settings and values.
SQLNET.AUTHENTICATION_SERVICES=(NTS)
Not much to say here except on the server I always use NTS because it's required by ASM, and on the Citrix server I set this variable to NONE.
NAMES.DIRECTORY_PATH=(TNSNAMES)
We use TNSNames for service resolution, so that's what we put.
DISABLE_OOB=ON
Disable Out-of-Band protocol (disables Ctrl-C). The application doesn't require OOB, so why bother with this overhead? Disable it.
Note: do not include the parameter BREAK_POLL_SKIP. With BREAK_POLL_SKIP, the Oracle client will check for a Ctrl-C, while DISABLE_OOB completely disables it. If both are used, I am not sure which takes precendence.
USE_DEDICATED_SERVER=ON
I use Dedicated Server mode for all production databases to ensure maximum performance. We have enough RAM, so why not?
SQLNET.EXPIRE_TIME=10
Closes a dead connection after 10 minutes e.g. application crashed and we need to clean up the dead connection.
TCP.NO_DELAY=YES
This is the most important parameter for OLTP apps. It tells the Oracle network software "stop messing about with buffers, just send the data back to the client ASAP!". This had a substantial performance improvement.
Sybase, in its ASE documentation, recommends this to be set on for its ASE. The relevant document can be found here:
Sybase puts it eloquently:
The tcp no delay parameter controls TCP (Transmission Control Protocol) packet batching. The default value is 1, which means that TCP packets are not batched.
TCP normally batches small logical packets into single larger physical packets (by briefly delaying packets) fill physical network frames with as much data as possible. This is intended to improve network throughput in terminal emulation environments where there are mostly keystrokes being sent across the network.
However, applications that use small TDS (Tabular Data Stream) packets may benefit from disabling TCP packet batching.
Yes, I realise it's Sybase doc and not Oracle, but the concept is the same, and their explanation is the best I could find.
The anti-thesis of this parameter is these two:
RECV_BUF_SIZE
SEND_BUF_SIZE
These two parameters set up the data buffers for the packets going back & forth. It's perfect for DSS (reporting) type applications. Put these in your SQLNET.ORA if you want your OLTP app to run slowly!
If both sets of parameters are used, I'm not sure which takes precedence.
SQLNET.ORA: DEFAULT_SDU_SIZE=32120 & SDU=3120 in Listener.ora
This is extremely important. I'll explain how I arrived at this in a separate post. For the new SDU size to take effect, you must update the SQLNET.ORA (client and server) and the LISTENER.ORA (as shown above), and restart the listener.
No comments:
Post a Comment