OBIEE Performance:
Reason
for BI Projects Non-performance
•
Poor Report Performance
•
Using Analytical tool as reporting tool.
•
No Data Quality Processes
•
Has no reliable source of historical data
•
Manual, Error-Prone Processes
•
No Single Source of Truth for Data
•
No Clear Road Map for BI Implementation
•
Ignoring the True end users
•
Lack of User Adoption due to poor user interface
Stage
to decide on Performance
•
Based on business Requirement and Need
•
Not everything can run in matter of Seconds
•
Performance should always be before presentation in term of priority.
•
Sacrifices on presentation during the design of the project.
Classic
Cry’s for OBIEE users:
• Slow
login page to OBIEE
•
Even though report is showing few Rows, it’s taking for ever
•
Even if there are no users, System response is slower.
•
As concurrent users increases, system stops responding
•
How to trouble shoot OBIEEE performance problem.
•
Any OBIEE Performance checklist?
•
Does caching works?
•
In spite of cluster environment, server response is sporadic?
Things
that can help OBIEE performance:
• Connection
Pools
•
Setting the NQSConfig.ini parameters
•
Use of “filter By” Option compared to “Case When”
•
Always specify the number of elements per level. BI Server will use it to
identify aggregate tables and mini dimensions. It does not need to be accurate,
a rough estimate is fine.
•
Exposing materialized views in Physical layer
•
Deploying SSL will have a level of overhead on the overall performance
•
Formatting of reports has overhead on the system verse executing HTML based
reports only (i.e. Dashboards)
•
Turning off logging
•
Setting query limits
•
Pushing calculations to the database
• Using
database hints
•
Infrastructure sizing
•
Using aggregates
•
Constraining results using a WHERE clause
•
Limiting the number of initialization blocks
•
Caching (Last Resort)
•
Depending on your configuration, enable some parameters in database feature:
•
Always specify the content level in all logical table
•
Delete Unused Views from Presentation Layer
•
Do not overuse dashboard conditions, it has a cost on performance.
•
Dashboard should be as interactive as possible:
– Column selectors,
– Contextual drill-down,
– guided navigation
How
to troubleshoot performance issues:
• NqQuery.log
– At loglevel 5 this file can give a lot of information.
•
Usage Tracking - When you enable usage tracking, statistics for every query are
inserted into a database table or are written to a usage tracking log file.
•
If the performance problem is with a single report then NqQuery.log is more
useful than Usage tracking data.
•
NqQuery.log file shows Physical SQL Query, logical execution plan.
•
Usage tracking does not have this information.
•
Usage Tracking can be enabled all the time and can be stored in a table for
analysis.
•
Good way to confirm an issue with Presentation Services is to check if the the
query is fast in OBI ODBC client
Connection
pool and database:
• What
is Maximum Connections
– When the limit is reached, then the system waits for a connection to become
available.
•
How to decide on Connection Pool Number
– Maximum number of connections in a connection pool :
20% of
concurrent users * number of reports per dashboard.
•
How Connection Pool can improvise Performance
– Create dedicated connection pools for your Session Variables.
– Create dedicated connection pools for your report queries.
•
Always use OCI Call Interface in the rpd if your backend db is Oracle
– Using ODBC for the Oracle RDBMS Connection can degrade performance
•
DB tracing
– ODBC tracing is enabled to troubleshoot ODBC issues. This can cause
huge performance problems. This is not indicated in any OBI logs.
OBIEE
Performance checklist:
• Using
aggregates
–
Enabling OBI to generate queries against smaller, summarized tables
•
Constraining results using a WHERE clause
–
Limiting the rows returned from a data source
•
Caching
–
Fulfilling a query from a local cache as opposed to processing the query
through a data source
•
Limiting the number of initialization blocks
–
Initialization block queries are executed when OBI is started and when users
log in
•
Turning off logging in production
–
No overhead for OBI to generate log files
–
NqQuery.log – At loglevel 5 this file can give a lot of information
Setting
query limits
–
Enabling OBI to track and cancel runaway queries
•
Pushing calculations to the database
–
Automatically pushing certain operations to the database based on database
feature entries
•
Exposing materialized views in Physical layer
–
Guarantees to choose the most economical table source to satisfy a query
•
Using database hints
–
Forcing the database query optimizer to execute the statement in a more
efficient way
•
Network Issues
–
Any network issues. It may be necessary to enable network tracing tools, if you
suspect problems with slow network.
Other
Factors
–
Sizing of the OBI servers is an important part in implementations when the
number of users and the complexity of the dashboards/reports is significant. It
may be necessary to involve infrastructure Admin to get a sizing review for
large implementations.
–
The size of the RPD and webcatalog may affect start up times of the services.
It is recommended to clean up the RPD and webcatalog to remove redundant
objects.
–
Sometimes ODBC tracing is enabled to troubleshoot ODBC issues. This can cause
huge performance problems. This is not indicated in any OBI logs.
–
When analyzing the NqQuery.log, if the Physical SQL is taken a lot of time,
performance tuning needs to be done on the database.
–
Oracle Database features like proper indexing, partitioning and Star
Transformations can help performance to a great extent.
Repository
Consideration
–
The RPD should be designed according to best practices.
–
De-normalize dimensional objects, combine several dimensional attributes into
one flat table. This will help reduce joins.
–
Model your physical tables in a way that should help in creating simpler
Business Models.
–
Use Aggregate Tables when necessary to help better performance.
–
Aggregate tables will need additional ETL, storage and complex mapping efforts.
–
The size of the RPD may affect start up times of the services
•
Complex Calculations
–
It may be better to move complex calculations to ETL for performance reasons.
–
If customers have complex logic that involves complex SQL, it is a good idea to
consider pushing this logic to ETL rather than handling it in the RPD at the
run time
• Avoid
Cross-database joins
–
Cross-database joins or “federated queries” can be expensive in terms of
performance when compared to joins within the same database. For cross-database
joins, tables are normally loaded into memory, and then joined.
•
Caching (THE LAST RESORT)
–
Caching can be very helpful in alleviating performance issues for complex
queries that are reused many times.
–
iBots can be used to seed queries during off peak hours to achieve performance.
•
DBFeatures
–
If one of the dbfeatures is turned off, improper SQL may be generated by OBI.
–
Improper SQL may cause performance degradation
•
Control long running queries
–
Set certain timeout parameters in the RPD
Webcat/Report
consideration:
• Use
a default value for the prompts in dashboards.
–
This will make sure that the reports will return a smaller result set
–
If this is not possible to do due to the business requirements
•
Consider adding some links on the dashboard page for reports.
•
If the query ran quickly in the NqQuery.log, then Presentation services may be
taking more time to display the results.
–
If you have a significant number of Charts or Pivot table views for the
reports.
–
Changing the existing reports to Table view could help performance
•
Too many pivot tables and charts on a dashboard page can make the UI very slow
•
Check for hidden sections and guided navigations that will run always.
•
Make sure the logconfig.xml file is not modified to get enhanced logging. This
is not recommended in production systems.
Some
of the reasons caching doesn’t work:
SQL
request contains Non-cacheable element Current_Timestamp, Current_Time, Rand,
Populate, or a parameter marker then it is not added to the cache
•
Result set is too big. MAX_ROWS_PER_CACHE_ENTRY and MAX_CACHE_ENTRY_SIZE
parameters
in nqsconfig.ini limit the max rows and max size for cache
•
Oracle BI Server is clustered.
•
Caching cannot be used for constrained Prompts.
•
If session variables are used, caching will not work since this results in the
cache being specific to a user.
•
ORDER BY clause made up of columns in the select list. Queries that order by
columns not contained in the select list result in cache misses.
•
Cache entries become specific to users depending on the way data-level security
is setup in the RPD.
•
The logical query has an additional column as compared to the seeded query.
Setting
the NQSConfig.ini parameters
Setting
following NQSConfig.ini parameters that can affect OBI performance
–
SORT_MEMORY_SIZE: specifies the maximum amount of memory to be used for each
sort operation
–
SORT_BUFFER_INCREMENT_SIZE: specifies the increment by which the sort memory is
increased as more memory is needed
–
VIRTUAL_TABLE_PAGE_SIZE: specifies the size of a memory page for OBI internal
processing
•
There are some more parameters which can help/effect the performance
–
SERVER_THREAD_RANGE
–
SERVER_THREAD_STACK_SIZE
–
DB_GATEWAY_THREAD_STACK_SIZE
–
MAX_SESSION_LIMIT
–
MAX_REQUEST_PER_SESSION_LIMIT
–
BUFFER_POOL_SIZE
–
MAX_CACHE_ENTRY_SIZE
–
DATA_STORAGE_PATHS
–
INIT_BLOCK_CACHE_ENTRIES
Recommended
parameters in NQSConfig.ini
• INIT_BLOCK_CACHE_ENTRIES
= 5000
•
MAX_SESSION_LIMIT=5000
•
SERVER_THREAD_RANGE = 40-260;
•
DB_GATEWAY_THREAD_RANGE = 50-520;
•
MAX_QUERY_PLAN_CACHE_ENTRIES = 1024; // default is 1024
•
ENABLE = YES;
•
MAX_ROWS_PER_CACHE_ENTRY = 100000; # 0 is unlimited size
•
MAX_CACHE_ENTRY_SIZE = 20 MB;
•
MAX_CACHE_ENTRIES = 10000;
•
WORK_DIRECTORY_PATHS = "C:\Temp"; /* /dev/shm on Linux */
•
SORT_MEMORY_SIZE = 4 MB ;
•
SORT_BUFFER_INCREMENT_SIZE = 256 KB ;
•
GLOBAL_CACHE_STORAGE_PATH = ""
SIZE;
•
MAX_GLOBAL_CACHE_ENTRIES = 1000;
•
CACHE_POLL_SECONDS = 300;
Oracle
recommendations presentation server
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:
--replace
( with < and ) with >
(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]
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
--replace
( with < and ) with >
(ServerInstance)
(Catalog)
(LockStaleSecsSoft>14400
(LockStaleSecsHard>14400
(HashUserHomeDirectories>3
(/Catalog)
(BIEEHomeLists)
(Enabled)false(/Enabled)
(CatalogSynhronizationFrequencyMinutes)30(/CatalogSynhronizationFrequencyMinutes)
(/BIEEHomeLists)
(/ServerInstance)
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).
--replace
( with < and ) with >
(ServerInstance)
(Cursors)
(NewCursorWaitSeconds)10(/NewCursorWaitSeconds)
(/Cursors)
(/ServerInstance)
No comments: