|
When you have a specific application or request that is problematic, you can perform a request trace analysis to determine the problem. Request trace analysis involves configuring the Database Tracing wizard to narrow diagnostic data gathering to only the user, connection, or request that is experiencing the problem. Then, using the various data viewing tools in Application Profiling mode, identifying any potential conflicts or bottlenecks.
Connect to your database as the DBA.
Choose Mode > Application Profiling.
If the Application Profiling wizard appears, click Cancel to close it.
Select the database you are going to analyze, and choose File > Tracing. If this is the first time you are using the Database Tracing wizard, or if you previously cleared the tracing configuration settings, the wizard starts automatically. If you are presented with a submenu, choose Configure and Start Tracing.
Follow the instructions in the Database Tracing wizard to configure and capture a tracing session. In particular, narrow the Scope to be the users and/or connections for which problems are occurring. The Edit Tracing Levels page provides you with the ability to customize all criteria for the diagnostic data gathered.
Once you are finished configuring tracing details, click Finish.
A tracing session is now in progress. Allow applications to interact with the database for a period of time to allow data to be gathered.
When you are done gathering tracing data, select the database and choose File > Tracing > Stop Tracing With Save.
This terminates the tracing session and stores the captured data in for the tracing session.
Perform the analysis.
In the Application Profiling Details pane, click Open an Analysis File or Connect to a Tracing Database.
The Open Analysis or Connect to Tracing Database dialog appears.
Choose In a Tracing Database, and click Open.
The Connect to a Tracing Database dialog appears.
Specify the user name and password you used to connect to the database you are analyzing, and then click OK.
The Application Profiling Details pane displays information about the tracing database and the tracing sessions stored in it.
From the Logging Session ID dropdown, select the last entry in the list (this is the last session captured).
Details for the tracing session appear in the Application Profiling Details pane.
Click the Database Tracing Data tab at the bottom of the Application Profiling Details pane. You can now select from several tabs that provide you with different views of the data gathered for your analysis. For example, the Summary tab allows you to see all requests executed against the database during the tracing session, including how many times each request was executed, execution duration times, the user who executed the request, and so on. If the list is long and you are looking for a specific request, click the Filtering title bar on the Summary tab and use the SQL Statements Containing field to enter a string to search for in the list.
To view more details about a specific request, right-click the request and choose Show the Detailed SQL Statements for the Selected Summary Statement. This moves you to the Details tab, where more information is presented. Right-click the row containing the request, and additional choices for information are provided, including viewing additional SQL statement, connection, and blocking details.