Networking

Changing SDU Settings

Some useful artices on Metalink:
1005123.6 Tuning SQL*Net for better performance
44694.1 SQL*Net Packet Sizes (SDU & TDU Parameters)

By tuning the SDU it is possible to reduce the time taken to move around some screen from around 30 seconds to under 5. Similar improvements were observered in other areas of high data volumes between forms server and database.

Overview
The Session Data Unit (SDU) and Transport Data Unit (TDU) sizes are tunable (up to 32K or 32767 bytes) from Oracle 7.3 onwards. There is little benefit in overriding the default TDU size of 32767 bytes so we will only consider the SDU.

SDU values range from 512 to 32767 bytes with a default of 2048 bytes, in can be helpful to increase this when significant amounts of data are being sent via SQL*Net, as is often the case with v5.

Choosing a new SDU Value

We need to specify a larger SDU than the default, but what to choose?

Using the bigger is better approach, since we’re only talking connections between application server and database server which should have a nice quick connection, something approaching the maximum seems a good bet.

Oracle say to minimize packet header overhead and message fragmentation (at the TCP/IP level), set the SDU size as a multiple of the MSS (Maximum Segment Size) and that when Oracle Advanced Security Encryption is not used to increase the SDU size by one (1). And now we’re right into network speak!

The Maxium Transmission Unit (MTU) is the maximum size of packet that can get sent out onto the network. On Solaris you can check the MTU for a network interface by doing ifconfig -aIf it’s anything other than 1500 I’d be surprised (but you never know!).

The Maximum Segment Size (MSS) is the size of the payload in that packet - i.e. the actual data, to get the MSS you need to lop of the size of a TCP packet header from the MTU.

The header of a TCP packet is normally 40 bytes, so the MSS is therefore 1460 bytes. Apparently if you don’t use Oracle Advanced Security Encryption your header is only 39 bytes, so your effective MSS is 1461.

Therefore by specfying our SDU to be a multiple of the MSS we ensure that or SQL*Net message is split into the most efficient number of TCP/IP packets when it actually makes it out onto the network.

So to cut a long story short our SDU should be a multiple of 1461 (since we don’t use Oracle Advanced Security Encryption) and as near to 32767 as we can sensibly get. Two minutes with a calculator shows that 32142 is a good bet!

listener.ora
First we need to specify the SDU in the listener.ora file, but auto-registration of databases with the listener gives us a problem.

Our tnsnames.ora files typically connect using the ORACLE_SID, but if you have an entry in the listener.ora and the database auto-registers you will have two services that can handle a connection to the ORACLE_SID.

It seems it is not possible to specify an SDU size when auto-registering, so we need a way to ensure that the connection uses the one defined in the listener.ora and not the auto-registered one.

Specifying a GLOBAL_DBNAME in the SID description also helps, e.g.

     (SID_DESC =
     (SDU = 32142)
     (SID_NAME = MYDB)
     (GLOBAL_DBNAME = MYDB)
     (ORACLE_HOME = /oracle/test/9.2.0)
)

The GLOBAL_DBNAME of the auto-registered entry is usually .world as it tags the domain on the end.

tnsnames.ora
The final piece in the jigsaw is the tnsnames.ora, in this case on the application server in the /opt/bin/ihsdevlp directory, i.e. the tnsnames.ora used by the forms servers to connect to the database.

Simply add the SDU in here to correspond to that in the listener.ora and connect to a SERVICE_NAME rather than ORACLE_SID you’re in business.

MYDB=
     (DESCRIPTION=
          (SDU=32142)
          (ADDRESS=
          (PROTOCOL=TCP)
          (HOST=MYSERVER)
          (PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=MYDB)))

Note that SERVICE_NAME should correspond to whatever you defined GLOBAL_NAME to be listener.ora.

If you’ve set it up properly application connections to the database should show the SDU settings when you do a ps -ef | grep oracleMYDB:

oracle 21225 1 0 12:58:37 ?    0:41 oracleMYDB (DESCRIPTION=(LOCAL=NO)(SDU=32142))