Tuning Oracle Reports 6i - I

Introduction 

There are a number of distinct areas to focus on when tuning Oracle Reports. It's helpful to have a clear understanding of these areas which can provide both perceived and measured improvements.

Consideration must be given to the costs involved, computing environment

complexity and the trade-offs that may occur in improving performance in a single area such as reports.

Investigation of some of these areas can result in significant performance improvements, some may result in minor performance improvements and yet others may have no effect on the actual report performance, but can improve the perceived execution time.

Performance analysis tools

Report Profile

The first step in analyzing the performance of a report by identifying the time taken by the report in various phases

1. Time spent in fetching the data (Report Sql)
2. Time spent in formatting the data (Report Layout)

Profile can be set in the report builder to identify the time consumed in each phase of the report.

Go to Report Builder

Navigate Tools -> Preferences -> Runtime settings -> Profile (Provide the path where the log file has to be saved)

The Profile log file gets saved only after the session. A sample profile file will look like this

----------------------------------- Sample Profile file ------------------------------------

LOG :

      Logged onto server: vis

      Username: apps

                                     +-------------------------------------+

                                    | Report Builder Profiler statistics |

                                    +-------------------------------------+

            TOTAL ELAPSED Time:     314.00 seconds

            Reports Time:     311.00 seconds (99.04% of TOTAL)

            ORACLE Time:       3.00 seconds ( 0.95% of TOTAL)

                            UPI:       2.00 seconds

                            SQL:       1.00 seconds

 TOTAL CPU Time used by process: N/A

Where

Total Elapsed Time:          Execution time of the report

Report Time:                    The amount of time that was spent formatting the retrieved data

Oracle Time:                    The amount of time spent waiting for the data to be retrieved

UPI time:                         The time spent establishing the database connection and parsing and executing the SQL.

SQL time                          The time spent while the database server fetches the data (percent of time spent executing SRW.DO_SQL() statements, EXEC_SQL statements, PL/SQL Cursors, etc.).

From the above example we can understand that the majority of the time is spent in formatting the retrieved data. So we have to concentrate much of our tuning effort on report layout

  

We can even trace the report profile when executing as command line argument.

The preferred method is to set the TRACEFILE (i.e. TRACEFILE=<filename>), with the additional command line arguments of TRACEMODE and TRACEOPTS.

TRACEMODE indicates whether to replace or append to the existing trace log file. TRACEOPTS is used to build the event list within the log file with TRACE_PRF being the specific profile option.

The following command line:

c:\rwrun60 report=emp.rdf userid=scott/tiger@orcl desformat=pdf tracemode=replace tracefile=emp.lis traceopts=trace_prf

Was used to produce the trace file for the Reports Profile Statistics.

Reports Trace (For Tuning and Debuging Report)

The reports trace option produces a file that describes the series of steps completed during the execution of the report.

The trace option can be set so that either all events are logged in the file or only a subset of those events (e.g., only SQL execution steps).

The trace file can provide an abundance of information that is not only useful for performance tuning, but also helps to debug reports.

The trace option can be set either from the main menu (Tools -> Trace) or from the command line argument TRACEFILE (i.e. TRACEFILE=<filename>.

Additional command line arguments

for this option include TRACEMODE and TRACEOPTS.

TRACEMODE specifies whether to replace or append to the existing trace log file. TRACEOPTS is used to specify the events to record within the log file.

Trace from Report builder

Go to Report Builder

Navigate to Tools -> Trace

Now provide trace file name with the trace options & trace mode.

  

A sample trace file may look like this:

22:04:04  MSG MSG-01000: Sequence of execution

22:04:05  MSG MSG-01001: BeforePForm

22:04:10  MSG MSG-01003: AfterPForm

22:04:11  MSG MSG-01004: Input: RESEARCH

22:04:11  APP  (  Database Column          P_DNAME

22:04:11  APP  )  Database Column          P_DNAME

22:04:11  PLS  (  Function:       beforereport

22:04:11  PLS  )  Function:       beforereport

22:04:11  APP  (  Frame                   

22:04:11  APP . (  Frame                    M_1

22:04:11  APP .. (  Repeating Frame          R_1

22:04:11  APP ... (  Group                    G_dname  Local Break:  0  Global Break:  0

22:04:11  APP .... (  Query                    Q_1

22:04:11  SQL         EXECUTE QUERY : select dname, deptno, loc from scott.dept where dname = :p_dname

                      Bind Variable               Value

                      --------------------------- -----------------------------

                      P_DNAME                     RESEARCH

22:04:11  APP .... )  Query                    Q_1

22:04:11  APP ... )  Group                    G_dname

22:04:11  APP ... (  Text Boilerplate         B_6

22:04:11  APP ... )  Text Boilerplate         B_6

22:04:11  APP ... (  Text Boilerplate         B_5

22:04:11  APP ... )  Text Boilerplate         B_5