Skip Headers
Oracle® Application Server Reports Services Publishing Reports to the Web
10g Release 2 (10.1.2)
B14048-02
  Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

20 Tuning Oracle Reports

As your reporting requests grow in size and complexity and your user base increases, you will 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 tune the performance of your reports:

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 20.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).


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://www.oracle.com/technology/products/reports/index.html) for more information.

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

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

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

This chapter addresses these aspects in the following sections:

20.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 runtime 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.

This section discusses the tools you can use to monitor the performance of your report:

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

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

Table 20-2 Structure of the RW_SERVER_JOB_QUEUE Table

Column Name Description

JOB_QUEUE

States whether the job listed is CURRENT, PAST, or SCHEDULED.

JOB_ID

System generated job identification number.

JOB_TYPE

Type of job, such as report, rwurl, and so on, as defined in the Reports Server configuration file, server_name.conf.

JOB_NAME

Job submission name (or file name if no value for JOBNAME is specified).

STATUS_CODE

Current status of job. See Table 20-3 for more information about status codes.

STATUS_MESSAGE

Full message text relating to status code (includes error messages if report is terminated). See Table 20-3 for more information about status codes.

COMMAND_LINE

Complete command line submitted for this job submission.

OWNER

User who submitted the job. On the Web, the default user is the OS user who owns the Web server.

DESTYPE

Destination where report output is sent.

DESNAME

Name of the report output if not going to the Reports Server cache.

SERVER

Reports Server to which the report was submitted.

QUEUED

Date and time the job submission was received and queued by the given Reports Server.

STARTED

Date and time the job submission was run.

FINISHED

Date and time the submitted job completed.

RUN_ELAPSE

Elapsed time between started and finished time, in units of milliseconds.

TOTAL_ELAPSE

Elapsed time between queued and finished time, in units of milliseconds.

LAST_RUN

Date and time a scheduled job was last run.

NEXT_RUN

Date and time a scheduled job will run.

REPEAT_INTERVAL

Frequency on which to run a job.

REPEAT_PATTERN

Repeat pattern (for example, every minute, every hour, or every day).

CACHE_KEY

Cache key used to compare a request with an already cached result. The key is a string that uniquely indicates a report output result without considering the time the job was run. For example, if two requests have the same key, it means they will both generate the same output if they are running at the same time, although the outputs may be used for different purposes (for example, sent to e-mail or saved to a file).

CACHE_HIT

Indicates whether the job result was fetched from cache instead of running itself.


Table 20-3 Job Submission Status Codes

Status Code Defined PL/SQL Constant Description for Status Code

0

UNKNOWN

No such status.

1

ENQUEUED

Job is waiting in queue.

2

OPENING

Server is opening report definition.

3

RUNNING

Report is currently running.

4

FINISHED

Job submission has completed successfully.

5

TERMINATED_W_ERR

Job has ended with an error.

6

CRASHED

Engine has crashed during execution of the job.

7

CANCELED

Job was canceled by user request.

8

SERVER_SHUTDOWN

Job was canceled due the Reports Server shutting down.

9

WILL_RETRY

Job failed and is queued for RETRY.

10

SENDING_OUTPUT

Job has completed and is returning output.

11

TRANSFERRED

Job is transferred to another server in the cluster.

12

VOID_FINISHED

Job is finished but output is void because of reaching limit of cache capacity.

13

ERROR_FINISHED

Output is successfully generated but failed to send to destinations.

14

DISTRIBUTE

Distributing report output.


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'

Note:

While the table contains the date and time a report was queued, run, and finished, it is not a good idea to use a query based on the fact that a job has a defined QUEUED and STARTED time but no FINISHED value. If a report ends due to an unexpected error, such as invalid input, then the FINISHED column remains NULL. However, the STATUS_CODE and STATUS_MESSAGE both indicate there has been a failure and list the cause of that failure.

20.1.3.1 Updating the Database with Queue Activity

The Reports Server job queue is implemented through the use of a PL/SQL case API. It functions to update the queue table with the queue information as requests are made. This implementation is defined in the following path:

