24 Diagnosing and Tuning Oracle Reports

As your reporting requests grow in size and complexity and your user base increases, you must consider streamlining your report's performance (or your report's execution time) as much as possible. This maximizes its reach and minimizes its delivery time. Consider the following essentials before you tune the performance of your reports:

  • Performance and the trade-offs that occur when improving both perceived and measurable performance.

  • Costs involved.

  • Computing environment's complexity.

Investigating some of these areas can result in significant performance improvements. Some may result in minor performance improvements and others may have no affect on the actual report performance but can improve the perceived performance. Perceived performance refers to events that contribute to the end result (measured in terms of the final output). See Section 24.6.1, "Fetching Ahead" for an example of perceived performance.

This chapter provides a number of guidelines and suggestions for good performance practices in building, implementing, and tuning individual reports. The suggestions given are general in nature and not all suggestions might apply to all cases. However, implementing some or all of the points in a given application environment should improve the performance of report execution (real and perceived).


This chapter does not address Oracle Reports deployment or scalability issues. For more information, refer to the Oracle Reports Services Scalability white paper on OTN (http://www.oracle.com/technetwork/middleware/reports/collateral-9i-10g-087783.html).

This chapter will help you look at your report in the broader context of:

  • The application requirements

  • The correctness of the underlying data model

  • The environment where this report will run (for example, client/server, the Web, or inside firewalls)

  • The degree of user interaction required

After identifying the context of your report, you can gear the tuning process towards optimizing and minimizing:

  • The calls to the data source

  • The amount of unnecessary formatting required for the layout

To achieve these objectives, you should focus your tuning on the following distinct aspects of your report:

  • Execution time. Determine where your report is spending a majority of its execution time. Once you have accomplished this, use one of several performance tools available: to evaluate the query, review database optimization, and examine for efficiency specific pieces of code used by the report.

  • Formatting and layout. Examine the formatting and layout of the report information.

  • Runtime parameters. Set runtime parameters to maximize performance and distribution of reports. See Section 24.6.2, "Bursting and Distribution" for information on how distribution maximizes your reports performance.

This chapter addresses these aspects in the following sections: