Performance Tuning in OBIEE

Performance has always been an issue in all the OBIEE implementations I have seen till date. Below are the learnings from my experience.

1. Keep the number of init blocks / session variables to the minimum.

2. During development the loglevel for a user can be at any number, but once in production make the loglevel to 0. Logging hurts the query response time.

3. In connection Pool we have to take care to set the Maximum connections to the right value.
Maximum connections to the dashboards = 10% of simultaneous users X number of requests on dashboard. But total connections have to be less than 800.
For the connection pool dedicated to an initialization block it should be the number of concurrent users.

4. Specify Driving table: Driving table can be specified when creating logical table joins. Making the table with less number of records as the driving table will lead to query optimization.

5. VIRTUAL_TABLE_PAGE_SIZE: This is a parameter in NQSConfig.ini file. Operations like Sort, Join, unions and fetch require lot of memory. OBIEE users virtual table mechanism to manage this. When data exceeds the VIRTUAL_TABLE_PAGE_SIZE, then remaining data is placed in temp files, and brought to the virtual table as processing continues. When this paramter value is increase it will reduce the number of I/O operations. Default value is 128 Kb.

6. NULL_VALUES_SORT_FIRST: This is a parameter in NQSConfig.ini file. This specifies if NULL values are sorted before other values (ON) or after (OFF). The value of of this parameter should conform to the underlying database. Default value is NULL_VALUES_SORT_FIRST = OFF. If there are multiple underlying databases that sort NULL values differently, set the value to correspond to the database that is used the most in queries.

7.I used the CURSOR_SHARING parameter. CURSOR_SHARING is an init.ora parameter which decides whether a SQL send from user is a candidate for fresh parsing or will use an existing plan.
In the init blocks, as the second init block I added another init block.
Added the statement ALTER SESSION SET CURSOR_SHARING = SIMILAR and initialized a dummy variable.
CURSOR_SHARING=SIMILAR significantly reduces the number of plans in shared pool, and improves performance.

This will be learning for ever, so will keep updating this article when ever I learn something new.