The SQL TRACE function provides you with the exact statements that are being parsed. Once you have them, you can time them in SQL*Plus, and multiply these times with the expected number of rows to retrieve from the database. (Always compare apples to apples; that is, send output to a file, not to the screen. Do not change anything in the SQL statements when moving it to SQL*Plus; even the slightest change in the WHERE clause can make a big difference in the performance.)
There are two ways to trace your reports:
We recommend the user level because you can more easily find the information you need. For more information on SQL TRACE, see the Oracle Server SQL Language Reference Manual.
Open the report for which you want the performance data.
Create a report-level formula column named SQL-TRACE
that has the following formula:
SRW.DO_SQL
('ALTER SESSION SET SQL_TRACE=TRUE');
return(1); --Formulas must return a value.
Note: You could also call SQL TRACE from the Before Form trigger.
Run the report. A new file, some_number.trc
will be created in either ORACLE_HOME
/rdbms/log
,
or the destination indicated by the init.ora
parameter USER_DUMP_DEST
.
(The date stamp on the file can help you determine which .trc
file is yours.)
Use the TKPROF
command to
format the trace output file.
If you issue more than one trace during the rwbuilder
session, the trace outputs are concatenated into one file.
Insert these statements into your init.ora
file: SQL_TRACE=TRUE
and
TIMED_STATISTICS=TRUE
.
Shut down, then restart your database.
Note:
Every interaction with the database will be traced, and the ORACLE_HOME/rdbms/log
is likely to grow very large.
Copyright © 1984, 2005, Oracle. All rights reserved.