Skip Headers

Oracle® Application Server Reports Services Publishing Reports to the Web 10g (9.0.4) Part Number B13673-01 |
| Home | Contents | Index

# 20 Tuning Oracle Reports

As your reporting requests grow in size and complexity and your user base increases, you need to 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 proceed to tune Oracle 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 —behind-the-scenes— to the end-result (measured in terms of the final output). See 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 Oracle 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).

Note: This chapter does not address Oracle Reports deployment or scalability issues. Refer to the Oracle Application Server Reports Services Scalability white paper on OTN, (`http://otn.oracle.com`) for more information.

This chapter contains Methodology as the main section.

## 20.1 Methodology

You must not look at the report in isolation, but in the 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 before firewalls)

- The degree of user interaction required

After identifying the context, 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 two objectives, focus your tuning on the following distinct aspects of your report:

1. 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.

   See:

   - Performance Analysis Tools and

   - Accessing the Data

   for more information on how to minimize your report’s execution time.

2. Formatting and layout

   Examine the formatting and layout of the report information.

   See :

   - Formatting the Data and

   - General Layout Guidelines

   for more information on how to optimize your report’s formatting and layout, to yield maximum productivity.

3. Runtime parameters

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

   See:

   - Calling Oracle Reports from Forms and

   - Running the Report

   for more information on how to set / remove appropriate runtime parameters.

The following is the best way to go about improving performance:

- Make changes incrementally.

- Isolate performance bottlenecks.

- Optimize data access.

- Streamline formatting.

- Maximize runtime efficiency.

Changes made in one area can have a performance impact in another area.

### 20.1.1 Performance Analysis Tools

The first step towards tuning your report is determining where your report spends most of its execution time. Does it spend a large portion of the time retrieving the data, formatting the retrieved data, or waiting for run time resources/distribution? Even if your report has the most streamlined and tuned layout possible, it may be of little consequence if most of the time is spent in retrieving data, due to inefficient SQL.

#### 20.1.1.1 Report Trace

Setting the report tracing option produces a text file that describes the series of steps completed during the execution of the report. Tracing can be set to capture all events or just specific types of events. For example, you can trace just the SQL execution or just the layout and formatting. The trace file provides abundant information, which is useful not only for performance tuning but also for debugging reports.

Tracing can be set for various methods of report execution, such as, both `.rdf` and `.jsp` report definitions containing SQL and/or non-SQL data sources (for example, XML and Text pluggable data sources).

Generating a reports trace file

To turn tracing on, do one of the following:

- In Reports Builder:

  1. Choose Program >Tracing.

  2. Select the Trace Mode.

  3. Select appropriate Trace Options. The trace file now logs information for the entire Reports Builder session. Refer to Chapter 3, " Configuring OracleAS Reports Services", for more information on Trace Options.

Figure 20-1 Reports Builder

Description of the illustration tune_trace.gif

- In the `rwbuilder.conf` configuration file, specify:

      <trace traceFile="trace_file_name" traceOpts="trace_all"
      traceMode="trace_replace"/

  Note: The location of the trace file is relative to the Oracle Reports log directory (`ORACLE_HOME``\reports\log`) or absolute if a full path name is specified. If you do not specify a trace file name, the default name is `hostname``-rwbuilder.trc`.

- Specify tracing options via the Reports Builder built-in package (SRW) using `SRW.TRACE_START`, `SRW.TRACE_END`, `SRW.TRACE_ADD_OPTION`, `SRW.TRACE_REM_OPTION`.

- For the `rwbuilder` and `rwrun` executables, specify trace options on the command line:

      traceFile=trace_file_name traceOpts=trace_all traceMode=trace_replace

  Note: The location of the trace file is relative to the current working directory or absolute if a full path name is specified. Command line tracing options override the options in the `rwbuilder.conf` file.

- For the `rwserver` executable, set the trace options in the `servername``.conf` file. Separate trace files are generated for the server and the engine(s). Specify:

      <trace traceFile="trace_file_name" traceOpts="trace_all" traceMode="trace_replace"/

  Note: The location of the trace files is relative to the server log directory (`ORACLE_HOME``\reports\log)`. If you do not specify a trace file name, the default server trace file name is `serverName``.trc` and the default engine trace file name is `serverName``-``engineName``-``engineNo``.trc`.

- For `rwservlet`, set the trace options in the `rwservlet.properties` configuration file.

  Use a separate line for each option:

  `TRACEOPTS=TRACE_ALL`

  `TRACEFILE=rwservlet.trc`

  `TRACEMODE=TRACE_REPLACE`

  See Also: Chapter 3, " Configuring OracleAS Reports Services" For more information on OracleAS Reports Services Trace Options. Appendix A, " Command Line Options" For more information on report trace options.

Options can be combined on the command line. For example, the `TRACEOPTS=(TRACE_APP,TRACE_PRF)` means that the log file will contain information on all report objects and performance statistics.

Viewing the generated the trace file helps you determine the actual time spent in fetching data versus the time spent in formatting the report. This helps you to identify performance bottlenecks.

The following command line example generates a trace file, containing performance trace information, and replaces any previously existing trace file:

    rwrun module=emp.rdf userid=scott/tiger@orcl destype=file desformat=pdf desname=emp_pdf.pdf traceopts=trace_prf tracemode=trace_replace
    tracefile=emp_tr.txt

Following is the outline of the information output to the `emp_tr.txt` trace file.

Example 20-1 Reports Builder

    +------------------------------------------+
    | Report Builder Profiler statistics       |
    +------------------------------------------+
    Total Elapsed Time: 8.00 seconds
    Reports Time:    7.00 seconds (87.50% of TOTAL)
    ORACLE Time:     1.00 seconds (12.50% of TOTAL)
    UPI:             0.00 seconds
    SQL:             1.00 seconds
    TOTAL CPU Time used by process: N/A

Table 20-1 Reports Builder

Field | Description
--- | ---
Total Elapsed Time | Time spent in executing the report.
Reports Time | Time spent in formatting the retrieved data. Also displayed as a percentage of Total Elapsed Time.
ORACLE Time | Time spent in retrieving the data. Also displayed as a percentage of Total Elapsed Time.
UPI | SQL queries only. Time spent in establishing a database connection, then parsing and executing the SQL.
SQL | Time taken by the database server to fetch the data (percent of time spent executing `SRW.DO_SQL()` statements, `EXEC_SQL` statements, PL/SQL cursors, and so on.)

Note: If your data source is a non-SQL data source such as Text or an XML pluggable data source, the values for ORACLE Time, UPI, and SQL display as 0.

In Example 20-1, focus your tuning efforts on formatting (Reports Time) the data rather than on querying and fetching it.

#### 20.1.1.2 Efficient SQL

Oracle Reports uses SQL to retrieve data from the database.

Note: Oracle Reports uses SQL for non-PDS queries only.

Inefficient SQL can cripple performance especially in large reports. Thus, anyone tuning Oracle Reports must have a good working knowledge of SQL and understand how the database executes these statements. If you are less proficient in SQL, use the Data Wizard and Query Builder in the Reports Builder. However, the wizard cannot prevent inefficient SQL from being created. For example, SQL that does not use available indexes.

To tune your report's SQL, use the Oracle database's trace functionality. SQL tracing allows you to determine the SQL statement sent to the database as well as the time taken to parse, execute, and fetch data. Once a trace file is generated, use the TKPROF database utility to generate an EXPLAIN PLAN map. The EXPLAIN PLAN map graphically represents the execution plan used by Oracle Optimizer. For example, the Oracle Optimizer shows where full table scans have been used. This may prompt you to create an index on that table depending on the performance hit.

To turn on SQL tracing inside Reports Builder, add a report-level formula column named SQL_TRACE with the following code:

    SRW.DO_SQL(‘ALTER SESSION SET SQL_TRACE=TRUE’);
    return(1);

Note: You can also call SQL_TRACE using either a Before Report Form trigger, or a Before Parameter Form trigger.

The following EXPLAIN PLAN map was generated using the database’s SQL trace facility. Refer to the SQL Language Reference and Programming documentation for more information.

Example

The statement being executed is:

    SELECT e.ename, d.dname
    FROM emp e, dept d
    WHERE e.deptno(+) = d.deptno

The EXPLAIN PLAN generated is:

    OPERATION            OPTIONS     OBJECT_NAME   POSITION
    ------------------ ----------- --------------- --------
    SELECT STATEMENT
      MERGE JOIN     OUTER                                1
       SORT                  JOIN                         1
         TABLE ACCESS FULL                 DEPT           1
       SORT                  JOIN                         2
         TABLE ACCESS FULL                 EMP            1

When you tune data for Oracle Reports, understand that the Oracle RDBMS provides two optimizers: cost-based and rule-based. By default, the cost-based optimizer constructs an optimal execution plan geared towards throughput, i.e., process all rows accessed using minimal resources. You can influence the optimizer's choice by setting the optimizer approach and goal, and gathering statistics for cost-based optimization. While the cost-based optimizer removes most of the complexity involved in tuning SQL, understanding the distribution of the data and the optimizer rules allow you to choose the preferred method and gives you greater control over the execution plan. For example, in your SQL statement, you could do one of the following:

- Provide optimizer hints with the goal of best response time, i.e., process the first row accessed using minimal resources.

- Decide that an index is not needed.

  Note: For large queries, it is imperative to do one of the following: Activate the cost-based optimizer and gather statistics by using the `DBMS_STATS` package, the `COMPUTER STATISTICS` option, or the `ANALYZE` command. Optimize all SQL following the rules laid out by the rule-based optimizer.

The Oracle Application Server documentation provides more information on the database optimizer’s functionality.

#### 20.1.1.3 PL/SQL

Use the `ORA_PROF` built-in package to tune your report’s PL/SQL program units. The procedures, functions, and exceptions in the `ORA_PROF` built-in package allow you to track the amount of time that pieces of your code takes to run.

Example

    PROCEDURE timed_proc (test VARCHAR2) IS
    i PLS_INTEGER;
    BEGIN
    ORA_PROF.CREATE_TIMER('loop2');
    ORA_PROF.START_TIMER('loop2');
    ColorBand_Program_Unit;
    ORA_PROF.STOP_TIMER('loop2');
    TEXT_IO.PUTF('Loop executed in %s seconds.\n',
    ORA_PROF.ELAPSED_TIME('loop2'));
    ORA_PROF.DESTROY_TIMER('loop2');
    END;

This procedure creates a timer, starts it, runs a subprogram, stops the timer, and displays the time it took to run. It destroys the timer when finished.

Refer to Reports Builder online help for more information on the `ORA_PROF` built-in package.

Implement PL/SQL program units performing a significant amount of database operations as stored database procedures. Stored procedures run directly on the Oracle database and perform operations more quickly than local PL/SQL program units. Local PL/SQL program units use the Reports Builder’s PL/SQL parser, then the database’s SQL parser, and also include a network trip.

PL/SQL program units that do not perform any database operations should be coded as locally as possible using the Program Units node in the Object Navigator. Localizing the PL/ SQL program unit has a performance advantage over executing PL/SQL from an external PL/SQL library. Use external PL/SQL libraries only when the benefits of code sharing can be utilized.

The `SRW.DO_SQL()` built-in procedure should be used as sparingly as possible. Each call to the `SRW.DO_SQL()` built-in procedure necessitates parsing and binding the command and opening a new cursor like a normal query. Unlike a normal query, this operation will occur each time the object owning the `SRW.DO_SQL()` built-in procedure fires.

For example, a PL/SQL block in a formula column calls the `SRW.DO_SQL()` built-in procedure and the data model group returns 100 records. In this case, the parse/ bind/ create cursor operation occurs 100 times. Therefore, use the `SRW.DO_SQL()` built-in procedure for operations that cannot be performed using normal SQL (for example, to create a temporary table or any other form of DDL), and in places where it will be executed sparingly (for example, in triggers that are only fired once per report).

The primary reason to use the `SRW.DO_SQL()` built-in procedure is to perform DDL operations, such as creating or dropping temporary tables. For example, have the `SRW.DO_SQL()` built-in procedure to create a table. The table’s name is determined by a parameter entered in the Runtime Parameter Form.

Example

    SRW.DO_SQL (`CREATE TABLE' || :tname ||
    `(ACCOUNT NUMBER
    NOT NULL PRIMARY KEY, COMP NUMBER (10,2))');

#### 20.1.1.4 Java Stored Procedures

Java stored procedures enable you to implement business logic at the server level; thereby, improving application performance, scalability, and security. Oracle9i allows PL/SQL and Java stored procedures to be stored in the database. Typically, SQL programmers who want procedural extensions favor PL/SQL and Java programmers who want easy access to Oracle data favor Java. Although Java stored procedures offer extra flexibility, there is some overhead involved. Balance the trade off between performance and flexibility based on your individual needs.

Refer to the Oracle9i Java Stored Procedures Developer's Guide for more information on Java stored procedures.

#### 20.1.1.5 The Java Importer

Although Oracle PL/SQL provides a powerful and productive development environment, it is sometimes necessary to integrate with external application services and providers. As many of these external application services and providers are increasingly offering integration points in Java. Oracle Reports integrates with the Oracle Java Importer to facilitate the invocation of business logic contained in external middle-tier Java classes. The Java Importer declaratively creates a PL/SQL wrapper package for each class you select and exposes the methods identified in the class through PL/SQL functions and procedures. This allows you to instantiate, use, and destroy the Java object instances when the report is run. While this powerful extension insulates you from having to write Java code yourself, there is some overhead involved. Separate PL/SQL packages are generated for every class specified. The PL/SQL generator performs type translations when it generates the PL/SQL packages from the Java methods. Any time a Java object instance is created using the new function in the PL/SQL package and generated by the Java Importer, the result is stored in a variable of type JOBJECT. Java Object persistence must be carefully handled because accumulating large numbers of global references without removing them increases the JVM’s memory consumption.

### 20.1.2 Accessing the Data

If your performance measuring tools show that the report spends a large amount of time accessing data from the data source(s), you need to review the structure of the data and determine how the data is being used. Inefficient schema design has a dramatic affect on the performance of a report. For example, an overly normalized data model can result in many avoidable joins or queries.

#### 20.1.2.1 Non-SQL Data Sources

To publish data from any data source use the pluggable data source architecture in Oracle Reports. Out-of-the-box Oracle Reports supports non-SQL data sources, such as the XML, Text, and JDBC pluggable data sources. Both XML and Text pluggable data sources can be accessed through a remote URL (even across firewalls). If speed is a concern, download the data locally and use the local data stream rather than a remote URL. You can also specify the domains for which you can bypass a proxy server.

The XML pluggable data source supports runtime XML data validation. Select the Validate Data Source check box in the XML query wizard to activate runtime XML data validation. Selecting Validate Data Source ensures that the XML data is verified as it is fetched against the data definition specified in the DTD or in the XML schema. This is a very costly operation and proves to be useful only when you develop the report and not during production. You will see a noticeable performance difference when the XML data stream is very large.

You can specify either an XML schema or a DTD schema for the data definition. An XML schema forces type checking. Whereas, a DTD schema does not require type checking as all data is treated as strings.

Note: Ensure that the data types of the non-SQL sources match — columnwise.

You can also specify an extensible style sheet language (XSL) file for the XML data stream to convert it from any format into a simple row set/row data feed. It is better to have data in the correct format to start with, unless you need to apply the XSL at run time.

Pluggable Text data sources support the use of cell wrappers. This causes the file format level delimiter to be ignored for every field that has a wrapper defined. Avoid using cell wrappers unless really required.

The JDBC pluggable data source supports JDBC bridges, as well as, thick and thin JDBC drivers. Selecting the driver directly impacts the fetching of data. The choice depends on the application and the database being used. Using a native driver generally results in better performance.

#### 20.1.2.2 Database Indexes

Columns used in a SQL WHERE clause should be indexed. The impact of indexes used on columns in the master queries of a report are minor, as these queries access the database once. To improve performance significantly, indexes should be used on any linked columns in the detail query.

Note: Lack of appropriate indexes can result in many full-table scans and slows down performance.

#### 20.1.2.3 Calculations

Within a report (either through summary or formula columns) ensure that most of the calculations are performed by the data source. In case of SQL queries, calculations are performed on the database rather than on the data retrieved by the report. User-defined functions and procedures stored by the database can also be included in the query select list of an Oracle9i or a JDBC query. This is more efficient than using a local function, since the calculated data is returned as part of the result set from the database.

Example

The following PL/SQL function can be stored in the Oracle9i database:

    CREATE OR REPLACE FUNCTION CityState (

    p_location_id world_cities.location_id%TYPE)
    RETURN VARCHAR2 is
    v_result VARCHAR2(100);

    BEGIN

    SELECT city || ‘,‘||state

    INTO v_result
    FROM world_cities

    WHERE location_id = p_location_id;
    RETURN v_result;

    END CityState;

This function returns the city separated by a comma, a space, and the state. This formatting is done at the database level and passed back to the report to display.

In the report, the SQL query would look like:

    SELECT location_id, citystate(location_id)"City
    & State" FROM world_cities

The result would look like this:

    LOCATION_ID CITY & STATE
    ----------- -------------------------
              1 Redwood Shores, California
              2 Seattle, Washington
              3 Los Angeles, California
              4 New York, New York

#### 20.1.2.4 Redundant Data

A report’s query should ideally select only required columns and not unrequired columns (redundant query) as this affects performance. The fewer queries you have, the faster your report will run. Single-query data models execute more quickly than multi-query data models. However, situations can arise where a report not only needs to produce a different format for different users, but also needs to utilize different query statements. Although this can be achieved by producing two different reports, it may be desirable to have a single report for easier maintenance. In this instance, the redundant queries should be disabled using the `SRW.SET_MAXROW()` built-in procedure.

Example

The following code used in the Before Report trigger will disable, either Query_Emp or Query_Dept, depending on the user parameter:

    IF :Parameter_1 = 'A' then

    SRW.SET_MAXROW('Query_Emp',0);

    ELSE

    SRW.SET_MAXROW('Query_Dept',0);

    END IF;

Note: The only meaningful place to use the `SRW.SET_MAXROW()` built-in procedure is in the Before_Report trigger (after the query has been parsed). Calling the `SRW.SET_MAXROW()` built-in procedure after this point raises the `SRW.MAXROW_UNSET` built-in exception. The query will still be parsed and bound, but no data will be returned to the report.

You can define a query based either on an XML or a Text pluggable data source by selecting the fields to be used in the query (i.e., all available fields or a subset). If you must use a subset of the fields, do so at the query level using parameters, as opposed to fetching all the values and filtering them using a group filter or layout level format triggers.

#### 20.1.2.5 Break Groups

Limit the number of break groups to improve your report’s performance. Oracle Reports sets the break level for each column in the data model that has the break order property set except the lowest child group.

For a SQL query, Oracle Reports appends this as an extra column to the ORDER BY clause in the query. The fewer columns in the ORDER BY clause, the lesser the work the database has to do before returning the data in the required order. Creating a break group may render an ORDER BY clause redundant inspite of defining it as part of the query. Remove any such ORDER BY clauses as it requires extra processing by the database.

If your report requires the use of break groups, set the break order property for as few columns as possible. A break order column is indicated by a small arrow to the left of the column name in the group in the Reports Builder Data Model view. Each break group above the lowest child group of a query requires at least one column to have the break order property set. Removing the break order from columns where sorting is not required increases performance.

Limit break groups to a single column whenever possible. These columns should be as small as possible and be database columns (as opposed to summary or formula columns) wherever feasible. Both conditions help the local caching that Oracle Reports does, before the data is formatted for maximum efficiency. Clearly, these conditions cannot always be met but can increase efficiency whenever utilized.

#### 20.1.2.6 Group Filters

Group filters reduces the number of records retrieved from the data source. Filtering takes place after the query returns the data (from the data source) to Reports Builder. Even if the filter is defined to display only the top five records, the result set returned to reports will contain all the records returned by the query. Hence, it is more efficient to incorporate the group filter functionality into the query’s WHERE clause or into the maximum rows property, whenever possible. This restricts the data returned by the database.

#### 20.1.2.7 To Link or not to Link

There are a number of ways to create data models that include more than one table. Consider the standard case of the dept/emp join, with the requirement to create a report that lists all the employees in each department in the company. You can create either of the following:

- Single query

      SELECT d.dname, e.ename
      FROM emp e, dept d
      WHERE e.deptno(+) = d.deptno

- Two queries with a column link based on deptno

      SELECT deptno, dname FROM dept
      SELECT deptno, ename FROM emp

When you design the data model in the report, minimize the actual number of queries by using fewer large multi-table queries, rather than several simple single-table queries. Every time a query is run, Oracle Reports needs to parse, bind, and execute a cursor. A single query report returns all the required data in a single cursor, rather than many cursors. With master-detail queries, the detail query will be parsed, bound, and executed again for each master record retrieved. In this example, it is more efficient to merge the two queries and use break groups to create the master-detail effect.

Keep in mind that the larger and more complex a query gets, the more difficult it is to be maintained. You need to decide when to achieve the balance between performance and maintenance requirements.

### 20.1.3 Formatting the Data

After the data is retrieved from the data source, Oracle Reports generates the report layout and formats the output. The time taken for a paper layout depends on a number of factors, but generally comes down to:

- The work required to prevent an object from being overwritten by another object.

- The efficiency of any calculations or functions performed in the format triggers.

The rules for a Web layout are a little different as Oracle Reports does not:

- Own the Web page

- Control the rendering mechanism

It merely injects data into a regular JSP page.

#### 20.1.3.1 Paper Layout

When generating a default paper layout, Oracle Reports wraps a frame around virtually every object to prevent the objects from being overwritten, when the report is run. At runtime, every layout object (frames, fields, boilerplate, etc.,) is examined to determine the likelihood of that object being overwritten. In some situations (for example, boilerplate text column headings) when there is clearly no risk of the objects being overwritten, the immediately surrounding frame is removed. This reduces the number of objects that Oracle Reports must format and consequently improves performance.

Extra processing is required when an object has an undefined size (variable, expanding, or contracting either horizontally and / or vertically). In this case, Oracle Reports must determine the instance of the object's size, before formatting that object and those around it. There is no processing overhead involved for objects assigned a fixed size, as the size and positional relationships between the objects is known.

The following guidelines helps to improve performance when creating a paper layout:

- Make your non-graphical layout objects (for example, boilerplate text or fields with text) fixed in size by setting the Vertical Elasticity and Horizontal Elasticity properties of the field to Fixed. In particular, setting the size of repeating frames and their contents to fixed, improves performance. Variable (size) non-graphical objects require more processing overhead, because Reports Builder must determine their size before formatting them. However, the overhead for fixed non-graphical objects is less, since the additional processing is not required.

- Make your graphical layout objects (for example, images and graphs) variable in size by setting the Vertical Elasticity and Horizontal Elasticity properties of the objects to Variable. Fixed graphical objects require more processing overhead as their contents have to be scaled to fit. Variable objects grow or shrink with the contents eliminating the need for scaling.

- Make text fields span a line (maximum) and ensure that their contents fit within the specified width (for example, use the SUBSTR function). If a text field spans more than a line, Reports Builder must use its word wrapping algorithm to format that field. Ensuring the text field takes only one line to format avoids the processing overhead of the word wrapping algorithm.

- Minimize the use of different formatting attributes (for example, fonts) within the same field or boilerplate text, because it takes longer to format.

- Use the SUBSTR function in the report query to truncate the data at the database level, instead of truncating a character string from a field in the Report Builder layout.

- For paper layout only reports, `.rdf` and `.rep` files run faster than a`.jsp` file, because the serialized formats of a `.rdf` or a `.rep` file do not require parsing. Additionally, a `.rep` file runs faster than a `.rdf` file as it is optimized for the current platform.

##### 20.1.3.1.1 Format Triggers in a Paper Layout

Format triggers can dynamically disable, enable, and change the appearance of an object. Exercise caution when using them as they fire each time an instance of their associated object is produced and formatted (at runtime).

Consider the following example, on the use of format triggers:

A tabular report includes a single repeating frame that expands vertically and has the Page Protect property set to On. As the report is formatted, there is room for one more line at the bottom of the first page. Oracle Reports starts to format the next instance of the repeating frame and fires its associated format trigger. One of the objects inside the repeating frame is found to have expanded and this instance of the repeating frame is moved to the following page. The format trigger for the repeating frame is fired again. Although the repeating frame only appears once (at the top of the second page), the format trigger has fired twice. DML should not be performed in a format trigger, because you are not sure how many times the format trigger will fire for a particular object.

With the example above, had the format trigger contained an INSERT statement, then two rows of data would have been inserted.

Format triggers can be used against repeating frames to filter data. However, by introducing filtering at appropriate levels, you not only improve a report's performance but also reduce the complexity required for this type of a report.

Use the following filtering order whenever possible:

- Modify the SQL statement to prevent the data being returned from the server.

- Use the group filter to introduce filtering in the Data Model.

- Use return false inside the format trigger.

Format triggers should be placed at the highest level possible in the object/frame hierarchy so that the trigger fires at the lowest possible frequency. For example:

Figure 20-2 Format Triggers

Description of the illustration tune_formattriggers.gif

Maximize the efficiency of the code, whenever you define any triggers or PL/SQL program units within Oracle Reports. For example, to change the display attributes of a field dynamically to draw attention to values outside the norm, change the attributes using individual built-ins such as the `SRW.SET_TEXT_COLOR()` built-in procedure.

Refer to the PL/SQL User's Guide and Reference for general PL/SQL tuning issues.

Assigning a transparent border and fill pattern to layout objects (for example, frames and repeating frames) improves performance, as these objects are not rendered as a bitmap file.

#### 20.1.3.2 Web Layout and JSP Report Definition

In Oracle Reports, you can use your favorite Web authoring tool to design the static portion of your Web page and then use Reports Builder to insert the dynamic portion (data) into appropriate sections of the page. A poorly designed Web page impacts perceived performance. Alternatively, you can use pre-defined Oracle9i Web templates to build the Web page.

Avoid including Java code in a JSP file (mixing business and data access Java code with presentation logic) as it increases the JSP’s footprint and limits the efficient use and management of system resources.

Customized formatting of a Web page is always an expensive operation. Any type of formatting that cannot be natively achieved through Oracle Reports (e.g., change the foreground color of a data block) should be done using Java. We discourage the use of PL/SQL wrappers for formatting purposes.

A `.jsp` report definition can contain both a paper layout definition and a Web layout definition. Oracle Reports always formats the paper layout definition first when executing the report, since the Web layout section of a JSP report could contain an `<rw:include>` tag referencing a paper layout object. If your JSP report does not reference any paper layout objects at all, we recommend using the `SUPPRESSLAYOUT` command line option to prevent Oracle Reports executing the paper layout formatting.

See the Oracle9iAS Best Practices in Performance and Scalability: Application Design and Configuration white paper on OTN, (`http://otn.oracle.com`), for more performance tips on JSP technology.

### 20.1.4 General Layout Guidelines

This section outlines the various guidelines that you can follow when designing your report’s layout.

#### 20.1.4.1 Fetching Ahead

Oracle Reports enables you to display data such as total number of pages or grand totals, in the report margins or on the report header pages. This option, although useful, forces the entire report to be Òfetched ahead". Fetching-ahead requires the entire report to be processed before the first page can be output. The usual model is to format pages as and when required.

Although the fetched-ahead functionality does not affect the overall time the report takes to generate, it affects the amount of temporary storage required and the time taken before the first page can be viewed. This is an example of perceived performance as opposed to actual performance. If the report is to be output to the screen in a production environment, fetching ahead should be avoided unless the performance variance is deemed acceptable.

#### 20.1.4.2 Bursting and Distribution

With the introduction of report bursting, a report layout can be made up of three distinct sections: header, body, and trailer. A report can comprise all three sections, or it can be viewed as three separate reports within one report. Oracle Reports allows you to control bursting at group record level offering a further level of granularity. This is made possible by the Distribution and Repeat On properties for each individual section. The performance gain is evident when bursting is used in conjunction with distribution, allowing each section of a report to have multiple formats and sent to multiple destinations. Once the distribution options has been set the report needs only to be run once, to be output to multiple destinations with a single execution of the query(s). Previously the report had to be executed multiple times.

When you implement bursting and distribution in a report, you can generate section-level distribution by setting the Repeat On property for a section to a data model break group, which generates an instance of the section for each column record of that break group. Then, you can distribute each instance of the section as appropriate (for example, to individual managers in the MANAGER group).

If you set the Repeat On property for more than one of the Header, Main, and Trailer sections of a report, all Repeat On property values must be set to the same data model break group. If the Repeat On property for any one of the Header, Main, and Trailer sections is set to a different data model break group, Oracle Reports raises any of the following messages:

    REP-0069: Internal Error
    REP-57054: In-Process job terminated: Terminated with error
    REP-594: No report output generated

### 20.1.5 Calling Oracle Reports from Forms

Applications built using Forms Builder and Reports Builder require reports on data that has already been retrieved/updated by the Oracle Application Server Forms Services section of the application. The tight product integration between Oracle Reports and Oracle Application Server Forms Services allows you to pass blocks of data between the associated products and removes the need for subsequent queries. This technique referred to as query partitioning ensures that Oracle Reports is responsible for formatting data and ignores dynamic alteration of queries via triggers and lexical parameters.

Passing data between Oracle Application Server Forms Services and Oracle Reports is achieved using record groups and data parameters, in conjunction with the `Run_Report_Object` built-in (for calling Oracle Reports from OracleAS Forms Services). This is the only supported way to call Oracle Reports from OracleAS Forms Services.

Refer to the Integrating Oracle9iAS Reports in Oracle9iAS Forms white paper on OTN, (`http://otn.oracle.com`), for more information on calling a report from an Oracle Application Server Forms Services application.

Note: Unless data parameters are unreasonably large or the queries particularly complicated, the perceived performance improvements should be negligible. Additionally, only top level groups in a report can accept data parameters passed from forms.

### 20.1.6 Running the Report

You can further affect the overall performance by setting specific runtime options:

- Reports Builder automatically runs an error check on paper layout definitions and bind variables. Set the runtime parameter `RUNDEBUG=NO` to turn off this extra error checking at runtime.

- For JSP report definitions, Reports Builder performs tag validation and checks for items such as duplicate field identification or malformed attributes. This feature is useful only during the design phase, but not in the production environment. By default, Tag validation in OracleAS Reports Services is off. To turn this option on, use the `VALIDATETAG=YES` option in your `http` request.

- By default, the `RECURSIVE_LOAD` command line option used by both `rwrun` and `rwservlet` commands is set to yes causing invalid external references of PL/SQL program units to automatically recompile. Set the `RECURSIVE_LOAD` option to no in a production environment, because this is useful only in a development environment.

- For SQL queries, Oracle Reports takes advantage of the Oracle database’s array processing capabilities for data fetching. This allows records to be fetched from the database in batches instead of one at a time, resulting in fewer calls to the database. However, array processing requires more memory on the execution platform to store the arrays of records returned. To reduce the network load (number of network trips) in a production environment, set the value of the `ARRAYSIZE` command line parameter (defined in kilobytes) to a large value.

- Set the `LONGCHUNK` parameter to as large a value as possible, if your report uses the LONG, CLOB, or BLOB data types to retrieve large amounts of data. This reduces the number of increments taken by Oracle Reports to retrieve long values. On an Oracle8i or Oracle9i server, use the more efficient CLOB or BLOB data types, instead of LONG or LONG RAW.

- If the paper layout Parameter Form is not required, set the `PARAMFORM` option to `NO`.

Use the `COPIES` parameter carefully when printing to PostScript. Setting `COPIES` to a value greater than 1 requires that Oracle Reports save the pages in a temporary storage, in order to collate them. This increases the amount of temporary disk space used and the overhead of writing additional files results in slow performance.