A script-enabled browser is required for this page to function properly.

Tracing using the SQL TRACE function

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.

Performing a user-level trace

  1. Open the report for which you want the performance data.

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

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

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

Performing a system-level trace

  1. Insert these statements into your init.ora file: SQL_TRACE=TRUE and TIMED_STATISTICS=TRUE.

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