Kumar Kambam’s OBIEE Blog

Intelligence on Oracle Business Intelligence

OBI Apps Informatica Performance Tuning – Optimizing SIL Read Throughputs – Teradata Response Buffer Size Optimization

Teradata Response Buffer Size

For the reader connection to source data from a Teradata database, ODBC connection is used out of the box. There is another option using piped FastExport utility connection with which there has been mixed success and will be discussed in a later blog post. OBDC can be tuned to achieve better performance in read throughput by changing the response buffer size. MaxRespSize is used to change the value in ODBC.ini. The default is 8192. The max value that can be specified is 1048576. From experience at a client site, simply specifying the max value did not help in improving the throughput. Increase the value systematically to determine the sweet spot for a network. The graph bellow illustrates throughputs achieved at different MaxRespSize values

image

Driver=/usr/odbc/drivers/tdata.so

Description=Teradata Linux 64-bit

DBCName=DWTESTcop1.XXXX.COM

SessionMode=Teradata

StCheckLevel=0

LastUser=

Username=

Password=

Database=

DefaultDatabase=

OutPutAsResultSet=Yes

MaxRespSize=8192

Advertisements

September 23, 2010 Posted by | OBI Apps Performance Tuning, OBIEE Performance Tuning, Uncategorized | , , , , , | Leave a comment

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)

)

)

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

Connection Pools – Best Practices

Most of the times not much thought is given to defining connection pools while developing rpd. Improperly defined connection pool would affect the OBIEE performance and user experience. Here are some of the things to consider while defining connection pool.

  • Change the default maximum connections. The default is 10. Based on your system usage change the value that is more realistic to your system usage
  • Create a separate connection pool for execution of session variables
  • Create a separate connection pool for the execution of aggregate persistence wizard. Remember that you need to give the schema user owner credentials for this connection pool as the wizard creates and drops tables
  • If need be create a separate connection pool for VVIPs. You can control who gets to use the connection pool based on the connection pool permissions.

August 17, 2009 Posted by | Best Practices, OBIEE, OBIEE Performance Tuning | 2 Comments

OBIEE Performance Tuning Tip – Turn off Query Logging

 

Though query logging has immeasurable development value, do not use this for regular production users as the runtime logging cost is extremely high. Every log item is flushed to the disk, which in turn hurts query response. Also, note that the query log files are not created on per user or query basis, there is only one query log per OBIEE server and it would have exclusive lock on the log file, which kills concurrent performance.

On the other hand, usage tracking has a very low runtime cost and is preferred to monitor the queries being used.

January 19, 2009 Posted by | OBIEE Performance Tuning | , , , | 3 Comments