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
Description=Teradata Linux 64-bit
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):
(ADDRESS = (PROTOCOL = TCP)(HOST = crmoltp.xyzcompany.com)(PORT = 1521))
Server Side Configuration:
(GLOBAL_DBNAME = CRMOLTP)
(ORACLE_HOME = /u01crmoltp/oracle/product/10.2.0)
(SID_NAME = CRMOLTP)
(SID = CRMOLTP)
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.
When it comes to migrating repository file between environments (Dev – TEST-Prod), one of the common questions from OBIEE environment administrators is if there is a way to change the connection information without having to change them manually in all the places.
Creating repository variables for DSN and DSN Username solves the issue to an extent. However, imagine having to deal with changing multiple DSNs and their respective usernames. Moreover, there is the setting password to the DSN username. One way to automate the setting of the variables is to store the values in a file and set them via admin tool command line mode.
First, define repository variables that can be used in the connection pool.
Reference the DSN information in the connection pool.
Now create a control file SetVariables.txt with the environment specific values.
‘To Open rpd – Open <rpdname> <Administrator User> <administrator password>
Open YourRpd.rpd Administrator SADMIN
‘Setting OLAP DSN variable
SetProperty “Variable” “OLAP_DSN” Initializer ” ‘QA_DSN’ ”
‘ Setting OLAP DSN Username variable
SetProperty “Variable” “OLAP_DSN_USER” Initializer ” ‘QA_User’ ”
‘Setting OLAP DSN User QA database password
SetProperty “Connection Pool” “AppDW”.”Connection Pool” “Password” “QAPassword”
Create one per environment.
In the command line run the following command:
AdminTool.exe /command SetVariables_QA.txt
And the repository is now prepped for the other environment (QA in this case).
Repeatedly customers pose the question – OBIEE cache is enabled, but why is the query not cached? The reason why the queries are not cached can be of many reasons. Some of the reasons are:
Non-cacheable SQL function: If a request contains certain SQL functions, OBIEE will not cache the query. The functions are CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, RAND, POPULATE. OBIEE will also not cache queries that contain parameter markers.
Non-cacheable Table: Physical tables in the OBIEE repository can be marked ‘non-cacheable’. If a query makes a reference to a table that has been marked as non-cacheable, then the results are not cached even if all other tables are marked as cacheable.
Query got a cache hit: In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache. Note: The only exception is the query hits that are aggregate “roll-up” hits, will be added to the cache if the nqsconfig.ini parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to Yes.
Caching is not configured: Caching is not enabled in NQSConfig.ini file.
Result set too big: The query result set may have too many rows, or may consume too many bytes. The row-count limitation is controlled by the MAX_ROWS_PER_CACHE_ENTRY nqsconfig.ini parameter. The default is 100,000 rows. The query result set max-bytes is controlled by the MAX_CACHE_ENTRY_SIZE nqsconfig.ini parameter. The default value is 1 MB. Note: the 1MB default is fairly small. Data typically becomes “bigger” when it enters OBIEE. This is primarily due to Unicode expansion of strings (a 2x or 4x multiplier). In addition to Unicode expansion, rows also get wider due to : (1) column alignment (typically double-word alignment), (2) nullable column representation, and (3) pad bytes.
Bad cache configuration: This should be rare, but if the MAX_CACHE_ENTRY_SIZE parameter is bigger than the DATA_STORAGE_PATHS specified capacity, then nothing can possibly be added to the cache.
Query execution is cancelled: If the query is cancelled from the presentation server or if a timeout has occurred, cache is not created.
OBIEE Server is clustered: Only the queries that fall under “Cache Seeding” family are propagated throughout the cluster. Other queries are stored locally. If a query is generated using OBIEE Server node 1, the cache is created on OBIEE Server node 1 and is not propagated to OBIEE Server node 2
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.
I was reading the initial reviews of Windows 7 and how good it is and so on when it said somewhere that Microsoft is going to release the beta version for general public to try on Friday. I immediately wanted to get my hands on it for not to see how it works, but out of curiosity to see if, Windows 7 can tango with OBIEE 10.1.3.4 (the latest and greatest). Well, after all someone had to do it!
After the usual Microsoft fiasco, I was finally able to download both the 32 bits and 64 bits of Windows 7.
Upon successful installation of Windows 7 32 bits, I could not wait to install the Java SDK and launch the setup.exe. Soon after, I was hit with a disappointment – Oracle Business Intelligence is not supported on this Windows version. After all, Windows 7 is a leaner and meaner Vista….
Yes, after all Windows 7 is a leaner and meaner Vista, so I tried to install it in compatibility mode.
Right click on “setup.exe”. In the setup properties, click on “Change settings for all users” the in the new window, check the check box “Run the program in compatibility mode for” and choose Windows Vista in the drop down.
Click ok and start setup.exe after the usual selections and wait at 100% forever, I was presented with the following screens.
Now to see if the components have started….
Oc4j seems to start…
Services seem to start…
The connection to presentation services was successful
The log on was successful and the dashboards and answers were working just fine.
OBIEE seems to work just fine on Windows 7 Beta build 7000 32 bits. I would not really expect a true supported version of OBIEE until after the official release of Windows 7.
I will install Windows 7 64 bits later to “test” OBIEE on it and will let you know its outcome.