There is a misconception that SQL*Net is a protocol or runs on top of the network. These statements are false and, as such, means SQL*Net does not lend itself well to such questions as, Why does SQL*Net run faster on one infrastructure and not another? or Why is SQL*Net faster on the LAN than on the WAN?

While these are valid questions, it must be understood that SQL*Net has two main tasks:

• To read and write to the TCP send/receive buffers
• To give and receive data to and from an application such as SQL*Plus and the Oracle Database.

The act of reading and writing to the TCP buffers is an instantaneous process. Therefore delays are normally a result of:

• How data was "packetized" and given to TCP;
• An accumulation of wait times on ACKs when communicating on a WAN.

This note describes what can be done from a SQL*Net and an Application perspective. There is very little attempt to inform you how to tune your underlying physical TCP network. That is not the focus of this document.

The most important thing to note here is that most common networking performance issues are not SQL*Net related, but more about how the Application is written, how the network infrastructure is set-up, or how the Oracle database is tuned. This note will touch on some non-Oracle-related issues later.

Tuning SQL*Net revolves around the concept of decreasing the number of packets put on to the network. There are only two things that can be done with SQL*Net to effect network performance and nothing else. These are

• Setting Session Data Unit (SDU)
• Setting tcp.nodelay.

Please note that the term nodelay does not mean work faster. Nodelay is a method of disabling Nagle's Algorithm. Refer to the section describing tcp.nodelay for more information.

While SQL*Net can only be tuned in two different ways, there are other factors that can affect performance (These will NOT be covered in detail in this note as they do not pertain to SQL*Net. However, they may be touched on to stress overall importance, for example, Application performance with ARRAYSIZE)

• Slow or busy server
• Underlying Network
• Using Shared Server(Used to be called MTS) or not
• Altering send and receive buffer sizes at the TCP layer
• Properly tuned database
• Properly functioning application

There also should be some basic understandings that some SQL*Net features will naturally cause some degree of performance degradation, such as

• Advanced Networking Option(ANO)
• Transparent Gateways
• Connection Manager(CMAN)
• Oracle Names (affecting connect times)

DETAILS

• FAQ
• Setting the ARRAY fetch size
• Altering SDU parameters
• tcp.nodelay
• Miscellaneous items
• New parameters for 10g: altering the send and receive buffer sizes in TCP
• Tuning the components of your network

FAQ

1. "I'm using TNSping to test my network performance and..."
   
   TNSping should never be used to test network performance. TNSping's only function is to send a Connect Packet (NSPTCN) to the listener; the listener replies with a Refuse Packet (NSPTRF) and a round trip time is computed. A slow TNSping time could be anything from poor DNS resolution to a slow network to a busy listener to a busy server.Document 787848.1Intended Usage of the 'tnsping' Tool
   
   
2. "SQL*Net query performance is slow but TCP/IP PING is fast, therefore SQL*Net is slow."
   
   This is a false statement. Ping only uses the ICMP/IP protocol (not the TCP protocol) to test whether another host is reachable. Since the TCP protocol is not used, a socket is not created, therefore data is not transferred between 2 nodes (except for 8+[optional] bytes needed for the echo request, but again, that is not TCP data), and none of the rules of TCP are being applied. If Ping is fast, that only means that IP was able to quickly find a route to the destination server.
   
   
3. "PING is fast but TNSPING is slow. What is wrong with TNSPING?"
   PING and TNSPING should not be used for ANY comparison. Please see 1 and 2 for explanations of TNSPING and PING, along with referenced note on TNSping usage.
   
   
4. "Why are FTP/sftp transfers faster than sqlnet?"
   
   Like PING, this test is not a good benchmark. There are no RFC standards for setting window sizes in FTP. The vendor may have FTP tuned very well with respect to window sizes. You are able to pass send and receive window sizes to TCP via sqlnet. See Document 260984.1 for more information.

Setting the ARRAY Fetch Size in the Application

An application can be sped up on the network by optimizing the amount of records the application fetches at once. "Sniffing" technology has been used to illustrate how network optimization is done.

In this example SQL*Plus is the application and a SELECT * FROM BIGEMP is issued. The BIGEMP table consists of 4928 rows.In SQL*Plus the amount of records fetched at once is set using ARRAYSIZE.
If the default ARRAYSIZE of 15 in SQL*Plus is used, and the SELECT is issued, the sniffer trace generated shows that 256 packets are sent and received between client and server. This SELECT took 16 seconds.

