This section describes some important considerations for improving query performance with the Oracle BI Server.
The following list summarizes methods that you can use to improve query performance:
Tuning and indexing underlying databases: For Oracle BI Server database queries to return quickly, the underlying databases must be configured, tuned, and indexed correctly. Note that different database products have different tuning considerations.
If there are queries that return slowly from the underlying databases, then you can capture the SQL statements for the queries in the query log and provide them to the database administrator (DBA) for analysis. See Managing the Query Log.
Aggregate tables: It is extremely important to use aggregate tables to improve query performance. Aggregate tables contain precalculated summarizations of data. It is much faster to retrieve an answer from an aggregate table than to recompute the answer from thousands of rows of detail.
The Oracle BI Server uses aggregate tables automatically, if they have been properly specified in the repository. See Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for examples of setting up aggregate navigation.
Query caching: The Oracle BI Server can store query results for reuse by subsequent queries. Query caching can dramatically improve the apparent performance of the system for users, particularly for commonly used dashboards, but it does not improve performance for most ad-hoc analysis. See About the Oracle BI Server Query Cache.
Setting parameters in Fusion Middleware Control: You can set various performance configuration parameters using Fusion Middleware Control to improve system performance. See Setting Performance Parameters in Fusion Middleware Control.
Setting parameters in NQSConfig.INI: The NQSConfig.INI file contains additional configuration and tuning parameters for the Oracle BI Server, including parameters to configure disk space for temporary storage, set virtual table page sizes, and several other advanced configuration settings. See Configuration File Settings.
You can also improve the overall performance of the system by increasing throughput by scaling out system components. See Scaling Your Deployment.