Best Practices to Improve Query performance In OBIEE

Hi all,

Got fed up of this question?? Yes, me too.. So penning down some of the recommended practices to make better your query performance.

1. Dashboard should be as interactive as possible: column selectors, drill-down, guided navigation… Interactivity is one of the best assets of Oracle BI. Use it.
2. Check if time is spent on BI Server or database (response time and physical query duration versus compilation time). Normally, time spent on BI Server should not exceed few seconds. Otherwise, analyze the steps done on BI Server to find the cause
3. Are all tables included in this query really necessary? Do we have tables that are joined but are not included in select clause and do not have filters applied (real filters, not join conditions)?
4. If optimizing the SQL is not enough, look with a DBA at execution plan and find out the root cause of performance issue. Globally there are mainly four ways to improve performance at this point:
• Reducing volume of IOs by improving data access path.
• Reducing volume of IOs by reducing the volume of data read.
• Review the filters applied.
• Increasing parallelism (number of thread used to read big tables)
• Improving IO speed (hardware improvement, in-memory...)
5. Tend to star data model in repository
6. Use hints in physical layer of the repository
7. Use fragmentation
8. Use aggregation
9. Set the no. of elements
10. Use the where clause in LTS if possible
11. Apply index on column from DB side only
12. Get long running physical queries, check explain plan and make change accordingly to DB tables or from OBIEE.
13. Try to use cache seeding wherever possible
14. Implement data level security to lower the data set pulled from DB
15. Reconfirm that aggregate/fragmented tables are getting used properly
16. Various configuration options can be used to limit the maximum number of rows that can be downloaded, processed, rendered, included in mail, etc.

That's it what I can think of. There can be many more, please feel free to add in comment section.

Happy blogging.. :-)