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

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Performance improvements, diagnostics, and monitoring » Application profiling tutorials » Tutorial: Diagnosing slow statements

 

Lesson 2: Reviewing statements processed by the database server

You can identify which statements the database server spends the most time processing by using the Summary and Detail tabs, located on the Application Profiling pane in Sybase Central.

Prerequisites

This lesson assumes that you have the roles and privileges listed in the Privileges section at the start of this tutorial: Tutorial: Diagnosing slow statements.

This lesson assumes that you have completed all preceding lessons. See Lesson 1: Creating a diagnostic tracing session.

 Task
  1. In Sybase Central, open the analysis file as follows:

    1. Click Application Profiling » Open Analysis File or Connect to a Tracing Database.

    2. Click In A Tracing Database, and then click Open.

    3. In the User ID field, type DBA.

    4. In the Password field, type sql.

    5. In the Action dropdown list, click Connect to a running database on this computer.

    6. In the Database Name field, type app_profiling.

    7. Click Connect.

    8. If the Application Profiling Details pane does not appear at the bottom of the window, click View » Application Profiling Details.

  2. Examine the statement execution times of statements that were processed during the tracing session, as follows:

    1. On the Status tab in the Application Profiling Details pane, select the most recent ID (highest number) from the Logging Session ID field, and then click the Database Tracing Data tab.

      On the Summary tab, the SQL statements you executed during the session appear. You may see additional statements, because the statements you executed automatically caused other statements to be executed (for example, a trigger).

      The Summary tab groups similar statements together and summarizes the total number of invocations and the total time spent processing them. SELECT, INSERT, UPDATE, and DELETE statements are grouped together by the 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 tab). A statement may appear expensive in the Summary tab because it is an expensive statement, or because it is frequently executed.

      Find statements that are running slowly on your system by examining the Total Time and Maximum Time columns. These provide information about the execution times for each statement processed by the database server.

      For each SQL statement executed during the tracing session, the number of invocations, total time, maximum time, user and statement text are shown.
  3. To view information about the connection that executed a statement, click the Details tab, right-click the statement and click View Connection Details For The Selected Statement.

  4. To view the execution plan used for a SQL statement, click the Details tab, right-click the statement and click View More SQL Statement Details For The Selected Statement.

    The SQL Statement Details window appears, displaying the full text of the statement along with details about the context in which it was used. The text displayed for the statement may not match the original SQL statement you executed. Instead, the SQL Statement Details window displays the statement in its rewritten form, as it was processed by the database server. For example, queries over views may appear different because view definitions are often rewritten by the optimizer when executing the query.

    Click the Query Information tab at the bottom of the SQL Statement Details window to see the execution plan.

Results

You have completed the tutorial on diagnosing slow statements.

 See also