Kumar Kambam’s OBIEE Blog

Intelligence on Oracle Business Intelligence

OBI Apps Informatica Performance Tuning – Optimizing SDE Read Throughputs – Oracle DB Network Optimization

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 =

(DESCRIPTION =

(SDU=32768)

(TDU=32768)

(ADDRESS =

(PROTOCOL= TCP)

(Host= CRMOLTP)

(Port= 1521))

(CONNECT_DATA =

(SID = CRMOLTP)

)

)

Advertisements

September 3, 2010 - Posted by | OBI Apps Performance Tuning, OBIEE Performance Tuning | , , , , , ,

1 Comment »

  1. Hello Kumar,

    I’ve almost forgotten about these TDU/SDU settings, as they aren’t commonly adjusted. What range of performance improvement have you seen by changing these settings?

    To clarify, would you recommend that these TDU/SDU settings be changed on the source/target database servers, or on the Informatica repository database server, or on the Oracle client of the Informatica server?

    Thanks,
    Steve

    Comment by Steve | September 14, 2010


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: