Investigating performance trends in SQL statements

You can use the Trend view to investigate performance trends in high-cost SQL statements. The Trend view displays statistics from the SQL Performance Performance Advisor Database (PADB) tables for a selected time frame. By default, the Trend view shows statistics from the first PADB table where it finds data. For more information, see Performance Advisor Database tables. The default time frame depends on the PADB table where it found data. For more information, see Timeframes.

Before you begin

To use the Trend view, the BMC AMI Command Center superuser must define the location of the PADB. For more information, see Defining the location of the Performance Advisor Database.

To investigate performance trends in SQL statements

From one of the following apps, select a row and click Trend:
- SQL Analysis
- SQL Alerts

Use the following table to investigate the performance of the SQL statement:

Purpose	Control	Description
Choose a PADB table as the source of the statistics		From the Performance Advisor Database table list, select a PADB table. For more information about the PADB tables, see Performance Advisor Database tables
Choose a time frame for the graphs		From the Timeframe list, select one of the following values: 7 days 15 days 30 days 90 days 1 year The available values depends on the PADB table selected. For more information, see Timeframes.
Switch graphs between total and average statistics		Perform one of the following actions: To view total CPU time, Elapsed time, and GETPAGES, select Total To view average CPU time, Elapsed time, and GETPAGES, select Average
View detail statistics for a point on the graph		On any of the graphs, click a point.
View the coordinates of a point on the graph		Hover the cursor over a point on the graph.
Select a statistics group		Select one of the following values from the statistics group list: General information High-level statistics High/low watermarks Rows processed Buffer and I/O details Wait time details The values shown depends on the PADB table selected.
Return to the calling app		Click Back .

Performance Advisor Database tables

You can display data from the following PADB tables. The tables are listed in the order that BMC AMI Command Center searches for data when the Trend view opens.

Table name	Apptunemonitoring mode	Description
DAILY_STMT_STATISTICS	Advanced	Statistics per statement per day
WEEKLY_STMT_STATISTICS	Advanced	Statistics per statement per week
MONTHLY_STMT_STATISTICS	Advanced	Statistics per statement per month
STMT_STATISTICS	Advanced	Statistics per statement per defined collection interval
DAILY_BASIC_STMT_STATISTICS	Standard	Statistics per statement per day
WEEKLY_BASIC_STMT_STATISTICS	Standard	Statistics per statement per week
MONTHLY_BASIC_STMT_STATISTICS	Standard	Statistics per statement per month
BASIC_STMT_STATISTICS	Standard	Statistics per statement per defined collection interval

Timeframes

The following table shows the Timeframe list items that are available based on the PADB table selected:

Table	7 days	15 days	30 days	90 days	1 year	Default
DAILY_STMT_STATISTICS	X	X	X	X	X	90 days
DAILY_BASIC_STMT_STATISTICS	X	X	X	X	X	90 days
WEEKLY_STMT_STATISTICS			X	X	X	90 days
WEEKLY_BASIC_STMT_STATISTICS			X	X	X	90 days
MONTHLY_STMT_STATISTICS				X	X	90 days
MONTHLY_BASIC_STMT_STATISTICS				X	X	90 days
STMT_STATISTICS	X	X				7 days
BASIC_STMT_STATISTICS	X	X				7 days

Statistics groups

The statistics groups available in the right panel of the Trend view depend on the PADB table Apptune mode. The following table shows which values are available for both Apptune modes:

Group	Advanced	Standard
General	X	X
High-level stats	X	X
High/low watermarks	X
Rows processed	X
Buffer and I/O details	X
Wait time details	X	X

Where to go from here

Use the Performance perspective to analyze an SQL statement with poor performance. For more information, see Tuning SQL.