How to Generate SQL Level traces

adchkcfg.sh/cmd

应用层: /bin 数据库层: /appsutil/bin

这个脚本可以在运行AutoConfig之前运行, 用来查看运行AutoConfig的变化. 这将生成一个报告,显示现有的配置和运行AutoConfig之后的配置之间的差别. 更多信息

adchkcfg.sh/cmd

应用层: /bin 数据库层: /appsutil/bin

这个脚本可以在运行AutoConfig之前运行, 用来查看运行AutoConfig的变化. 这将生成一个报告,显示现有的配置和运行AutoConfig之后的配置之间的差别. 更多信息

How to Generate SQL Level traces (Level 4, Level 8, Level 12) for Performance Issues (Doc ID 364479.1)

To Bottom

---

Modified:09-Aug-2013Type:HOWTO

Applies to:

Oracle Learning Management - Version 11.5.1 to 12.0 [Release 11.5 to 12.0]
Oracle US Federal Human Resources - Version 11.5.0 to 12.0 [Release 11.5 to 12.0]
Oracle iRecruitment - Version 11.5.1 to 12.0.0 [Release 11.5 to 12.0]
Oracle Human Resources - Version 11.5.1 to 12.0 [Release 11.5 to 12.0]
Oracle Advanced Benefits - Version 11.5.10.2 and later
Information in this document applies to any platform.
Checked for relevance on 17-May-2011

Goal

How to Generate SQL Level traces (Level 4, Level 8, Level 12) for Performance Issues

Solution

New Page 1SQL Level Trace OptionsHow to Generate SQL Level traces (Level 4, Level 8, Level 12)

Oracle Support Services and Oracle Development generally require a SQL Level trace in order to further troubleshoot performance issues on forms. The trace output is used to identify what SQL statement(s) is/are non-performant. 

Different levels of SQL tracing can be performed to retrieve different types/amounts of data:

• Regular (Level 1 – standard/default level)
• Level 4 (standard + binds)  - A Level 4 trace provides contains details of the actual contents of all the 'bind variables' passed to each SQL statement.
• Level 8 (standard + waits) - A Level 8 trace provides the default or regular trace information as well as displays a list of all database wait events. (Database wait events list the reasons if the Elapsed time is greater than the CPU time in the tkprof report. The trace output is used to identify what SQL statement(s) is/are non-performant.
• Level 12 (standard + binds and waits) - A Level 12 trace provides both binds and waits and are also typically requested for performance issues.

 

 

Development usually requires a Level 12 Trace for diagnosing performance issues.

SQL tracing can be initiated on a forms or process basis. 



Forms-Based SQL Trace

Instructions for the forms-based SQL trace are listed below.

Help >Diagnostics > Trace >

SQL trace options for forms:

• Regular (Level 1 – standard/default level)
• Level 4 (standard + binds)
• Level 8 (standard + waits)
• Level 12 (standard + binds and waits)

 

To initiate a SQL level trace from a form within the application, perform the following steps (note: the trace has to be ‘turned on’ prior to you performing the action that is non-performant):

 

1.   Navigate to Help -> Diagnostics -> Trace and click next to ‘Trace with Waits’. (Note: if Support requests a Level 12 trace, click next to ‘Trace with Binds and Waits’.) Enter the APPS user password if prompted (consult your DBA if necessary).

2.   You will then receive a Note stating the following: ‘You have enabled SQL tracing for this form and any other forms you open. Your trace file is located at //.trc.’  Click on OK.  SQL Trace is now ON.

3.   Perform the action or series of steps needed to recreate the problem.

4.   When finished, you need to turn the trace OFF.  Navigate to Help -> Diagnostics -> Trace and click next to No Trace.

5.   You will then receive a Note stating the following: ‘SQL Tracing is now disabled.  Your trace file is located at //.trc.’  Important: Record the trace file location!!

6.   Click on OK.  SQL Trace is now OFF.

Provide the location of the trace file to your DBA if necessary.  (Raw trace files are normally created in the ‘user_dump_dest’ directory.) You or your DBA will need to be able to access this trace file to perform the next step: format the output using the tkprof utility. 

References:

More detailed instructions for generating a SQL trace can be found in MyOracleSupport
Note 169935.1 - Troubleshooting Oracle Applications Performance Issues
Note 171647.1 - Tracing Oracle Applications using Event 10046.

Processed -Based SQL Trace

Instructions for the processed-based SQL trace are listed below.

SQL trace options for forms:

• Regular (Level 1 – standard/default level)

Level 4 (standard + binds)
Level 8 (standard + waits)
Level 12 (standard + binds and waits)

To initiate a SQL level trace from a process, perform the following steps (Note: the trace has to be ‘turned on’ prior to you performing the action that is non-performant):

Responsibility:  System Administrator .

1. Navigate:   Profiles -> System
2. Query the Profile 'Initialization SQL Statement - Custom'
3. Set this profile option at user level to following value.
   
   
   BEGIN FND_CTL.FND_SESS_CTL('','', 'TRUE', 'TRUE','','ALTER SESSION SET
   EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 8 '||'''');
   END;
   
   
   In the above example, this will trace all the sessions of that user at level 8. To specify a different level, change the 'Level 8' to the appropriate tracing level required.
4. Make sure to reset the profile to NULL after the traces.

TKPROF Output for the Raw Trace File

You will often hear Support Services request both the ‘raw’ and ‘tkprof’d’ trace output.  When you perform the trace in the step above, this creates a ‘raw’ or unformatted output file.  To be able to ‘read’ the file, the raw trace now needs to be converted to a formatted file using the tkprof utility.  The utility tkprof ‘reads’ the raw trace file and converts it to a more readable format. 

Support Services also usually requires the ‘Explain Plan’ be included.  The Explain Plan shows the execution path the CBO (Cost Based Optimizer) has taken for each SQL statement.  The performance issue could be caused by the database using an incorrect or expensive plan, i.e. it is not behaving as expected.  Oracle HCM Development requires the following sort options be used: fchela, exeela, prsela.  Generally speaking, these options are used to sort the trace so that the most expensive (or least performant) SQL statements appear first. 

Note: TKPROF reports must be generated on the same database instance where the raw SQL Trace was created.  Additionally, the TKPROF utility should be the same version as the database.

 

The command to the tkprof utility is:  

tkprof .trc .prf explain=apps/pw@yourdb sort='(fchela exeela prsela)'

• .trc -is the name of the trace file. 
• .prf -  is the name you are giving the tkprof output file which is to be created.  
• explain -  is the Explain Plan option which requires the APPS username, password (and name of the database SID). 
• sort -  refers to the sort options described above.

 
Note: the above command requires read/write access to the location of the raw trace and where the formatted output will be written