Applies to:
Oracle Learning Management - Version 11.5.1 to 12.0 [Release 11.5to 12.0]
Oracle US Federal Human Resources - Version 11.5.0 to 12.0 [Release11.5 to 12.0]
Oracle iRecruitment - Version 11.5.1 to 12.0.0 [Release 11.5 to12.0]
Oracle Human Resources - Version 11.5.1 to 12.0 [Release 11.5 to12.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 SQLLevel traces (Level 4, Level 8, Level 12)
Oracle Support Services and OracleDevelopment generally require a SQL Level trace in order to furthertroubleshoot performance issues on forms. The trace output is usedto identify what SQL statement(s) is/arenon-performant.
Different levels of SQL tracing can beperformed to retrieve different types/amounts of data:
- Regular (Level 1 – standard/defaultlevel)
- Level 4 (standard + binds) -A Level 4 trace provides contains details of the actual contents ofall the 'bind variables' passed to each SQL statement.
- Level 8 (standard + waits) - A Level 8 traceprovides the default or regular trace information as well asdisplays a list of all database wait events. (Database wait eventslist the reasons if the Elapsed time is greater than the CPU timein the tkprof report. The trace output is used to identify what SQLstatement(s) is/are non-performant.
- Level 12 (standard + binds and waits) - ALevel 12 trace provides both binds and waits and are also typicallyrequested for performance issues.
Development usually requires a Level 12 Trace for diagnosingperformance issues.
SQL tracing can be initiated on a forms or processbasis.
Forms-Based SQLTrace
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 aform within the application, perform the following steps (note: thetrace has to be ‘turned on’ prior to you performing the action thatis non-performant):
1. Navigateto Help -> Diagnostics -> Trace and click next to ‘Trace withWaits’. (Note: if Support requests a Level 12 trace, click next to‘Trace with Binds and Waits’.) Enter the APPS user password ifprompted (consult your DBA if necessary).
2. You willthen receive a Note stating the following: ‘You have enabledSQL tracing for this form and any other forms you open. Your tracefile is located at //.trc.’ Click onOK. SQL Trace is now ON.
3. Performthe action or series of steps needed to recreate the problem.
4. Whenfinished, you need to turn the trace OFF. Navigate to Help -> Diagnostics -> Trace and click next toNo Trace.
5. You willthen receive a Note stating the following: ‘SQL Tracing is nowdisabled. Your trace file is located at//.trc.’ Important: Record the tracefile location!!
6. Click onOK. SQL Trace is now OFF.
Provide the location of the trace file to your DBA ifnecessary. (Raw trace files are normally createdin the ‘user_dump_dest’ directory.) You or your DBA will need to beable to access this trace file to perform the next step: format theoutput using the tkprof utility.
References:
More detailed instructions for generating a SQL trace can be foundin MyOracleSupport
Note 169935.1 - Troubleshooting Oracle Applications PerformanceIssues
Note 171647.1 - Tracing Oracle Applications using Event10046.
Processed -BasedSQL Trace
Instructions for the processed-based SQL trace are listedbelow.
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 followingsteps (Note: the trace has to be ‘turned on’ prior to youperforming the action that is non-performant):
Responsibility: System Administrator .
- Navigate: Profiles ->System
- Query the Profile 'Initialization SQL Statement - Custom'
- Set this profile option at user level tofollowing value.
BEGIN FND_CTL.FND_SESS_CTL('','', 'TRUE', 'TRUE','','ALTER SESSIONSET
EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL8 '||'''');
END;
In the above example, this will trace all the sessions of that userat level 8. To specify a different level, change the 'Level 8' tothe appropriate tracing level required.
- Make sure to reset the profile to NULL after the traces.
TKPROF Output forthe Raw Trace File
You will often hear Support Services requestboth the ‘raw’ and ‘tkprof’d’ trace output. Whenyou perform the trace in the step above, this creates a ‘raw’ orunformatted output file. To be able to ‘read’ thefile, the raw trace now needs to be converted to a formatted fileusing the tkprof utility. The utility tkprof‘reads’ the raw trace file and converts it to a more readableformat.
Support Services also usually requires the‘Explain Plan’ be included. The Explain Planshows the execution path the CBO (Cost Based Optimizer) has takenfor each SQL statement. The performance issuecould be caused by the database using an incorrect or expensiveplan, i.e. it is not behaving as expected. OracleHCM Development requires the following sort options be used:fchela, exeela, prsela. Generally speaking, theseoptions are used to sort the trace so that the most expensive (orleast performant) SQL statements appearfirst.
Note: TKPROF reports must be generated onthe same database instance where the raw SQL Trace wascreated. Additionally, the TKPROF utility shouldbe the same version as the database.
The command to the tkprof utility is:
tkprof .trc .prfexplain=apps/pw@yourdb sort='(fchela exeela prsela)'
- .trc -is the name of the tracefile.
- .prf - is the name you aregiving the tkprof output file which is to becreated.
- explain - is the ExplainPlan option which requires the APPS username, password (and name ofthe database SID).
- sort - refers to the sortoptions described above.
Note: the above command requires read/write access to the locationof the raw trace and where the formatted output will bewritten
To Bottom
