Source:
For example, the following is the navigation to enable trace in a form:
1. Goto the Oracle Applications
2. Login
3. Open the form where the error occurs but do not yet cause the error.
4. Enable SQL*Trace by choosing Help > Diagnostics > Trace > Trace with binds
5. A message appears indicating that the trace will be recorded
6. Note the file name and location of the file
7. Now reproduce the error.
8. Once the error occurs, disable trace as soon as possible.
9. Disable SQL*Trace by choosing Help > Diagnostics > Trace > Trace off
10. The same file name and location will be noted in case you need it again.
11. Retrieve the trace file from the DB(user_dump_dest).
For example, the following steps could be used.
1. Goto Sysadmin > Concurrent > Program > Define
2. Query the concurrent program
3. Check the trace box to enable trace
If you require bind variables for your trace file from a concurrent program, a more sophisticated method is required as noted below.
One can enable trace for all actions that a user takes by setting a profile option for the user. This will function when the user logs into the Oracle Applications forms or when logging into the Self Service Web Applications. This method uses an Event Trace.
The instructions for enabling the event trace follow:
1) First enable trace for a specific user:
- Bring up the Core application - System Administrator responsibility
- Move to Profile/System
- Check off the USER box - and enter your username that you are using when getting the error
- Search on the following profile option - 'Initialization SQL Statement - Custom'
Please set this at the user level with the following string:
BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'4269824.999' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;
This must be one complete line of text - so may be best to copy the string into notepad prior to putting it into the profile.
2) Once the profile is set, please change responsibilities and directly go and reproduce the problem.
To locate the trace file, use the following sql in sqlplus:
SQL> select name, value from v$parameter where name like 'user_dump_dest';
- The value is the location of the trace file on the database server.
- This is the trace file created - please tkprof and upload BOTH the raw and tkprof trace file to My Oracle Support.
3) Ensure that the profile option is unset before exiting the Application.
This is a very important step. If this is not unset, unnecessary trace files will be generated.
4) Note that a similar technique can be used for enabling trace with binds for a user in SQL*Plus. The commands would look like the following:
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='577' EVENTS ='10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
SQL> select 'x' from dual; -- or any SQL that you want to trace
SQL> alter session set sql_trace = false;
alter system set max_dump_file_size = 'unlimited';
This will prevent trace being cutoff and not usable.
2.The profile FND: Diagnostics = Yes is required to have the Diagnostics button appear in Self Service menus.
1.IF setup for the first time, THEN exit and sign on again to the SSA
3.Click on Diagnostics button (In 12.2 - click the GEAR icon and choose Diagnostics)
1.Choose Set Trace Level from the drop down, the press Go
2.Choose the trace level - Trace with BINDS (or for performance issue, then Trace with Binds and Waits)
and press Save
3.A single trace number will be shown… This is NOT usually the trace that is needed.
4.As more steps are performed, more trace numbers will be written to this screen and they will be the ones that be for the functionality that is being tested
4.Perform the actions necessary in Self Service Applications - Note: Use the least number of steps possible!
5.Then click on Diagnostics button again
1.Choose Set Trace Level from the drop down, the press Go
2.Now there will be multiple trace file numbers showing on the screen.
3.Write down the trace numbers and then click Disable Trace and press Save (the number will disappear when you press Save)
6.Have the DBA retrieve the trace files from the user_dump_dest of the database.
1. Retrieve the trace file.
2. Issue a command like the following to create a TKPROF version of the trace file. The explain option will look to see how each query is handled by the database in terms of what tables and indexes are referenced.
$ tkprof sys=no explain=apps/
However, TKPROF does not take into account bind variables. Therefore, these are ignored in the output. When you need to analyze bind variables, consider using TraceAnalyzer.
A handy technique when trouble shooting performance issues is to use TKPROF to look at the longest running queries. Since trace files related to performance can be huge, one might spend hours looking through the results to find the queries causing the issue. If you sort the file by the longest running queries first, it makes it much easier to investigate. The following example sorts by longest running queries first with the sort options selected as "sort='(prsela,exeela,fchela)'" and limits the results to the "Top 10" long running queries with the "print=10" command:
$ tkprof sys=no explain=apps/ sort='(prsela,exeela,fchela)' print=10
of your employees. The employee id might be a variable that is changed before you run each SQL. This could be translated to the database as a bind variable so that the same SQL can be used each time but the correct variable can be substituted just before the SQL is run.
If someone requests that you enable bind variables for your trace file, this causes the database to document the substitution variables within the trace file as well as the SQL run. For example, we may see that a select statement ran against the employee table ten times, but without the binds, we would not know what employee was selected each time.
FAQ: Common Tracing Techniques in Oracle E-Business Applications 11i and R12 (Doc ID 296559.1)
Why Can't Users Enable Forms Trace in 12.1.3 ? (Doc ID 1223753.1)
FAQ - How to Use Debug Tools and Scripts for the VCP (aka APS) and EBS Applications (Doc ID 245974.1)
Enable trace in the Oracle Application forms.
One can enable trace through the forms by using the Help menu, choosing the diagnostics menu, trace and then selecting the appropriate trace for your needs. Most commonly if debugging an error, you should at least provide trace with binds. When debugging a performance issue, you may consider using trace with binds and waits.For example, the following is the navigation to enable trace in a form:
1. Goto the Oracle Applications
2. Login
3. Open the form where the error occurs but do not yet cause the error.
4. Enable SQL*Trace by choosing Help > Diagnostics > Trace > Trace with binds
5. A message appears indicating that the trace will be recorded
6. Note the file name and location of the file
7. Now reproduce the error.
8. Once the error occurs, disable trace as soon as possible.
9. Disable SQL*Trace by choosing Help > Diagnostics > Trace > Trace off
10. The same file name and location will be noted in case you need it again.
11. Retrieve the trace file from the DB(user_dump_dest).
Enable trace for a Concurrent Program.
Checkbox
With system administration privileges, a simple way to enable trace in a concurrent program is to review the concurrent program definition and select trace enabled. This will enable trace without binds for all users that run the program.For example, the following steps could be used.
1. Goto Sysadmin > Concurrent > Program > Define
2. Query the concurrent program
3. Check the trace box to enable trace
If you require bind variables for your trace file from a concurrent program, a more sophisticated method is required as noted below.
Debug Options Button! (Binds and Waits)
When submitting a concurrent program in R12.1 and higher, the concurrent program submission screen has a "Debug Options" button where you can enable trace. The button opens a window with various SQL Tracing options including Binds and Waits. The selection applies just to the current run of the program.Enable trace for ALL user actions
How does one enable trace for all actions that occur for a user?One can enable trace for all actions that a user takes by setting a profile option for the user. This will function when the user logs into the Oracle Applications forms or when logging into the Self Service Web Applications. This method uses an Event Trace.
The instructions for enabling the event trace follow:
1) First enable trace for a specific user:
- Bring up the Core application - System Administrator responsibility
- Move to Profile/System
- Check off the USER box - and enter your username that you are using when getting the error
- Search on the following profile option - 'Initialization SQL Statement - Custom'
Please set this at the user level with the following string:
BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'4269824.999' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;
This must be one complete line of text - so may be best to copy the string into notepad prior to putting it into the profile.
2) Once the profile is set, please change responsibilities and directly go and reproduce the problem.
To locate the trace file, use the following sql in sqlplus:
SQL> select name, value from v$parameter where name like 'user_dump_dest';
- The value is the location of the trace file on the database server.
- This is the trace file created - please tkprof and upload BOTH the raw and tkprof trace file to My Oracle Support.
3) Ensure that the profile option is unset before exiting the Application.
This is a very important step. If this is not unset, unnecessary trace files will be generated.
4) Note that a similar technique can be used for enabling trace with binds for a user in SQL*Plus. The commands would look like the following:
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='577' EVENTS ='10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
SQL> select 'x' from dual; -- or any SQL that you want to trace
SQL> alter session set sql_trace = false;
How To Generate and Retrieve Trace Files for SSA (Self Service Applications)
1.Have DBA or someone with APPS access to SQL runalter system set max_dump_file_size = 'unlimited';
This will prevent trace being cutoff and not usable.
2.The profile FND: Diagnostics = Yes is required to have the Diagnostics button appear in Self Service menus.
1.IF setup for the first time, THEN exit and sign on again to the SSA
3.Click on Diagnostics button (In 12.2 - click the GEAR icon and choose Diagnostics)
1.Choose Set Trace Level from the drop down, the press Go
2.Choose the trace level - Trace with BINDS (or for performance issue, then Trace with Binds and Waits)
and press Save
3.A single trace number will be shown… This is NOT usually the trace that is needed.
4.As more steps are performed, more trace numbers will be written to this screen and they will be the ones that be for the functionality that is being tested
4.Perform the actions necessary in Self Service Applications - Note: Use the least number of steps possible!
5.Then click on Diagnostics button again
1.Choose Set Trace Level from the drop down, the press Go
2.Now there will be multiple trace file numbers showing on the screen.
3.Write down the trace numbers and then click Disable Trace and press Save (the number will disappear when you press Save)
6.Have the DBA retrieve the trace files from the user_dump_dest of the database.
Using TKPROF
A trace file can be reviewed using TKPROF. TKPROF reformats the raw data so that it is easier to review. The TKPROF commands are normally run at the operating system command prompt. This will be signified with a $ as this is a common prompt in UNIX.1. Retrieve the trace file.
2. Issue a command like the following to create a TKPROF version of the trace file. The explain option will look to see how each query is handled by the database in terms of what tables and indexes are referenced.
$ tkprof
However, TKPROF does not take into account bind variables. Therefore, these are ignored in the output. When you need to analyze bind variables, consider using TraceAnalyzer.
A handy technique when trouble shooting performance issues is to use TKPROF to look at the longest running queries. Since trace files related to performance can be huge, one might spend hours looking through the results to find the queries causing the issue. If you sort the file by the longest running queries first, it makes it much easier to investigate. The following example sorts by longest running queries first with the sort options selected as "sort='(prsela,exeela,fchela)'" and limits the results to the "Top 10" long running queries with the "print=10" command:
$ tkprof
What is a trace file?
A trace file is a log of SQL run in a particular session or sessions focused on selects, inserts, updates, and deletes. A trace file can be used in many circumstances including reviewing performance, finding tables and views referenced, or finding the root of an error. More details about trace files can be read in the database user's guide related to your version.What are bind variables?
A bind variable is a substitution value used within a trace file. To improve performance, the Oracle database will reuse SQL substituting values for variables as necessary. For example, the same SQL statement could be run for eachof your employees. The employee id might be a variable that is changed before you run each SQL. This could be translated to the database as a bind variable so that the same SQL can be used each time but the correct variable can be substituted just before the SQL is run.
If someone requests that you enable bind variables for your trace file, this causes the database to document the substitution variables within the trace file as well as the SQL run. For example, we may see that a select statement ran against the employee table ten times, but without the binds, we would not know what employee was selected each time.
