This post describes a few tips and things to keep in mind for OBIEE Performance Tuning.
Be Proactive when possible
The need to performance tune can be proactive (tune before a major issue arises) or reactive (tune after a problem is reported by users for example). It is best to be proactive – so performance tuning should be built into your OBIEE maintenance schedule. For example, OBIEE’s Usage Tracking functionality should be used regularly to identify reports whose performance can be improved and then performance steps should be carried out on the worst performers.
Iterative Process – change one thing or set of things at a time
One of the first things to keep in mind is that performance tuning is an iterative process. And there is typically no one silver bullet that will resolve all your performance problems. You may need to analyze and make changes to multiple parts of the system, but you want to make the changes methodically. It is best to change one parameter or setting at the same time (or one related set of parameters). Adjust and test the settings for that one parameter/setting (or set of parameters) before moving on to another. If you change too much at one time, you may have a difficulty determining what is helping from what is hurting your efforts.
Fix user complaints first, worst performers next, and then the next bad performers down the list
Another thing to keep in mind, tune what users are reporting first, then tune the worst problems second, then move on to the next.
Team Effort – problem could be anywhere along the technology stack
Performance problems could be anywhere along the technology stack:
• OBIEE
• Database
• Server
• Network
Due to that span of technology, performance tuning is a team effort. OBIEE Admins and Developers, DBAs, and ETL Developers can all be key to solving performance issues.
Logs from all components may need to be reviewed depending on the scenario.
Try to isolate or narrow-down the source of the problem
For example, run the report SQL directly on the database and see if you have the same problem. If there is no issue when run directly on that the database, then you have eliminated the database as the problem.
Determine if other applications have been also been experiencing slowness which could indicate the possibility of a network problem.
If your users have reported an issue, then you need to get as much details as possible about the performance problems they are experiencing. When did this start happening? Is it just one report or many? Is it localized to one business area or multiple? Is it all the time or sometimes? Knowing this will help you to know where to focus.
Other questions to ask as you try to identify the source of the problem include but not limited to:
Has anything changed? If reports were running fine, but are now slow, the first thing to ask is …
When the issue start? Determining exactly when it started might be helpful when correlating with other system or company activity)
What has changed recently? Has there been any system changes, data changes, database updates, network changes, etc. (even if they seem unrelated)? For example, rolling into a new calendar year will cause new “Year” value(s) to be included in the data and can impact performance if statistics are not gathered.
Is there a possibility that an index was dropped and not recreated as expected?
Use OBIEE’s Usage Tracking information to analyze specific reports, analyze long running reports, or frequently run reports. You will want to capture and analyze the SQL from these reports to determine what can be done to improve their performance.
Database
DBAs can monitor the system in real-time, use various tools, or review logs for information that can be helpful in the tuning effort. Tools such as Oracle Enterprise Manager (EM) or SQL Tuning Advisor can be used to identify, analyze and tune high-load SQL.
OBIEE Usage Tracking can also be used to identify high-load SQL.
Without getting into much detail, these are some database features that could be used to help improve performance:
• Gather Statistics
• Results Cache database feature
• Partitioning
Servers
The System Admins can monitor the server resources to determine if there is an issue there.
• Use fast disk for the OBIEE cache and/or temporary files.
OBIEE-specific performance tuning tips
• OBIEE Caching
Are the tables being used set to cacheable?
Is caching turned on at the application level?
You may consider seeding the cache daily.
CACHE Settings:
o MAX_ROWS_PER_CACHE_ENTRY
o MAX_CACHE_ENTRY_SIZE
o MAX_CACHE_ENTRIES
o ——————-
o USE_ADVANCED_HIT_DETECTION
• Use Aggregation: Aggregate data when applicable
o You can use Aggregate tables or materialized views to realize this benefit.
o Aggregate Fact tables and corresponding Aggregate Dimensions.
o Make sure aggregation rules are applied to Fact table measures.
o Don’t necessarily merge all measures into a single fact.
• Joins and Indexes
o Do not create unnecessary joins.
o Verify that the joins on the tables being investigated are appropriate.
o Performance Indexing could be helpful. Again, this is an iterative process.
• Prompts and Filters
o Use LOV tables to drive prompt values when possible, instead of building prompts from large transactional data tables.
o Force filter selection / entry by making prompt values required. Do not allow open ended run of reports.
• Filter out unneeded data. If there is a significant amount of data that is not being used in one or more tables (especially if they are frequently used), then that data should be filtered out by the ETL before it gets joined in SQL, and then has to be filtered out in the RPD or at the report level.
• Enter the “Number of Elements at this level” value in the logical level in hierarchies.
• Also ensure that all logical level keys are unique.
• Avoid function in the where clause when possible.
• Be careful of sub-queries.
• Check out the features of the OBIEE Performance Monitor
http://server:port/analytics/saw.dll?Perfmon (enter your OBI server and port)
• When possible, do comparison analysis to determine for example, why is this report running fine, but this other seemingly similar report is not.
• Use fast disk for the OBIEE cache and/or temporary files.
Sometimes a complete overhaul might be required
Review the users’ workflow and determine if new and improved queries can be written or if the number of queries can be reduced.
Present information from a summary level first, and then provide increasing levels of details as requested by users through drill down or navigation. Basically, present detailed information only when necessary, and minimize the amount of detail provided at a time by filtering on user selections.
Oracle’s OBIEE Performance Tuning Guide
Apply recommendations from the “Best Practices Guide for Infrastructure Tuning Oracle® Business Intelligence Enterprise Edition 11g Release”. I would recommend applying 1 – 3 changes or set of changes at a time; don’t apply everything at the same time because if there is a problem, it will be more difficult to determine which change caused it.
https://blogs.oracle.com/proactivesupportEPM/entry/wp_obiee_tuning_guide
The Data Community 