Introduction
+-------------------------------------+
22:04:04 MSG MSG-01000: Sequence of execution22:04:05 MSG MSG-01001: BeforePForm22:04:10 MSG MSG-01003: AfterPForm22:04:11 MSG MSG-01004: Input: RESEARCH
22:04:11 APP ( Database Column P_DNAME22:04:11 APP ) Database Column P_DNAME22:04:11 PLS ( Function: beforereport22:04:11 PLS ) Function: beforereport22:04:11 APP ( Frame 22:04:11 APP . ( Frame M_122:04:11 APP .. ( Repeating Frame R_122:04:11 APP ... ( Group G_dname Local Break: 0 Global Break: 022:04:11 APP .... ( Query Q_122:04:11 SQL EXECUTE QUERY : select dname, deptno, loc from scott.dept where dname = :p_dname22:04:11 APP .... ) Query Q_122:04:11 APP ... ) Group G_dname22:04:11 APP ... ( Text Boilerplate B_622:04:11 APP ... ) Text Boilerplate B_622:04:11 APP ... ( Text Boilerplate B_522:04:11 APP ... ) Text Boilerplate B_5
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
- Time spent in fetching the data (Report Sql)
- 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:
Bind Variable Value
--------------------------- -----------------------------
P_DNAME RESEARCH
