How to enable trace in oracle apps r12

• Sometimes users get performance issues during various pages in the Oracle Apps application. We need to take the trace of the problem in order to identify the bottleneck
• We can execute the below steps to generate the database level trace for the issue and find out the culprit sql or statement. Here is how to enable trace in oracle apps r12

How to get the trace for Self Service Page

(1) Set profile FND: Diagnostics = Yes at USER level.
(2) Login to the Personal Home Page as that user and select the Diagnostics link at the top of the page.
(3) Select Set Trace Level and click Go.
(4)Select the desired trace level and click Save.
(5)Write down the trace id number(s).
(6)Perform the activity that you want to trace.
(7)Return to the ‘Diagnostics’ page.
(8)Select `Set Trace Level’ and click Go.
(9)Select ‘Disable Trace’ and click Go.
(10)Write down the trace id number(s) if different.
(11)Go to diagnostics_dest for your database and collect the raw trace file(s) suffixes by the trace id number(s) you have recorded.
(12)Exit Applications

How to enable trace for forms in oracle apps r12

(1) Set profile FND: Diagnostics = Yes at USER level.
(2) log in to the Application
(3) Navigate to the form where you want to trace
(4) Turn on Tracing by using the menu option: Home > Diagnostics > Trace > Trace with waits
(5) A pop-up with the trace file name and location gets displayed. Note down the trace filename
(6) Perform the activity that you want to trace.
(7) Return to Home > Diagnostics > Trace >
(8) Select ‘Disable Trace’ and click Go.
(9) Go to diagnostics_dest for your database and collect the raw trace file(s) suffixes by the trace id number(s) you have recorded.
(10) Exit Applications

How to get the trace for Oracle Concurrent Program

1. Navigate to Concurrent > Program > Define screen
2. Search for the concurrent program you want to trace
3. Check the Enable Trace box to turn on tracing for the concurrent program
4. Submit and run the concurrent program
5. Write down the request_id of your concurrent program job
6. Go back to the Define screen and un-check the Enable Trace box for this concurrent program
7. Retrieve the raw trace file using the request_id

column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'ora'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from apps.fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, apps.fnd_concurrent_programs_vl prog,
apps.fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

how to enable trace for a running concurrent request

Run the below query to find the SPID and SID of the concurrent request

col addr format a11
col program format a20 trunc
col logon_time format a18
col osuser format a8 heading unixUsr
col p_user format a9 heading unixUsr
col terminal format a7 heading unixtrm
col command format 99 heading Cd
col machine format a7
col action format a10
col module format a10
col requestor format a20
col cmgr_job format a38 trunc heading 'CMgr_job'
set pagesize 24
Prompt Enter the Concurrent Request ID number:
select s.inst_id, fcr.request_id, fv.requestor, fv.Program cmgr_job,
p.PID,
p.SERIAL#,
p.USERNAME p_user,
p.SPID,
to_char(s.logon_time,'DD-MON-YY HH24:MI:SS') Logon_Time,
s.program,
s.command,
s.sid,
s.serial#,
s.username,
s.process,
s.machine,
s.action,
s.module
from apps.fnd_concurrent_requests fcr,
apps.FND_CONC_REQ_SUMMARY_V fv,
gv$session s,
gv$process p
where fcr.request_id = &request_id
and p.SPID = fcr.oracle_process_id
and s.process = fcr.OS_PROCESS_ID
and s.inst_id = p.inst_id
and p.addr = s.paddr
and fv.request_id = fcr.request_id
;

Now you can enable trace on the SID using oradebug or DBMS program as

SQL> oradebug setospid 1111
SQL> oradebug event 10046 trace name context forever, level 12
LEVEL 12 – Both Binds and Waits
LEVEL 8 – Only WAITS
LEVEL 4 – Only BIND Variables
SQL>oradebug tracefile_name
prod _ora_1111.trc
Wait for 15-20 minutes
SQL> oradebug event 10046 trace name context off

Using DBMS program

Full level with wait event And bind trace

execute dbms_system.set_ev(‘sid’,’serial’,10046,12,’’);

To put trace off

execute dbms_system.set_ev(‘sid’,’serial’,10046,0,’’);

How to analyze the Trace files

Once the trace files are found, we can use the tkprof utility to find the culprit statement from the trace file

tkprof XPROD_ora_19005_a.trc XPROD_ora_19005_a.txt sys=no explain=apps/apps sort=prsela,exeela,fchela

Related Articles
SQL trace : SQL trace or 10046 event is being used to trace the session activity in Oracle. The output generated by SQL trace is formatted using trcsess, tkprof utility
Autotrace in oracle : Autotrace in oracle, What is autotrace, how to set up it, Understanding Autotrace Output, Autotrace options, statistics displayed by autotrace
sql tuning advisor: How to run sql tuning advisor for sql_id in the Cursor cache, how is the sql tuning task created and executed to get the recommendation
Hanganalyze: check out how to take system state dump in oracle, hanganalyze in oracle, what is v$wait_chains, and how it can help find the blocking
How to generate FRD trace in Oracle Apps: How to generate Forms runtime diagnostics trace in oracle apps 11i/R12
FAQ: Common Tracing Techniques in Oracle E-Business Applications 11i and R12 (Doc ID 296559.1)