Introduction

On one of the projects there is a performance dip between Midnight and 05:00 and I have been asked to investigate the environment.  The Oracle database I am working with is a 920.6 32 bit version on HP UNIX.  I have used Stats pack as tool to do analyses. These stats packs are running in 15 minutes intervals in the database. During the intervals at night the top wait events are very often:

Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
SQL*Net more data to client                        77,119          74    31.11
CPU time                                                           63    26.36

The application connecting  to the database  comes in via an application server with EAI Tibco software via Java.

Since we suspect that there might be an issue on the network level, and after searching the web following approach is chosen for now:

According to the information found on  the Web (still grateful  of people sharing their experiences in blogs) this wait event might tend to network latency and there is an interesting option to tweak the package size in the communication between Listener and Client by tweaking the SDU size. As a default in the communication this package size is 2K by and it can be tweaked up to 32K.

In a Database – Client   environment this could be implemented in the following way if BOTH database environment and the client will set the SDU size. Hmm let me say that one more time. It needs 2 b done on BOTH sides. Heard about nasty things  that could happen if the setting is not in sync between the two.

On the Client side you will either set the tnsnames.ora  or set/chance your Java configuration or you alter your sqlnet.ora.

sqlnet.ora:  For global configuration on the client side, configure the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file:
DEFAULT_SDU_SIZE=32767
tnsnames.ora:
MYDB1 = (DESCRIPTION =
(SDU=32768)
(ADDRESS = (PROTOCOL = TCP)(HOST = MyHost1-vip)(PORT = 33000))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = MYDB1)
) )
Java: 
If your application is connecting through Java ( jdbc) )  this should pretty much look like this:
Jdbc:
jdbc:oracle:thin:@(DESCRIPTION=(SDU=32768)(ADDRESS=(PROTOCOL=TCP)(HOST= MyHost1-vip)(PORT=33000))(CONNECT_DATA (SID=MYDB1)(SERVER=DEDICATED))

Server Side configuration :On your database server  actually you also have two options .

Either:
For global configuration, configure the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file: 
DEFAULT_SDU_SIZE=32767

or

listener.ora
LISTENER_MYDB=
  (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=MyHost1-vip)(PORT=33000))
  )
 SID_LIST_LISTENER_MYDB=
  (SID_LIST=
     (SID_DESC=
     (SDU=32768)
     (ORACLE_HOME= /opt/oracle/product/11203_ee_64/db )
     (SID_NAME= MYDB1)
     )
)

To me at the moment it is still unclear If the Customer really will follow these suggestions. So maybe a follow-up will have to be done on this post.

For now I thought this is worth sharing.

Happy reading,

Mathijs