OBIEE 11g performance Settings BI Server

OBIEE 11g performance Settings

Oracle BI Server Component Tuning Guide


Following are the important parameters to tune in NQSConfig.INI

file and will increase the performance of the BI system under high users load:

Initialization blocks

Initialization blocks are defined in repository and are of two types, catalog and session. Catalog Init Blocks are invoked once in the life time of server. Below discussion pertains to session Init Blocks.

Deferred mode

It is recommended to enable deferred mode for Init Blocks wherever possible. Deferred mode or lazy loading enables Init Blocks to be executed when needed. If an Init Block were not set for deferred mode, it would get executed per session irrespective of its need.
There are three types of Init Blocks which cannot be deferred. 1. Row-wise Init Blocks which do not have target variables. 2. Security related session variable Init Blocks. 3. Init Blocks which have other non-deferred dependent Init Blocks.

Number of Init Blocks

For a given session, Init Block queries are executed serially and represent the per session memory costs. Be judicious in creating Init Blocks. Verify that the same Init Block query is not already being used in some other Init Block. Verify that cache is enabled for the Init Block and is being utilized

Init Blocks Cache 

The number of Init Block result sets that are cached with respect to row-wise initialization, can be tuned.  NQSConfig.INI parameter:
[ SERVER ]
INIT_BLOCK_CACHE_ENTRIES = 5000

Init block connection pool and location of data source

As a practice, allocate a separate DB connection pool for Init Blocks in the OBIS repository. Init Block query response time will be high if the connection pool points to a remote database.

Database connection pool tuning

The maximum connection size needs to be set in the repository for each DB connection pool. Maximum connections parameter in each connection pool of the repository should be set to a very large value. It controls the number connections which can be made by BI server to the database.
Use OBIS performance counters in EM to determine if more or fewer DB connections are required. For a simple sizing calculation, let’s assume there are peak N users concurrently downloading dashboard pages. On average, each dashboard page executes L logical queries. On average, each logical query executes P physical queries. Then the number of DB connections required for this load would be N * L * P. If fewer connections are specified, then response times will increase.

Data Mart Automation performance tuning in repository

The following parameters in the OBIS repository can be tuned to achieve better aggregate creation time:
 MIN_BULK_FETCH_BUFFER_SIZE (default value 32768) and MAX_BULK_FETCH_BUFFER_SIZE (default value 327680) needs to be set to a higher value i.e. 5000000.
Tip: Based on your data source used, tune these above values in DBFeatures.INI file.

Tuning of OBI Server session and threads

 Maximum number of concurrent client connections (sessions) to OBI server can be tuned.  Each client request to OBI Server uses one SERVER_THREAD.
 Each SERVER_THREAD uses 0 or more DB_GATEWAY_THREADs depending upon the number of DB queries executed.
 NQSConfig.INI parameters to be tuned:
[ SERVER ]
MAX_SESSION_LIMIT=5000
SERVER_THREAD_RANGE = 40-260;
DB_GATEWAY_THREAD_RANGE = 50-520;

Query plan caching

 When the Query Plan cache is hit : o It eliminates query parsing time. o It increases scalability due to less lock contention.  Never set the query plan cache size to 0. Doing so may cause Result Cache misses.
 NQSConfig.INI parameters to be tuned:
[ CACHE ]
MAX_QUERY_PLAN_CACHE_ENTRIES = 1024; // default is 1024

Query Results Caching

 One of the main advantages of query caching is to improve apparent query performance. It might be valuable to seed the cache during off hours by running queries and hence causing the server to cache their results.  The number of cache entries and disk size limit for the cache can be configured
 This configuration parameters can be set through by Oracle Business Intelligence Enterprise Manager
 NQSConfig.INI parameters to be tuned :
[ CACHE ]
ENABLE = YES;
MAX_ROWS_PER_CACHE_ENTRY = 100000; # 0 is unlimited size
# This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager MAX_CACHE_ENTRY_SIZE = 20 MB; # This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager MAX_CACHE_ENTRIES = 10000;

Read-Only Mode

 Makes the repository read-only so that online updates cannot be made.  Increased scalability due to less lock contention
 NQSConfig.INI parameters to be tuned:
[ SERVER ]
# This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager READ_ONLY_MODE = YES;

Improve sort efficiency by increasing sort buffer

 It helps to have the sort directory on a fast disk (e.g. a RAM disk)  NQSConfig.INI parameters to be tuned:
[GENERAL]

Cluster aware Cache seeding (using nqCmd or iBot)

 Seeding one node propagates across cluster  Data from shared location is pulled into local cache location during every poll
 NQSConfig.INI parameters to be tuned:
[ CACHE ]
# This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager GLOBAL_CACHE_STORAGE_PATH = "<shared directory name>" SIZE;
MAX_GLOBAL_CACHE_ENTRIES = 1000;
CACHE_POLL_SECONDS = 300;