SQL Trace with EBS

These MOS docs are handy for any performance issues:

How To Use SQL Trace And TKPROF For Performance Issues with EBusiness Suite [ID 980711.1] Author: Luis J. Gonzalez
https://support.oracle.com/epmos/faces/ui/km/DocContentDisplay.jspx?_afrLoop=1008981601705720&id=980711.1&_afrWindowMode=0&_adf.ctrl-state=pvt2x3r2b_74


How To Run Application Collection Tool (ACT/RDA) Diagnostics Test? [ID 559800.1]
https://support.oracle.com/epmos/faces/ui/km/DocContentDisplay.jspx?_afrLoop=1009691371364643&id=559800.1&_afrWindowMode=0&_adf.ctrl-state=pvt2x3r2b_313

Troubleshooting Oracle Applications Performance Issues [ID 169935.1] Author: Carlos Sierra
https://support.oracle.com/epmos/faces/ui/km/DocContentDisplay.jspx?_afrLoop=1100600262021534&id=169935.1&_afrWindowMode=0&_adf.ctrl-state=v9vy1cibx_388

R12: Forms Runtime Diagnostics (FRD), Tracing And Logging For Forms In Oracle Applications [ID 438652.1]
https://support.oracle.com/epmos/faces/ui/km/DocContentDisplay.jspx?_afrLoop=1507179551130647&id=438652.1&_afrWindowMode=0&_adf.ctrl-state=19e01bmqeu_101

R12: How To Trace and TKPROF A Concurrent Program With Performance Issue
1) As System Administrator, go to Concurrent-> programs -> Define, and query program
     with the problem

2) Enable the check box called 'Enable Trace' and save

3) Before starting trace, make sure the following DB parameters are set:
    MAX_DUMP_FILE_SIZE = UNLIMITED
    TIMED_STATISTICS = TRUE
    STATISTICS_LEVEL = ALL

   If they are not set as above, run the following as SYS:
     SQL> alter system set MAX_DUMP_FILE_SIZE=UNLIMITED;
     SQL> alter system set TIMED_STATISTICS=TRUE;
     SQL> alter system set STATISTICS_LEVEL=ALL;

4) Run the report reproducing the issue


5) After the process finishes, get the trace file from the directory set from below:
       SELECT value
         FROM v$parameter
         WHERE name = 'user_dump_dest';  

     then run tkprof for the trace file
           with  sort=exeela,prsela,fchela and explain=apps/appspw.
    Use tkprof from DB Oracle Home, and that the process already finished.
    If the process has not finished the rows column in tkprof will be all zeros and useless.

6) Once the log file, raw trace and sorted tkprof trace files are ready,
     ZIP them into a single file and upload to My Oracle Support (Metalink).

7) Run RDA Collection Test per Note:559800.1/Note:732091.1 for corresponding modules
     for ex., AR and SLA

Another way of Tracing a Concurrent Program:


1. Ensure that patch# 8743459 is applied
(Patch 8743459: 12.1.1: 'SQL TRACE' IN 'DEBUG OPTIONS' IS UNLIKELY WORKING)

2. Next, Navigate to System Administrator - and set the profile - FND: Debug Enabled
     to No - at all levels (site and user) to ensure this is not turned on.

3. Move to Concurrent/Program/Define - query the concurrent program and uncheck the 'Enable trace' box

4. To create the trace:

a. Navigate to System Administrator responsibility
b. Navigate to Profiles->System
c. Query the Profile Option Concurrent: Allow Debugging and set it to Yes at User level
d. Navigate to responsibility to excute the concurrent program
e. From the Requests form, choose the Concurrent Program and set the required Parameters
f. Click the Debug button on the SUBMIT Form that is now enabled. This will open a new window
g. Check the SQL Trace checkbox and specify Trace with Binds and Waits
     - Choose Ok - then it will tell you debug rule has been created - close the window
h. now back at the Submit form - Submit the Concurrent program - let it complete
i. Reset the value of the profile Option Concurrent: Allow Debugging
j. Retrieve the trace file created - it should have the user-name and concurrent request
    number in the filename

Finding Trace Files

Trace files are stored in the Automatic Diagnostic Repository (ADR), in the trace directory under each ADR home. To locate individual trace files within this directory, one can use data dictionary views. For example, find the path to the current session's trace file or to the trace file for each Oracle Database process.


To find the trace file for your current session:
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';
The full path to the trace file is returned.

To find all trace files for the current instance:
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';
The path to the ADR trace directory for the current instance is returned.

To determine the trace file for each Oracle Database process:
SELECT PID, PROGRAM, TRACEFILE FROM V$PROCESS;

To find trace file directory path:
SELECT VALUE FROM SYS.V_$DIAG_INFO WHERE NAME = 'Diag Trace';