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.
For global configuration on the client side, configure the
DEFAULT_SDU_SIZEparameter in the
sqlnet.orafile: 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.