My Journey to the cloud…

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

-Anand