Now if the ARRAYSIZE in SQLPLUS is set to 49:

and the SELECT is issued, the sniffer trace shows that 154 packets were put on the network, taking 13 seconds.So by changing the ARRAYSIZE, performance has improved by 3 seconds, by reducing the amount of packets on the network by 102.

Below is a simple fun example:

When applying this to TCP, it is important to understand how TCP/IP works. By default the maximum amount of actual 'data', after the headers etc., that can send in a TCP packet is 1460 bytes. In this example, when ARRAYSIZE is set to the default 15, the sniffer shows that the request is for 15 rows at a time and in nearly all cases there is a waste of around 500 bytes of free space in each packet. If these free 500 bytes can be utilized, this TCP packet can be filled and overall fewer packets will be sent and received. In the first case the application asks for 15 rows, so a fetch is sent and it gets one packet of data back with 15 rows in and 500 bytes of empty space, then another fetch is sent and another packet of 15 rows of data with another 500 bytes of free space, etc. When the ARRAYSIZE is set to 49, the application asks for 49 rows and in this case, it fills the first TCP packet fully, and then immediately get another data packet back with the rest of the rows in, and this almost fills the second data packet up.

So an application can tuned by setting a more appropriate ARRAY fetch size thus, it is possible to increase the application's performance. Setting the appropriate ARRAY fetch size is determined by trial and error and using a sniffer (if available). The above example is appropriate for DML and DDL.

In many cases changing the ARRAY fetch size is not an option because many applications are not developed in-house and are provided by a 3rd party vendor. A high percentage of increasing the performance of applications on a network depends on how well it is has been written to perform in conjunction with the protocol that sits underneath it.

Setting the SDU size

From a SQL*Net perspective, the only thing that can be tuned is the SDU size. SDU stands for Session Data Unit. SDU is a buffer at the Network Session (NS) layer from which SQL*Net packets are passed via TDU (Transport Data Unit) at the Network Transport (NT) layer, to the underlying protocol.
By default in version 2.3 (7.3), SDU was set to 4K. However, it changes to 2K in Net*8 and Oracle*Net (8.0 and higher) for dedicated connection. Shared server connections default is 32k.From 11.2 onwards dedicated SDU size increased to 8k, whilst shared server is increased to 65k
Below is a real life example where a customer's application performance was improved by tuning the SDU size.
A company had a large Global Information System written in OCI, where information was initially stored in flat files and then passed via the OCI application into Oracle 8.0.4. The application read the flat file in fixed chunks and then inserted them via Net8 into the database. The data was read from the flat files in 4K blocks but, when passed via OCI over Net8, the amount of data passed over the line equated to 2900 bytes.

With the default SDU size of 2K, each block of data was handled as follows:

If we set SDU to 4K, each block will be handled as follows:

