How To Generate a Database Trace for Oracle iStore?11.5.10/12:

Goal

The purpose of this document is to describe how to gather database trace files from Oracle iStore application to debug a performance issue (for example, order submit flow) or to trace sql statement executed for a particular iStore flow (for example, sql statement along with bind variables used by iStore catalog search).

Solution

1. Login to iStore

2. Go to a point where you are ready to reproduce the issue, but just before that, enable trace as follows:

2.1. Login to EBS Apps Login as SYSADMIN or user with access to System Profile Form
2.2. Responsibility: System Administrator
2.3. Profile > System

Profile =  Initialization SQL Statement - Custom
Level = User (iStore user)
Value (This is one line and all single quotes):

begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG', 'ALTER SESSION SET EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'' tracefile_identifier=''ISTORET1'' max_dump_file_size=''unlimited'''); end;

IMPORTANT:
* Do NOT use "-" (hyphen) in the tracefile_identifier value. For example, tracefile_identifier=''ISTORE-T1'' is NOT valid.
* Please provide a unique tracefile_identifier value for each iteration of testing. Replace "'ISTORET1" with your own identifier that helps identifies trace files relevant for your test case.

3. Reproduce the issue in iStore

4. Erase the profile "Initialization SQL Statement - Custom" value set on iStore user level to stop the trace
This is an IMPORTANT step.

5. Locate the trace file in Unix on database node as follows:

location:SQL> select value from v$parameter where name = 'user_dump_dest'

file name:In step 2.3, if tracefile_identifier=''ISTORET1'', then you can check for file name:
$ ls -ltr *ISTORET1*.trc

NOTE: There may be more than one trace file generated for the flow, we need all files

6. tkprof using below syntax:

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

7. Upload both raw and tkprof'd files in a zip file
IMPORTANT: raw trace file size may be too large, so please make sure you zip them before upload.

Thanks
Srini