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 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) log in 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. (Trace with waits is the recommended level)
(5)Write down the Process ID number(s). The tracefile_identifier is the userid
(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 Process ID number(s) if different.
(11)Go to diagnostics_dest for your database and collect the raw trace file(s) suffixes by the process ID number(s) and tracefile_identifier you have recorded.
(12)Self Service (OAF) uses connection pooling. So there could be several SQL trace files associated with a single user session. Either search the SQL traces to identify the file that the issue is in or aggregate them using trcsess.
(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 is 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,’’);

Profile Options Method

If none of the methods above allows the trace to be captured for the required session then all sessions for a particular user can be traced by setting the profile option “Initialization SQL Statement – Custom” user-level value.

This method also allows the level to be specified. So is often used for concurrent programs where a level 12 trace (binds and waits) is required.

To enable a SQL Trace using the Profile Option method:

1. Log in to the environment that will be traced.
2. If trace is being enabled for the current (logged in) user account then navigate to the Profile > Personal window, otherwise navigate to the Profile > System window.
3. Query the profile “Initialization SQL Statement – Custom”(internal name FND_INIT_SQL).
4. Enter the following as the profile value and save.

BEGIN FND_CTL.FND_SESS_CTL('','','TRUE','TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER=''TRACE12_PERF_TEST'' STATISTICS_LEVEL=ALL MAX_DUMP_FILE_SIZE=unlimited EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 8''');END;

Note: If there is existing SQL in the profile then the above should be merged with the existing SQL.

5. Log in (again) to the application using the user account being traced. This is necessary because the Initialization SQL Statement is only executed at login.
6. Execute the actions to be traced.
7. Exit the application and log out.
8. Navigate to the profile form again, remove the SQL above (ensuring any original SQL remains) from the “Initialization SQL Statement – Custom” profile and save.

How to find the trace files

Trace files are located in the below location

<diagnostic_dest>/diag/rdbms/<dbname>/<instance name>/trace

Where diagnostic_dest is specified in the initialization parameter diagnostic_dest.

This can be obtained by running the SQL:

SELECT value FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

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 in Oracle: SQL trace or 10046 event is being used to trace the session activity in Oracle.
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)