Oracle DB Network Optimization
After exhausting query performance with viable indices, consider modifying Session Data Unit (SDU) and Transport Data Unit (TDU) parameters to increase session throughput. The default value of SDU and TDU is 2048 and the maximum is 32767. These can be set as global parameters in sqlnet.ora or for a particular descriptor in tnsnames.ora. They need to be set on both server and client. Consider setting these parameters in tnsnames.ora so that it affects only a particular connection descriptor that is used for Oracle DB server to Informatica server communication. The SDU and TDU parameters can be set higher depending on the network and memory. Get help from your Infrastructure team in determining the MSS (Maximum Segment Size) of the network. Ideally they should be in the multiple of MSS. In addition, the SDU should not be greater than TDU. Ideally, they should be the same. For slower networks, consider a lower value.
Client Side Configuration (Informatica Server):
tnsnames.ora:
CRMOLTP =
(DESCRIPTION =
(SDU=32768)
(TDU=32768)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = crmoltp.xyzcompany.com)(PORT = 1521))
Server Side Configuration:
listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU=32768)
(TDU=32768)
(GLOBAL_DBNAME = CRMOLTP)
(ORACLE_HOME = /u01crmoltp/oracle/product/10.2.0)
(SID_NAME = CRMOLTP)
)
)
tnsnames.ora:
CRMOLTP =
(SDU=32768)
(TDU=32768)
(DESCRIPTION =
(ADDRESS =
(PROTOCOL= TCP)
(Host= CRMOLTP)
(Port= 1521))
(CONNECT_DATA =
(SID = CRMOLTP)
)
)