ORACLE_HOME\reports\admin\sql\RW_SERVER.SQL

This script is certified to worked against Oracle 10g database.

To implement the queue, perform the following steps:

  1. Load the rw_server.sql file to a database (this file is included with your OracleAS Reports Services installation: ORACLE_HOME\reports\admin\sql).

    This creates a schema that owns the report queue information and has execute privileges on the server queue API. For backward compatibility with Oracle6i Reports, this also creates a view called RW_SERVER_QUEUE.

  2. Set the repositoryconn property of the jobStatusRepository element in the server configuration file (ORACLE_HOME\reports\conf\server_name.conf) to the connection string of the schema that owns the queue data.If you want the connection information to be encrypted when Reports Server starts, set the confidential attribute to yes. For more information, see Section 3.2.1.12, "jobStatusRepository".

When the server starts, it connects as the defined user and logs job submissions.

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.


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, 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);

Note:

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

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.


Note:

For a description of the ORA built-in package see the Oracle Reports online Help.

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.


Note:

For a description of the SRW built-in package, including the SRW.DO_SQL built-in procedure, see the Oracle Reports online Help.

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.2 Tuning Reports Server Configuration

This section provides tips for improving the performance and stability of Reports Server, which is responsible for:

While operating under heavy load, it is essential to tune various Reports Server parameters to optimal values, as follows:

  1. Determine optimal values for the initEngine, maxEngine, and minEngine attributes of the engine element in the server configuration file:

    <engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1" maxEngine="2" minEngine="1" engLife="50"
    maxIdle="30" callbackTimeOut="90000">
    
    

    For more information on the engine element, refer to Section 3.2.1.4, "engine". The maxEngine value sets the maximum number of processes ready to respond to user requests for running reports. Setting it too low means user requests get queued up and available machine capacity is not fully utilized. Setting it too high means Reports Server will take more than its share of machine capacity from other activities the host also needs to perform, and could cause the operating system to thrash.

    As an example of a simple calculation for number of engines, suppose you have set of reports that takes an average of 10 seconds to run. Input requests to your system varies from 6 reports per minute to 12 reports per minute. In this scenario, the calculations are as follows:

    • initEngine = ( average time to run report) * (minimum report requests input rate) = (10/60) * 6 = 1

    • maxEngine = (average time to run report) * (maximum report requests input rate) = (10/60) * 12 = 2

    • minEngine = Depending on the kind of load, anything between 0 to initEngine

    With these calculations, minEngine=1 and maxEngine=2 can be specified in the server configuration file. This ensures that whenever a job arrives, it gets an idle engine immediately.

    In scalability and performance tests, maximum throughput is seen when maxEngine is configured using the guideline of 2-4 engines multiplied by the number of CPUs.

    If you are not using the URL engine, comment the engine element with ID="rwURLEng" in the server configuration file.

  2. Determine optimal values for the cache element's cacheSize property, the queue element's maxQueueSize attribute, and the EXPIRATION keyword.

    For more information, refer to Section 3.2.1.3, "cache", Section 3.2.1.16, "queue", and Section A.3.35, "EXPIRATION". The values of cacheSize, maxQueueSize, and EXPIRATION are related to each other and they need to be set carefully for efficient Reports Server operation.

    For example, when you run reports with EXPIRATION=480, this implies that you want to keep the jobs in cache for 4 hours (480 minutes). Given that, maxQueueSize should be set to accommodate all the jobs for 4 hours. Thus, at a rate of 10 jobs per minute:

    maxQueueSize = (report requests input rate) * (expiration period) = 480 * 10 =4800.

    The value of cacheSize also should be set sufficiently high to accommodate 4800 jobs. Suppose the average size of each report is 100K:

    cacheSize = (maxQueueSize) * (average size of report) = 4800*100/1000 = 480MB

    You can use similar logic to calculate the value of the the cache element's maxCacheFileNumber property.


    Note:

    The minimum recommended value for maxQueueSize is 1000 (the default). A significantly lower value than the default values for maxQueueSize or cacheSize may degrade Reports Server performance.

  3. Set the engineResponseTimeOut attribute of the engine element in the server configuration file:

    <engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1"
    maxEngine="2" minEngine="1" engLife="50" maxIdle="30"
    callbackTimeOut="90000" engineResponseTimeOut="5">
    
    

    For more information on the engine element, refer to Section 3.2.1.4, "engine".

    Set engineResponseTimeOut if you are experiencing intermittent engine hangs. This attribute enables Reports Server to detect the hanging engine and perform cleanup. The sooner Reports Server detects the hang, the better the stability of the system. Thus, engineResponseTimeOut must be set carefully, as follows:

    The value of engineResponseTimeOut should be set to the maximum time a report takes in the set of reports you have. For example, if you have set of reports that takes 10 seconds to 5 minutes to run, you can set engineResponseTimeOut="5" (5 minutes).


    Note:

    It is always better to run batch reports on a separate server with different engineResponseTimeOut values. Do not submit interactive and batch reports to same server.

  4. Set the maxConnect attribute of the connection element in the server configuration file;

    <connection maxConnect="180" idleTimeOut="15">
    
    

    For more information on the connection element, refer to Section 3.2.1.14, "connection".

    The maxConnect attribute controls how many total requests Reports Server can simultaneously handle at any moment in time. The key purpose of maxConnect is to keep Reports Server from being overcome by some runaway program or process or by a denial of service attack. It should be always set to a value that is greater than the maximum simultaneous clients.

    For example, if your system is expected to handle 150 simultaneous clients, you can set maxConnect to any value above 150. You can use a safety factor of 10% to 20%, as follows:

    maxConnect = 150 + 150 * 0.2 = 180

  5. Set the HTTP timeout value (applicable to AS only).

    The HTTP timeout value should be set based on the time required to run the longest report in the system. If longest-running report takes 20 minutes to run, HTTP timeout should be more than 20 minutes. Otherwise, an HTTP timeout error will display when the report is still running in the server. This parameter can be set in the ORACLE_HOME/Apache/Apache/conf/httpd.conf file.

