Performance Problems - Concurrent Programs

You’ve noticed that a particular program is taking, on average, two hours to run. You can
turn trace on for this report by logging in with the System Administrator responsibility,
and navigating to
Concurrent – Program – Define - Query up your report. Click on the “Enable Trace” button - Turn On Tracing

Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option Concurrent: Allow Debugging
Set profile to Yes

Run Concurrent Program With Tracing Turned On
Logon to the Responsibility that runs the Concurrent Program
In the Submit Request Screen click on Debug Options (B)

Select the Checkbox for SQL Trace
Click Debug Options

TKPROF Trace File

Once you have obtained the Raw trace file you need to format the file using
TKPROF.
$tkprof raw_trace_file.trc output_file explain=apps/apps
sort=(exeela,fchela) sys=no
Where:
raw_trace_file.trc: Name of trace file
output_file:
tkprof out file
explain:
This option provides the explain plan for the sql
statements
sort:
This provides the sort criteria in which all sql
statements will be sorted. This will bring the bad
sql at
the top of the outputfile.
sys=no:
Disables sql statements issued by user SYS
Another example: To get (TKPROF) sorted by longest running queries first and
limits the results to the "Top 10" long running queries

$ tkprof <filename.trc> <output_filename> sys=no explain=apps/<password
> sort='(prsela,exeela,fchela)' print=10
Now every time this report runs, until you turn trace off, it will generate a trace file.
We strongly recommend that you trace Concurrent Programs on your test
environment rather than your production environment, because anytime anyone
runs this report, it will generate a trace file. At the very least, if you run trace on your
production environment, don’t forget to turn it back off after you’re done!
cd $ORACLE_HOME/admin/<CONTEXT_NAME>/udump
run tkprof
tkprof filename.trc filename.prf explain=apps/<apps_pwd>
sys=no
sort='(prsela,exeela,fchela)' print=10
or
To run Trace Analyzer:
sqlplus apps/<apps_pwd>
SQL> START TRCANLZR.sql UDUMP vis_ora_1234.trc;
More options:
1. Login to Oracle Applications and select System Administrator responsibility.
2. Navigate to the Concurrent Program definition form.
Concurrent -> Program -> Define
3. Query the concurrent program ARBRFRRT
4. Enter your trace Options in the Options Field of the Executable Section using
the below format and save the changes:
TRACEOPTS=<option>TRACEFILE=<filename>
TRACEMODE=<TRACE_APPEND | TRACE_REPLACE>
Example :
TRACEOPTS=TRACE_ALL TRACEFILE=c:\Temp\SMtrace.trc
Note : TRACEOPTS can only be used for Oracle Reports type concurrent
programs as it is an execution option to an Oracle Reports R
untime executable
It will help if you can also enable Binds for the purposes of this SR.
If you wish you can enable binds, waits, and additional tracing options by
entering your Trace Options in the Options Field of the Executable Section.
List of values that can be used for TRACEOPTS :
Option Description
TRACE_ERR list error messages and warnings
TRACE_TMS enter a timestamp for each entry
TRACE_SQL log trace information on all of the SQL
TRACE_PRF log performance statistics
TRACE_PLS log trace information on all PL/SQL objects
TRACE_DST creates a log containing the distribution lists involved. Available
from Reports 6.0 and up.
TRACE_APP log trace information on all of the report objects
TRACE_ALL log all possible trace information

Reference: How To Trace a Concurrent Request And Generate TKPROF File -
453527.1