SQL trace for concurrent requsts

Tracing Concurrent Programs

Steps to enable trace for Concurrent Requests: Request Level

1. Log in to the environment from which you want to trace.
2. Set the value for the profile option Concurrent: Allow Debugging œto "Yes" at user level.
3. Choose the appropriate responsibility and concurrent program to be executed.
4. Click on the "Debug Options" button.
5. Enable tracing by selecting the "SQL Trace" Check box, and choose the trace level from the DropDown list. Confirm your selection by clicking the "OK" button.
6. Submit the concurrent program.
7. [Perform Steps 1-6 for each individual concurrent request that needs to be traced.]
8. Write down the request_id of your concurrent program job.
9. Once the concurrent request completes, retrieve the raw trace file using the request_id (from Step 7) or the tracefile_identifier (set by default to the userid).
10. Format the raw trace with TKPROF

The Debug Options button used for tracing individual requests is not available for request sets. You must set the Initialization SQL Statement Custom profile option method (described below) in order to trace request sets. With this method, a trace file will be produced for each program within the request set.

Steps to enable trace for Concurrent Requests - Program Level

1. Log in to the environment from which you want to trace.
2. Choose an appropriate responsibility and select the Concurrent > Program > Define screen.
3. Press [F11] to enter query mode and search for the concurrent program. Press [CTRL][F11] to execute the search.
4. Check the 'Enable Trace' box to turn on tracing for the concurrent program. Note that once checked, anyone who runs this concurrent program will produce a level 8 (wait events) trace.
5. Submit and run the concurrent program.
6. Write down the request_id of your concurrent program job.
7. Go back to the Concurrent > Program > Define screen and uncheck the enable trace box for this concurrent program.
8. Retrieve the raw trace file using the request_id (from Step 6) or the tracefile_identifier (set by default to the userid).
9. Format the raw trace file with TKPROF.

Profile-Based Tracing

An alternative method to enable trace from any location at the application user level is to set the profile option "Initialization SQL Statement - Custom" at user level, as described next.

1. Log in to Forms for the desired application.
2. If you are activating trace for your own account, navigate to the Profile > Personal menu option, press F11 to query, enter "Initialization%" in the Profile Name column, then press Ctrl-F11 to search for this profile option. If you are enabling trace for another user, select +Profile >> System ('Find System Profile Values' screen will pop up).
3. Check 'User' and Type in the Username (in which the account for that user will be traced).
4. Enter 'Initialization%' in the Profile box and press 'Find'.
5. In the User box for "Initialization SQL Statement - Custom", enter the following statement and then press 'Save':

1. It is advisable to specify an identifier to help identify your trace. In this case, TESTING will appear at the end of the trace file name. Quotes in the statement are all single quotes.
2. Log in to the application and page you wish to trace with a suitable username/password, and start your flow or submit your request set. Everything will be traced.
3. Log out of the application once you completed your flow.
4. Go back to the Profile option in the Forms application, delete the Initialization SQL statement, and press 'Save'.
5. Identify and retrieve the trace file using the tracefile_identifier specified in Step 5.
6. Format the raw trace file with TKPROF.

SQL*Plus-Based Tracing

1. Log in to SQL *Plus
2. Run the following query to find out the server process id for your session:

1. Write down the server process ID (Server PID).
2. Turn on and configure tracing by executing the following commands in your SQL *Plus session: