20 Tuning Oracle Reports

20.1.1 Oracle Enterprise Manager

Using Oracle Enterprise Manager to manage and monitor your Reports Server is discussed in detail in Chapter 19, "Managing and Monitoring OracleAS Reports Services".

20.1.2 Report Trace

Enabling report tracing generates 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 and identifying performance bottlenecks.

Generating a report trace file

To enable tracing, do one of the following:

• In the Reports Builder user interface:

  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.

Figure 20-1 Reports Builder Runtime Trace Setting Dialog Box

Description of "Figure 20-1 Reports Builder Runtime Trace Setting Dialog Box"

• For Reports Builder (rwbuilder) and Reports Runtime (rwrun), specify tracing options (described in Section 3.2.1.13, "trace") in the rwbuilder.conf configuration file, or on the command line using TRACEFILE, TRACEMODE, and TRACEOPTS.

  
  

  Note: Command line tracing options override the options in the rwbuilder.conf file.

  
  

  For example:

  • In rwbuilder.conf, specify:

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

  • On the command line, specify:

    rwrun report=myreport.rdf server=myserver userid=user_id/password@mydatabase 
    destype=cache desformat=pdf TRACEFILE=trace_file_name TRACEOPTS=trace_all TRACEMODE=trace_replace
    

  
  

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

  
  

• For Reports Server (rwserver), specify tracing options (described in Section 3.2.1.13, "trace") in the server_name.conf configuration file, or on the command line when starting Reports Server using TRACEFILE, TRACEMODE, and TRACEOPTS. Separate trace files are generated for Reports Server and the engine(s).

  
  

  Note: Command line tracing options override the options in the server_name.conf file.

  
  

  For example:

  • In server_name.conf, specify:

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

  
  

  Note: The location of the trace file is relative to the server log directory (ORACLE_HOME\reports\logs\server_name) or absolute if a full path name is specified. If you do not specify a trace file name, the default server trace file name is rwserver.trc and the default engine trace file name is rwEng-x.trc (where x is the engine ID).

  
  

  To enable job tracing for an individual report run through Reports Server (rwserver) specify desired tracing options with traceOpts in the configuration file (server_name.conf or rwbuilder.conf) or TRACEOPTS on the command line. The job trace is generated in ORACLE_HOME\reports\logs\server_name\job_id\log.xml. This log file is best viewed through Oracle Enterprise Manager 10g. For more information, see the Oracle Enterprise Manager 10g Application Server Control online Help: on the Contents tab of the online Help, expand the topics under Managing OracleAS Reports Services, then display the topic Viewing and Managing the Failed Jobs Queue, which contains the section "Viewing a Failed Job's Trace File".

• For Reports Servlet (rwservlet), specify tracing options in the servlet configuration file (rwservlet.properties), as described in Section 3.4.5, "Setting Up Trace Options for Reports Servlet and JSPs".

• For Oracle Reports bridge tracing, specify tracing options (described in Section 3.3.2.6, "trace") in the bridge configuration file (repbrg_bridgename.conf).

Example

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

rwrun report=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.

+------------------------------------------+
| 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

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

20.1.3 RW_SERVER_JOB_QUEUE Table

The RW_SERVER_JOB_QUEUE table provides another window (aside from that available through Enterprise Manager) into the Reports Server job queues.

The Reports Server posts information about the current report to the database each time a job request is submitted. This information is inserted into the RW_SERVER_JOB_QUEUE table that includes the following data:

• The name of the job

• The job submitter

• The output format

• The job's current status

• When the job was queued, started, and subsequently finished

Table 20-2 lists and describes the information contained in the RW_SERVER_JOB_QUEUE table:

Users can view this table if you grant them SELECT access. This will enable them to query the job submission of interest and determine the job's current status. You can also give them a view of this data by implementing a Reports Server Queue screen. You can implement such a screen by creating a report based directly on this table. Doing so displays the queue report as a job submission by the user.

Conversely, the real-time update of the table with the status of job submissions makes it very easy for administrators to know exactly how many concurrent users have requested jobs to be run on the Reports Server.

By counting the number of entries in the RW_SERVER_JOB_QUEUE table that have a status code indicating that the job has been queued but not completed, it is possible to return an accurate number of the current active users on the server. For example, you could use the following query:

SELECT Count(*)
FROM   RW_SERVER_JOB_QUEUE
WHERE  STATUS_CODE IN (1,     -- ENQUEUED
                      2,      -- OPENING
                      3)      -- RUNNING
AND    JOB_TYPE != 'Scheduled'

ORACLE_HOME\reports\admin\sql\RW_SERVER.SQL

20.1.4 SHOWJOBS Command Line Keyword

You can use showjobs on the command line to display a Web view of Reports Server queue status for reports run through rwservlet.

For more information, see Section A.3.99, "SHOWJOBS".

20.1.5 Efficient SQL

Oracle Reports uses SQL to retrieve data from the database.

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, such as SQL that does not use available indexes.

To tune your report SQL, use the trace functionality available in the Oracle database. SQL tracing enables 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);

The following EXPLAIN PLAN map was generated using the database's SQL trace facility. Refer to the PL/SQL User's Guide and Reference 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; that is, 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; that is, 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.6 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.

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.7 Java Stored Procedures

Java stored procedures enable you to implement business logic at the server level; thereby, improving application performance, scalability, and security. Oracle Database 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 Oracle Database Java Developer's Guide for more information on Java stored procedures.

20.1.8 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 enables 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.4.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 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 ensure 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.

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. For more information, see Chapter 9, "Configuring and Using the JDBC PDS".

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

20.4.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 Oracle Database 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 Oracle 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.4.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 multiquery 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;

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 (that is, 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.4.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 less 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 in spite 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 roperty 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.4.6 Group Filters

Group filters reduce the number of records displayed. Filtering takes place after the query returns the data (from the data source) to Oracle Reports. Even if the filter is defined to display only the top five records, the result set 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.4.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 multitable 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.5.1 Paper Layout

When generating a default paper layout, Oracle Reports places a frame around virtually every object to prevent the object from being overwritten by another object. At runtime, every layout object (frames, fields, boilerplate, and so on) 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.

An object that is defined as variable, expanding, or contracting in either or both the horizontal or vertical directions requires extra processing. 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.5.1.1 Format Triggers

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:

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 this example, 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 "Figure 20-2 Format Triggers"

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.5.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 Oracle Database 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 (for example, 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 keyword to prevent Oracle Reports executing the paper layout formatting.

20.6.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.6.2 Bursting and Distribution

With 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 enables 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