Debugging EBS Reports/Using SQL Trace - Notes

Enable Trace
~~~~~~~~~~~~~~~~~~~~~~~~~
alter session set SQL_TRACE true;

--for oracle reports
-- put the following code in Before Report Trigger

DECLARE
  v_spid NUMBER;
BEGIN
  srw.do_sql('alter session set sql_trace=TRUE');
  SELECT
    spid
  INTO
    v_spid
  FROM
    v$process p,
    v$session s
  WHERE
    p.addr  = s.paddr
  AND s.sid =
    (
      SELECT
        sid
      FROM
        v$mystat
      WHERE
        rownum=1
    );
  srw.message ('1', 'v_spid='||v_spid);
END; 

Alternately you can also generate trace file by checking the Trace option in Concurrent Program Options. See metalink note: 453527.1 - How To Trace a Concurrent Request And Generate TKPROF File

Open the Report Builder Log:
~~~~~~~~~~~~~~~~~~~~~~~~~
You will find the process id in the report builder log file.

MSG-00001: v_spid=13434882

Identify the log file location
~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT * FROM V$PARAMETER WHERE NAME = 'user_dump_dest';


Disable
~~~~~~~~~~~~~~~~~~~~~~~~~
alter session set SQL_TRACE false;

/*
Setting TIMED_STATISTICS = TRUE in the init.ora will enable the
collection of time statistics. Also, in 10g, the initialization parameter
SQL_TRACE has been deprecated (see Appendix A for more
information).
*/


Opening Trace Files
~~~~~~~~~~~~~~~~~~~~~~~~~
You can open .trc files in SQL Developer directly or
you can use TKPROF utility


tkprof tracefile output_file [sort = parameters] [print=number]
[explain=username/password] [waits=yes|no] [aggregate=yes|no] [insert=filename]
[sys=yes|no] [table=schema.table] [record=filename]

tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)'

examples:

Run TKPROF and list only the top five CPU (fetch + execute + parse) results:
tkprof ora_19554 rich2 explain=system/manager sort=(FCHCPU,EXECPU,PRSCPU) print=5

Run TKPROF and omit all recursive statements:
tkprof ora_19554 rich2 explain=system/manager sys=no

Run TKPROF and create a file that will create a table and insert records from the trace:
tkprof ora_19554.trc rich2.prf explain=system/manager insert=insert1.ins



Options
~~~~~~~~~~


Variable Definition

Tracefile This is the name of the SQL TRACE file containing the
statistics by SQL_TRACE.

Output_file This is the name of the file where TKPROF writes its
output.


print = number This is the number of statements to include in the output. If
this statement is not included, TKPROF will list all
statements in the output.

Explain = username/password Run the EXPLAIN PLAN on the user’s SQL statements in
the TRACE file. This option creates a plan_table of its own,
so the user will need to have privileges to create the table
and space in which to create it. When TKPROF is finished,
this table is dropped. Ensure that you use the
username/password of the user that parsed the cursor (ran
the query) to ensure the explain is by the correct user. See
Metalink note: 199081.1 for more information.

insert = filename This option creates a script to create a table and store the
TRACE file statistics for each SQL statement traced.
record = filename This option produces a file of all the user’s SQL statements.

Sys = yes|no This option allows the user to request that the recursive
SQL statements (issued by the SYS user) not be displayed
in the output. The default is YES. Recursive SQL usually
includes internal calls and any table maintenance, such as
adding an extent to a table during an insert.

sort = parameters A tremendous number of sorting options are available. My
favorites are FCHCPU (CPU time of fetch); FCHDSK (disk
reads for fetch); FCHCU and FCHQRY (memory reads for
fetch); FCHROW (number of rows fetched); EXEDSK (disk
reads during execute); EXECU and EXEQRY (memory reads
during execute); EXEROW (rows processed during
execute); EXECPU (execute CPU time); PRSCPU (parse
CPU); and PRSCNT (times parsed).

waits=yes/no Record summary for any wait events.
aggregate=yes/no If no, then tkprof does not combine multiple users of the
same SQL text.

table=schema.table The table in which tkprof temporarily put execution plans
before writing them to the output file.