So for each 4K block the OCI application passes to Net8, two TCP packets are sent instead of three by changing the SDU size. Ignoring Acknowledgment packets (ACK's), given that each flat file was 30MB in size, there would be 7680 X 4K blocks of data to send. With SDU at 2K, 23040 packets of data are sent. With SDU set at 4K, 15360 packets are sent, a saving of 7680 data packets.

It could be argued that the same amount of data is being passed over the wire, and it should take the same amount of time given a fixed bandwidth. However each packet has Data Link Control (DLC), TCP, and IP header information which take extra bandwidth. For example 7680 extra packets will use 405K of extra header data. Secondly, each packet will take a finite amount of time to pass through the Network Interface Card (NIC), routers etc, all of which will add time to the overall transaction. Also, if the ACK's in the equation included, packets on the network are lower with SDU set to 4K.

To understand this it is necessary to understand how ACK's work. In the above example, where 2K SDU size was used then there will be three data packets sent. The way TCP works is that if a data packet is sent and if that is the end of transmission, an ACK will be sent back. If some data is sent and it fills two packets, an ACK will be sent on receipt of two consecutive TCP segments. If three data packets are sent and the transmission ends, an ACK will be sent for the first two data packets and an ACK for the third packet. Therefore with an SDU of 2K, there will be three data packets and therefore get two ACK packets per 2900 bytes of data.

With an SDU of 4K there are only two packets per 2900 bytes sent therefore only one ACK is sent. So with an SDU of 2K, 15360 ACK's are sent. And with an SDU of 4K set there will 7680 ACK packets, a saving of 7680 ACK packets.

So as demonstrated by setting SDU appropriately there can be a marked improvement in network and application performance.Again getting the best SDU setting comes down to a bit of maths.

How to set SDU

Example 1. To set SDU size, edit the TNSNAMES.ORA and also the LISTENER.ORA on the desired server, as follows (if either one is not set, the default SDU will be used for SQL*Net communication):

Tnsnames.ora

Listener.ora

For Shared Server connections, formally known as MTS, SDU needs to also be set in the value for Dispatchers. For Example

Example 2

From releases 9.0.1.5, 9.2.0.4 and 10.2.0.1 onwards, the default SDU size can now be changed for connections using SERVICE_NAME. ie Dynamic registeration. Where as example 1, is for SID only, which is static registration

SQLNET.ORA
DEFAULT_SDU_SIZE = 8192

This now means SDU can be set on a per connection basis. Either by adding SDU parameter to local naming configuration file (TNSNAMES.ORA) and the listener configuration file (LISTENER.ORA), or by setting SDU for all Oracle Net connections with the parameter DEFAULT_SDU_SIZE in the SQLNET.ORA file on the client and server. Document 44694.1SQL*Net Packet Sizes (SDU & TDU Parameters)

SEND/RECV_BUF_SIZE

This is a new parameter for 10g onwards. SEND/RECV_BUF_SIZE alters the underlying TCP's send and receive windows. These windows correspond to the amount of data that can be sent before a TCP acknowledgment is received. Some network delays can be attributed to the time spent waiting on these ACKs. The time spent waiting on TCP acknowledgments is seen mostly in WAN environments. Little time is spent waiting on ACKs in LAN environments.

For more information, please refer to Note 260984.1

This is all that can be done from an Application or SQL*Net perspective. However if this is done right but performance is still an issue, then there are other things which are degrading performance on the WAN.

tcp.nodelay

Set in the PROTOCOL.ORA file for Oracle8i and in the  SQLNET.ORA file for Oracle9i and up.

NOTE: tcp.nodelay is not a performance feature. It alters the way packets are delivered on the network thereby possibly effecting performance. It is recommended NOT to alter this parameter unless it is known what the outcome will be.

Under certain conditions for some applications using TCP/IP, Oracle Net packets may not get flushed immediately to the network. Most often, this behavior occurs when large amounts of data are streamed. The implementation of TCP/IP itself is the reason for the lack of flushing, causing unacceptable delays. To remedy this problem, specify no delays in the buffer flushing process. This is not a SQL*Net feature but rather the ability to set the persistent buffering flag at the TCP layer.

"tcp.nodelay" activates/deactivates Nagle's Algorithm. More information on Nagle's Algorithm can be found in RFC 896.

Miscellaneous Items

• LISTENER.ORA: QUEUESIZE
  
  The QUEUESIZE parameter determines the number of concurrent connection requests the Listener can store while it works to establish those connections.If the number of incoming requests exceeds the size of the buffer, the client requesting the connection will receive failures. Ideally, the size of this buffer should be equal to the maximum number of expected simultaneous connection requests.
  
  
  LISTENER =
  (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = NodeA)(PORT = 1521)(QUEUESIZE = 300))
   )
  
  This parameter is only used when the TCP/IP or DECNET protocols are used, upto version 10.1
  For IPC example see Document 361957.1 Intermittent ORA-28575 Calling Extproc, When Load is high / system Stressed
  
  The value of QUEUESIZE is dependent on underlying TCP/IP tuneables, such as
  
  Sun Solaris: tcp_conn_req_max_q (default 128) 
  HP-UX: tcp_conn_req_max (default 20) 
  HP Tru64: somaxconn (default 1024)
  
  Default queue sizes
  
  Solaris default QUEUESIZE is 5
  Aix default QUEUESIZE is 1024
  Linux Default QUEUESIZE is 128
  Windows default QUEUESIZE is 50
  
  See Document 214122.1 on how to confirm queue size for TNS listener
• SQLNET.ORA: BREAK_POLL_SKIP
  
  A lesser known SQL*Net parameter. This value specifies the number of packets to skip before checking for a user break.
  Rules of thumb here:
  The higher the value, the less frequent CTRL-C checking, and the less CPU overhead used.
  The lower the value, the more frequent CTRL-C checking, and the more CPU overhead used.
  
  The default for this parameter is 4. The parameter is only useful on the client side of SQL*Net.
  
  
