“Initialization SQL Statement – Custom” @ USER LEVEL Tracing

In Oracle Apps when we have issues that needs in depth analysis including what is happening in the background such as database inserts, updates, selects or deletes; tracing needs to be enabled whether via user, responsibility or system level.

Here are the steps to enable trace to try to find other details:

1. Please Navigate to System administrator:Profile ->System and Set the profile GL Debug mode to No for your General Ledger responsibility.

2.Set the profile option “Initialization SQL Statement – Custom” at the USER LEVEL ONLY, for the user that will perform the test, to the value below.
The value should be entered all on one line and be sure to get the quotes exactly as indicated.
Exact syntax is important. Note that there are no double quotes in this command.

begin fnd_ctl.fnd_sess_ctl(”,”,’TRUE’,’TRUE’,’LOG’,’ALTER SESSION SET
TRACEFILE_IDENTIFIER=”joey_trace”’); end;

For more details on this profile usage please review Note 135389.1: Using profile option ‘Initialization SQL Statement – Custom’.

3. After Setting up the above profile option, log off from applications, login once again.
Run the problematic program that’s having the issue.
Close the application once the process is over.

4. After the trace is run ensure that the profile option is blanked to stop generating traces.

5. To locate the trace file run the following query with the Concurrent Request Id:

select ORACLE_PROCESS_ID from fnd_concurrent_requests
where REQUEST_ID = &request_id;

You should then search for the trace files in the trace file directory containing the value returned by the above sql.
For exxample if the above sql returns 1234, then you should search for files like *1234* in the trace file directory.

The trace file directory is given by:

select value from v$parameter where name = ‘user_dump_dest’;

6. Create the tkprof of the trace file using the following command within OS(linux) console:

tkprof sys=no explain=apps/apps sort='(prsela,exeela,fchela)’

sys=no           TKPROF does not list SQL statements run as user SYS.
prsela = elapsed time parsing
exeela = elapsed time executing
fchela  = elapsed time fetching

TKPROF Usage – Quick Reference (Doc ID 29012.1)


About this entry