20.3 Using rwdiag for Bridge and Network Timeout Settings

If the Oracle Reports client and the Reports Server are on different subnets, you need to use an Oracle Reports bridge. A bridge is used only when a broadcast mechanism is used for server discovery. A bridge is not necessary when a naming service is used. For more information on the Oracle Reports bridge, see Section 1.4.1.2, "Server Discovery Across Subnets".

For proper operation of the Oracle Reports bridge, you must configure the Oracle Reports bridge timeout properly. Bridge configuration also depends on the network configuration file used by the servers and clients. The rwdiag utility can help you calculate the bridge and network timeouts correctly.

The bridge configuration file rwbridge_bridgename.conf (see Section 3.3.2, "Bridge Configuration Elements (bridgeconf.dtd)" typically looks something like the following:

<bridge version="10.1.2" port="14011" timeout="1200">
   <!--identifier encrypted="no"confidential="yes">%USERNAME%/%PASSWORD%
      </identifier-->
   <!--networkConfig file="rwnetwork.conf" ></networkConfig-->
   <trace traceOpts="trace_all"></trace>
   <!-- Specify one or more remote bridges inside remoteBridges element -->
   <!--remoteBridges>
      <remoteBridge host="%HOST%" port="%PORT%"></remoteBridge>
   </remoteBridges-->
</bridge>

The network configuration file rwnetwork.conf (see Section 3.3.1, "Network Configuration Elements (rwnetworkconf.dtd)" typically includes a discoveryService element similar to the following in a bridge configuration:

<discoveryService>
   <multicast channel="228.5.6.7" port="14021" timeout="500" retry="3"/>
   <!--namingService name="Cos" host="%HOST%" port="%PORT%">  
   </namingService-->
</discoveryService>

To configure the Oracle Reports bridge, you first need to find the time taken by the remote bridge to respond. To find the time taken by a remote bridge, use the rwdiag utility and specify the remote server name.

rwdiag.bat  -find server_name

This command prints the time taken for finding the remote server through the remote bridge.

Suppose the value returned is 1100 milliseconds (1.1 seconds). You should take the value returned and add some time for bridge processing, for example 100 milliseconds, to the ping time. Hence, the timeout value in the bridge configuration file should be 1200 milliseconds.

The network timeout should also be set such that the client does not timeout before the bridge can respond back. Network timeout multiplied by the retry value should always be greater than the bridge timeout.

For reliable operation, it is always better to have a retry value greater than 1. Assuming a retry value of 3, you could calculate the network timeout value as follows:

1200 < network_timeout * 3
network_timeout > 400

Based upon this calculation, a good network timeout value would be 500 ms.

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

This section discusses ways to review and improve the efficiency of the data used in your report:

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.


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


Note:

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

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.


Note:

For a description of the SRW built-in package, including the SRW.SET_MAXROW built-in procedure, see the Oracle Reports online Help.

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 (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 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 rules for a Web layout are a little different as Oracle Reports does not own the Web page or control the rendering mechanism. It merely injects data into a regular JSP page.

This section discusses reviewing and tuning the format of your report:

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  follows
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.1.2 Image Outputs

You can improve the performance of reports that include images by judiciously setting environment variables related to image support.

Improving performance of graphs output to a PDF file or a printer

The REPORTS_GRAPH_IMAGE_DPI environment variable specifies a dots per inch (DPI) value for graphs output to a PDF file or a printer. The default value for this environment variable is set at 72 DPI to minimize the time taken to generate the report, as well as to reduce the report file size. If you specify a value higher than 72 DPI, you will see an improvement in the image resolution for graphs sent to a PDF file or a printer. However, this affects the time taken to generate the report output as well as the file size.

With the value of 250, the time taken to generate a report with an Oracle Reports graph increases 5 to 6 times when compared to the time taken to generate the same report with the value set to 72 DPI. The PDF file size also increases 5 to 6 times.

This functionality is currently not supported in Oracle Reports distribution functionality, as this is specific to PDF and printer outputs only.


Note:

When you set a DPI value greater than 250 and your graph is bigger than 5"x5" (approximately), you may also need to change the JVM heap size value using the REPORTS_JVM_OPTIONS tenvironment variable o avoid the Out Of Memory error for the JVM.

For more information, refer to Section B.1.44, "REPORTS_GRAPH_IMAGE_DPI".

Improving performance of JPEG/GIF/PNG output image formats

If your input image format is JPEG, it is recommended that you do not set the REPORTS_OUTPUTIMAGEFORMAT environment variable to GIF or PNG, which will increase the image size more and might degrade the performance problem. Similarly, if your input image format is GIF or PNG, it is recommended that you do not set the REPORTS_OUTPUTIMAGEFORMAT environment variable to JPEG. For better performance, use the same format for both input and output format.

For more information, refer to Section B.1.52, "REPORTS_OUTPUTIMAGEFORMAT".

Improving performance of JPEG images

The REPORTS_JPEG_QUALITY_FACTOR environment variable specifies the level of image quality desired for JPEG images. It provides control over the trade-off between JPEG image quality and size of the image. The better the quality of the image, the greater the image file size and lower performance. If you want to improve the performance, set value to 0. The default value is 100 (highest quality). A value of 75 provides a good quality image, while ensuring a good compression ratio.

For more information, refer to Section B.1.46, "REPORTS_JPEG_QUALITY_FACTOR".

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 General Layout Guidelines

This section outlines guidelines that you can follow when designing your report's layout to improve performance:

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

20.7 Calling Oracle Reports from Forms

Applications built using Forms Builder and Reports Builder require reports on data that has already been retrieved or updated by the OracleAS Forms Services section of the application. The tight product integration between Oracle Reports and OracleAS Forms Services enables 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 through triggers and lexical parameters.

Passing data between OracleAS 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).

For more information on calling a report from an OracleAS Forms Services application, refer to the Integrating Oracle Reports Services 10g in Oracle Forms Services 10g white paper on OTN (http://www.oracle.com/technology/products/forms/techlisting10g.html).


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.8 Running the Report

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