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
| Initialization 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;
OBIEE 11g Performance Presentation Settings
OBIEE 11g performance Settings
Oracle BI Presentation Services Component Tuning Guide
To achieve better performance with Presentation Services (OBIPS) component, the following parameters can be tuned instanceconfig.xml file
| Waiting time for results |
Controls how long the server waits for results after the initial request before returning the Searching page to the browser. It may be useful to set this value higher (such as 10 seconds) to avoid page refreshes if the majority of queries are not returning in 1 second. If running performance tests some test implementations behave properly only if this setting is set very high (such as 1000 seconds). <ServerInstance>
<Cursors>
<NewCursorWaitSeconds>10</NewCursorWaitSeconds>
</Cursors>
</ServerInstance>
| Catalog related tunable parameters |
There are several OBIPS web catalog related parameters available like Soft limit on when a lock is warned of being stale, Hard limit on when a lock is removed as stale, How many characters to use to hash usernames into sub directories etc
| <ServerInstance> <Catalog> <LockStaleSecsSoft>14400</LockStaleSecsSoft> <LockStaleSecsHard>14400</LockStaleSecsHard> <HashUserHomeDirectories>3</HashUserHomeDirectories> </Catalog> <BIEEHomeLists> <Enabled>false</Enabled> <CatalogSynhronizationFrequencyMinutes>30</CatalogSynhronizationFrequencyMinutes> </BIEEHomeLists> </ServerInstance> |
| Caching related tunable parameters |
There are several OBIPS cache related parameters for number of cache entries, expiry time, and algorithm to clean up the cache etc. <ServerInstance>
<Cache>
<CatalogXml> <!-- Remove from the cache everything older than N minutes --><MaxAgeMinutes>240</MaxAgeMinutes>
<MaxLastAccessedSeconds>14400</MaxLastAccessedSeconds>
</CatalogXml>
<Query>
<MaxEntries>10000</MaxEntries> <!-- AbsoluteMaxEntries is the enforced maximum number of entries. When this maximum is reached -->
<!-- subsequent queries will fail until the maximum is no longer exceeded. --><AbsoluteMaxEntries>20000</AbsoluteMaxEntries> <!-- CruiseEntries is amount of entries the OracleBI Presentation server tries to maintain in its cache. --> <CruiseEntries>3000</CruiseEntries> <!-- Forces the cache to attempt to remove an old entry when MaxEntries is exceeded. --> <ForceLRU>true</ForceLRU>
</Query>
<Accounts> <!-- Cleanup this cache every N minutes. --><CleanupFrequencyMinutes>14400</CleanupFrequencyMinutes>
</Accounts>
<AccountIndex> <!-- Cleanup this cache every N minutes. --><CleanupFrequencyMinutes>14400</CleanupFrequencyMinutes>
| Charting thread related tunable parameters |
Number of charting threads and maximum number of jobs allowed in the queue can be tuned for performance when the dashboards have several charts: <ServerInstance>
<ThreadPoolDefaults>
<ChartThreadPool>
<MaxQueue>2048</MaxQueue>
<MaxThreads>32</MaxThreads>
</ChartThreadPool>
</ThreadPoolDefaults>
</ServerInstance> MaxQueue: Specifies the maximum number of jobs allowed in the queue. On 64bit OS, the default value is low so you may need to set it to 2048. MaxThreads: Specifies the maximum number of threads. Tip: [default is 8 i.e. numProcessors() * 8]
OBIEE 11g Enterprise Manager Too Slow
OBIEE 11g: Slow Response when Clicking on Coreapplication in Enterprise
Log into Linux / Unix / Windows Machine
- Delete Logs from Following Directories
$MiddlewareHome/user_projects/domains/bifoundation_domain/servers/bi_server1/logs
$MiddlewareHome/user_projects/domains/bifoundation_domain/servers/AdminServer/logs
- Stop all the OBIEE Services
Remove / Delete all Log files and Restart the SERVICES
Remove all logs log00001 etc
- Start all the OBIEE Services
