Understanding SQL Activity

From DBArtisan
Jump to: navigation, search

Go Up to Performance Analyst for IBM DB2 for Linux, Unix and Windows Tutorial

Performance Analyst offers plenty of insight into SQL performance and execution patterns. Each one of the home pages depicts the current levels of SQL activity as determined by a variety of different metrics for that performance category. For example, Performance Analyst might show that rows reads account for 75% of all SQL statement I/O on the database.

Getting Details on Top SQL

Getting details on identified SQL statements is easy in Performance Analyst. For example, if you would like to see further information on a SQL statement identified on the Performance Analyst Home page, do the following:

  1. In the SQL Analysis section, position the pointer over Hash Values.
  2. Double-click or right-click, and then select Details.
    Performance Analyst opens the Top SQL summary page that shows a breakdown of all top SQL statements along with their execution statistics

If you would like to see the full SQL text of a SQL statement, do the following

  1. In the Top SQL Details section, double-click a statement.
    Performance Analyst opens the SQL Details tab of the Top SQL view to display the entire SQL statement and the execution metrics. While on this pane, you can also perform an EXPLAIN of the SQL statement and examine its access path information.
    Note: To automatically tune a problem SQL statement if you have IDERA SQL Tuner installed on your workstation, do the following:
  2. On the SQL Details tab, select a problem statement, and then click the Tune SQL button.
    SQL Tuner opens and you can begin a tuning session.

Proceed to Session 6: Using Operating System Performance Analysis