• SQLNET.ORA: DISABLE_OOB
  
  Out of band break checks can be disabled by adding this parameter to the SQLNET.ORA file. If for some specific reason the checks should not be performed, set this parameter to 'ON'. By default,SQL*Net assumes 'OFF' for this parameter.
  
  
• SQLNET.ORA: AUTOMATIC_IPC (Last supported release 8.0)
  AUTOMATIC_IPC = { ON | OFF }
  This parameter in the "SQLNET.ORA" file indicates whether SQL*Net should 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 SQL*Net client machines.
  
  Even on databases, this feature should only be used in very specific cases where a SQL*Net connection must be made to the local database. If no local database connections are needed or required, put this parameter to OFF, thus eliminating another short delay.
  
  
• SQLNET.ORA: NAMES.DIRECTORY_PATH
  NAMES.DIRECTORY_PATH = (ONAMES,TNSNAMES)
  This parameter specifies the resolution path for TNS aliases. The first entry in this parameter will be checked first, then the second, and so on ...
  By default, starting from SQL*Net 2.3.3, an attempt will be made to contact 5 Name Servers with the names 'nameserver1', 'nameserver2', etc... Only when these attempts time-out, the TNSNAMES will be consulted.
  
  Also, if there is no Oracle Names defined on the network, it is advisable to specify this parameter in the SQLNET.ORA without the 'ONAMES' value.
  
  Please note that 9i was the terminal release for ONAMES and so 10g and on ONAMES is obsoleted.
  
  If there is no local TNSNAMES.ORA on the client machines, also adjust this line in the SQLNET.ORA file (such as with LDAP use).
  
  
• Deactivate all logging and tracing
  Tracing can be enabled, both on the client and on the server. You can deactivate the SQL*Net tracing completely by specifying the following lines:
  
  SQLNET.ORA:
  TRACE_LEVEL_CLIENT =OFF
  TRACE_LEVEL_SERVER =OFF
  TNSPING.TRACE_LEVEL=OFF
  
  Logging and tracing are deactivated both when the trace levels are put to 'OFF' or
  
  LISTENER.ORA:
  TRACE_LEVEL_LISTENER=OFF
  LOGGING_LISTENER=OFF
  Remove the Listener log files
  If the Listener logging mode remains active, the LISTENER.LOG file continues to grow. Since the Listener program keeps this file locked and continues to write a line in this file for every connection made, the sheer size of the file by itself can become a problem. If this file becomes too large, turn off logging temporarily by using "lsnrctl set LOG_STATUS OFF" and archive or delete the current log file.
  Start logging again (recommended) using "lsnrctl set LOG_STATUS ON" and the TNS Listener will start with a clean new file.

Tuning Network

As mentioned towards top of this note, as well as tuning the application, the majority of performance issues over the network comes down to the components of the network itself. If performance issues are experienced, please check the following (greater detail would come from the System or network Administrator)

• Make sure by looking at a sniffer trace that the clients are being routed to the server by the optimum route.
  
  
• Check that the routers' etc. frame sizes are set to optimum for maximum throughput, make sure that frames aren't being fragmented as they pass through routers. If they are fragmented, this obviously will cause more network traffic and lower performance due to more packets, ACK's and the time taken to fragment and rebuild packets.
  
  
• Make sure that TCP packets are not being lost anywhere during transmission. Losing packets links nicely into the TcpWindowSize. This parameter should be set the same on client and server. On WAN's if packets are lost at routers due to heavy network traffic, a large TcpWindowSize can cause more problems. The TcpWindowSize is the amount of data buffered on a system. It is a sliding window and each host has a send and a receive window. Take for example if the Window size is 8K, which it is on Microsoft platforms by default. If the send window is filled with 8K's worth of data, then eight packets of data are sent, at which time the data remains in the send window, until it receives an acknowledgment back. When the packets are sent a retransmit time is set, so if it doesn't receive an ACK in this predefined time, it will resend the packet. When the server receives the first two packets in sequence, it will send an ACK back. At this point the clients' send window slides along and allows another 2K's worth of data to be sent. If at this point the other six packets are lost and therefore the client doesn't receive the ACK's back, then there will be a retransmission of the packets, at which time the retransmit time for the packets will reset to twice the original time. Because the send window is now full, no more data can be sent until a successfully acknowledged of at least one packet has been received. So it can seen that if network problems persist and network packets are lost, more and more traffic will be put on the network and application and network performance will slow down.
  
  Through the sniffer trace these potential problems can be identified, but it would be down to the customer's Network Admin to analyze the results.