22 Oracle Business Intelligence Performance Tuning

This chapter describes tips for tuning Oracle Business Intelligence performance. It contains the following sections:

• Section 22.1, "About Oracle Business Intelligence"

• Section 22.2, "Oracle BI Server Query Performance Tuning"

• Section 22.3, "Oracle BI Server Query Cache Performance Tuning"

• Section 22.4, "Oracle BI Web Client Performance Tuning"

22.1 About Oracle Business Intelligence

Oracle Business Intelligence (BI) Enterprise Edition (or Oracle Business Intelligence) provides a full range of business intelligence capabilities that collects up-to-date data from the organization, presents the data in easy-to-understand formats (such as tables and graphs), and delivers the data quickly to the members of the organization.

These capabilities enable the organization to make better decisions, take informed actions, and implement more-efficient business processes.

22.2 Oracle BI Server Query Performance Tuning

This section describes some important considerations for improving query performance with the Oracle BI Server.

For detailed information on BI performance tuning, see "Managing Performance Tuning and Query Caching" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

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" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about configuring query logging on the system.

• 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 Oracle Fusion Middleware 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" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about query caching concepts and setup.

• 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" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.

• 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 "NQSConfig.INI File Configuration Settings" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.

22.3 Oracle BI Server Query Cache Performance Tuning

You can configure the Oracle BI Server to maintain a local, disk-based cache of query result sets (query cache). The query cache allows the Oracle BI Server to satisfy many subsequent query requests without having to access back-end data sources (such as Oracle or DB2). This reduction in communication costs can dramatically decrease query response time. See "About the Oracle BI Server Query Cache" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

22.4 Oracle BI Web Client Performance Tuning

You can improve the performance of the Oracle BI web client (UI) by configuring your Web server to serve up all static files, as well as enabling compression for both static and dynamic resources. BI 11g ships with WebLogic Server (WLS) serving as the default HTTP server for the BI web client. By allowing the Oracle HTTP Server (OHS) to proxy requests to WLS instead, you may see an improvement in BI Web Client performance. See "Improving Oracle BI Web Client Performance" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.