About Query Performance Tuning

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 Manage the Query Log.

  • Aggregate tables: It's extremely important to use aggregate tables to improve query performance. Aggregate tables contain precalculated summarizations of data. It's 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've been properly specified in the repository. See Managing Metadata Repositories for Oracle Analytics Server 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 doesn't improve performance for most ad-hoc analysis. See About the 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 Set 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 Scale Your Deployment.