Performance Tuning and Caching
Where performance is won
Performance in Oracle BI is a layered problem. At the source, indexes, partitioning, and well-designed aggregate tables matter. At the BI Server, aggregate navigation, caching, and efficient logical SQL matter. At the presentation layer, limiting columns and rows, avoiding unnecessary views, and using prompts wisely matter. Tuning starts with measuring: use query logging and statistics to find slow logical queries, then inspect the generated physical SQL.
Caching mechanics
The BI Server caches query results so repeated identical logical queries return quickly. Cache entries are keyed on the logical query, the physical query, and relevant session context, so a query with a different session variable may miss the cache. Admins can enable or disable caching per physical table, purge cache entries, and set cache persistence. Aggregate navigation complements caching by routing a query to a pre-aggregated table when one can satisfy it. Event polling tables let the system detect data changes and invalidate cache.
Traps to watch
A frequent trap is assuming caching always helps - queries with volatile session variables or current-time functions may not be cacheable. Another is confusing cache purging with aggregate refresh: purging cache does not rebuild aggregates. Questions may test that caching occurs in the BI Server, that cache hits depend on matching the full logical query, and that disabling cache on a physical table affects queries touching it. Also watch for the trap that more aggregate tables always help - they add maintenance cost.
Rule of thumb
Before adding hardware, check three things: is the physical SQL efficient, can an aggregate satisfy the query, and is the cache being hit? Fix them in that order.