My Journey to the cloud…

In pursuit of excellence….

How to trace in E-Business Suite

As a DBA, the most challenging task comes when we need to work or diagnose the performance issue. Since I have worked extensively on performance tuning, I would like to share different ways to trace Oracle user session.

Tracing is a very handy tool for any DBA to be the first thing to look for when diagnosing any performance related issues.

  • Tracing one’s own session– many times while working on performance issue, I need to take the trace of my own database session. Steps which I follow
    • alter session set statistics_level=ALL (this is the default behaviour in Oracle 10g going forward)
    • alter session set tracefile_identifier = ‘<some_name_to_identify_session>’
    • alter session set sql_trace true –> this will enable level 4 trace OR
    • alter session set events ‘10046 trace name context forever, level x
      • x=4 –> level 4 trace
      • x=8 –> level 8 trace
      • x=12 –> level 12 trace
    • Run the SQL
    • Once the SQL has finished, disable the trace
      • alter session set sql_trace false OR
      • alter session set events ‘10046 trace name context off’
    • Go to the User dump location (user_dump_dest) on the database server and look for file named “tracefile_identifier”
  • Tracing other user’s database session(You MUST know the user session details – SID and Serial#). Different ways are
    • EXECUTE sys.dbms_system.set_ev(<SID>,<Serial#>,10046,<x>,”)
      • x=4 –> level 4 trace
      • x=8 –> level 8 trace
      • x=12 –> level 12 trace
    • EXECUTE dbms_support.start_trace_in_session(sid=><SID>,serial=><Serial#>,Waits=> true, binds=> true)
  • Tracing in oracle E-Business Suite– Different scenarios are
    • User is complaining that it is taking a lot of time while saving the transaction
      • In order to troubleshoot or isolate the issue, we MUST  know the query that runs in the database when  the user click on ‘SAVE’ button and hence should enable the trace before the ‘SAVE’ button is hit.
        • How to enable/disable trace
          • Login to Oracle application
          • Navigate to the required form (You need to enable the trace immediately before the ‘Save’ button is hit. This is done to ensure ONLY the offending SQLs are captured in the trace file)
          • Once on the Form –> Click on ‘Help’ –> Diagnostic –> Trace and Select any of the five options listed there. It will enable the trace and Oracle will prompt you the trace file identifier and its location as well.
          • Do the problematic transaction and once done, disable the trace (Follow the same navigation and select ‘No Trace’
    • Performance issue is reported by user– Not specific to any specific transaction
      • In such scenarios, we need to trace the complete navigation or User activity to identify the offending SQL .This is achieved by enabling the trace using “profile level tracing”
        • Login  to Oracle application
        • Profile –> System –> Check the ‘USER’ check box –> put the username whose transaction is being traced (NEVER DO THIS at SITE level) –> Query for the profile “Initialization SQL Statement – Custom”
        • Update this value for the USER with “BEGIN FND_CTL.FND_SESS_CTL (”,”,’TRUE’,’TRUE’,”, ‘ALTER SESSION SET TRACEFILE_IDENTIFIER =”<any_identifier>” MAX_DUMP_FILE_SIZE = ”UNLIMITED’ ‘ EVENTS=”10046 TRACE NAME CONTEXT FOREVER, LEVEL <level_no>”’); END;”
          • level_no –> could be 4,8 or 12
          • What this entry does
            • sets up a trace file identifier
            • sets the MAX_DUMP_FILE_SIZE as UNLIMITED
            • Enables the Level X trace
          • CAUTION: Be very careful in updating with the above value since any wrong entry can prevent specific user to login to Oracle application
        • Save this profile setting and ask the user to do the transaction. And once done, again query the profile for the specific user and remove the entry
    • Performance Issue for any concurrent program
      • This is normally done through enabling “Enable Trace” check box after querying the concurrent program
      • This way Level 8 trace is generated
      • For enabling Level 12 trace for any concurrent program, either it can be done from database side (as described above under section “Tracing User session” OR can be done from Oracle application front end. How to do it from Front end
        • Set the profile “Concurrent: Allow Debugging” to YES (ALWAYS DO this at USER level)
        • Login to application using the same responsibility as used to run the concurrent program
        • On the request submission screen, “Debug option” would be enabled (otherwise it is disabled)
        • Click on the “Debug Option” button and select the check box “SQL Trace”
    • Tracing SSWA/OAF application in Oracle E-Business Suite
      • You need to set the profile – “FND:Diagnostic” to YES (ALWAYS do at USER level)
      • Once done, navigate to the OAF page or SSWA page
      • You will see “Diagnostic” link on the top right cornet of the page
      • Click on the link
      • It will give you options to select – select “Set Trace Level”
      • Click on GO
      • Next page will give you the option to select the kind of trace you want and once selected, trace file identified will be displayed on the screen. You need to make a note of this.
      • Now do the required transactions and once done, Disable the trace (Diagnostic–>set trace level –> Disable Trace)
      • Diagnostic link will be visible as long as profile value is set to YES



8 responses to “How to trace in E-Business Suite”

  1. good one. I am started working on performance tuning, could you please help me to understand it.

    1. Thanks Manoj.
      Let me know what help you need to understand.

  2. Really nice blog Anand! Will try enabling the trace any my end. Will come back any questions or doubt.

  3. Nice and useful Info. Thanks Anand.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

About Me

I’m a Hands-On Technical & Entrprise Solutions Architect based out of Houston, TX. I have been working on Oracle ERP, Oracle Database and Cloud technologies for over 20 years and still going strong for learning new things.

You can connect me on Linkedin and also reach out to me

I am certified for 8x AWS, OCP (Oracle Certified Professionals), PMP, ITTL and 6 Sigma.


This is a personal blog. Any views or opinions represented in this blog are personal and belong solely to the blog owner and do not represent those of people, institutions or organizations that the owner may or may not be associated with in professional or personal capacity, unless explicitly stated.
All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site.

The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information. Any script available on the blog post MUST be tested before they are run against Production environment.


%d bloggers like this: