Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Monitoring and Improving Performance » Advanced application profiling using diagnostic tracing » Analyzing diagnostic tracing information

Troubleshooting performance problems Next Page

Detecting slow statements

You can identify which statements the database server spends the most time processing by using the Summary and Detail views. The Summary view groups similar statements together and reports the total number of invocations and the total time spent processing them. SELECT, INSERT, UPDATE, and DELETE statements are grouped together by what tables, columns, and expressions they reference. Other statements are grouped together as a whole (for example, all CREATE TABLE statements appear as a single entry in the Summary view). A statement may appear expensive in the summary view because it is an intrinsically expensive statement, or because, although a cheap statement by itself, it is frequently executed.

The Details view shows each statement that was captured as part of the tracing session, along with the time it took to execute it. The duration shown for each statement is the time the database server spent actually processing the request. A cursor may be left open for a long time (as shown by the presence of a long interval between the start time and the cursor close time), but the duration may be short if the database server was only asked to produce a small result set for this cursor.

You can right-click a statement for more details. The full SQL text of the statement appears, along with details about when it was used and in what context. Note that the text displayed for the statement may not match the original text. If this statement was captured after it was parsed by the database server (either because it is part of a compiled database object, such as a stored procedure or trigger), or because it was only selected for inclusion in the trace due to satisfying a sampling or cost condition, it may appear different than it was originally written. In particular, queries over views may appear drastically different, since the view definitions are often flattened into the queries (expanded inline).

If the statement was a query, the More Details dialog also shows details about the plan used to execute the query. Note that the text representation of the query plan is always captured and always accurately represents the actual access plan used by the database server. Depending on the settings selected for plans, the graphical plan shown may not be the actual plan used by the database server. If the description on the graphical plan is Best Guess Plan, then the database server has re-optimized the query, simulating as much as possible the database server conditions when it was first optimized. Usually, this causes the same plan to be selected. However, you should check that the guess plan matches the actual text plan before relying on it. See Reading execution plans.