20
SQL query tuning and creating indexes against a database can help to obtain maximum performance from IBM Tivoli service management products. When using DB2 as the database platform for IBM Tivoli service management products, DB2 snapshots are one of the primary mechanisms you can use for ensuring that queries and indexes are tuned appropriately.
DB2 snapshots provide general statistics and information about all applications, tables, table spaces, buffer pools, and locks for a specified database. Since snapshots only capture a point in time, you must make sure that the snapshot is taken while transactions are occurring that may be causing performance issues.
The basic set of commands needed to take a DB2 snapshot is as follows:
Note
that there is overhead with the monitor switches, so they should always
be enabled prior to taking the snapshot, and then disabled after the
snapshot data is gathered.- db2 update monitor switches using bufferpool on lock on sort on statement on table on uow on
- db2 -v reset monitor all
- Allow the system to process for a period of time (e.g. 15 minutes)
- db2 -v get snapshot for dbm > snapshot.txt
- db2 -v get snapshot for all on database-alias >> snapshot.txt
- db2 update monitor switches using bufferpool off lock off sort off statement off table off uow off
The output from the above commands is stored in text readable format. However, parsing the file and finding the information can be tedious at best.
To assist clients with analyzing performance data, IBM Tivoli service management products recently released the IBM Performance Analyst tool which can be downloaded at: http://ibm.biz/BdxDbg. One of the features of the tool is DB2 snapshot file analysis.
Once you have installed the tool and taken a DB2 snapshot, it is quite easy to open the snapshot in Performance Analyst and look for potential issues.
First, launch the Performance Analyst tool:
Then, click File --> Open and select the DB2::Snapshot plug-in and click OK:
Navigate to the snapshot file, select it, and click Open:
Performance Analyst will process the snapshot file and present you with a window similar to the following:
You
can now click through the various tabs to analyze the data collected in
the snapshot. You can also filter the data and show only the data
flagged as having issues by clicking the Only Alerts checkbox and the
bottom of the window:
Cells
highlighted in pink are considered to be warnings, while cells
highlighted in red are considered to be critical alerts. Alerts are
generated based on rules written for each plug-in. The tool comes with a
set of rules based on IBM Tivoli service management products best
practices; however, these rules can be modified or new rules added if
desired.
Context-help is available for all snapshot metrics. Simply double click on any cell and a help window appears showing the corresponding DB2 information center entry for that cell. For example, double-clicking on hash_join_overflows in the sample above would open a window showing the following information:
Context-help is available for all snapshot metrics. Simply double click on any cell and a help window appears showing the corresponding DB2 information center entry for that cell. For example, double-clicking on hash_join_overflows in the sample above would open a window showing the following information:
Now, let’s take a look at the Statement tab for the snapshot file:
By
default, the tool shows the top 25 statements ordered by total CPU
utilization; however, you can choose to show more rows, filter the data,
show only those rows with alerts, and order by any column by clicking
on the column header.
Also note that identical statements have been grouped with the top-level “root” row showing the query in a parameterized format. To see an example of the statement with actual values, just click the twistie next to the statement in the num_executions column.
In the example below, the top consuming SQL statement is:
Also note that identical statements have been grouped with the top-level “root” row showing the query in a parameterized format. To see an example of the statement with actual values, just click the twistie next to the statement in the num_executions column.
In the example below, the top consuming SQL statement is:
select * from incident where status not in (select value from synonymdomain where maxvalue in ('RESOLVED','CLOSED') and domainid in 'INCIDENTSTATUS') and (internalpriority=1 or internalpriority=2) for read only with UR
This
statement was identified as a StartCenter portlet query that was written
inefficiently. By re-writing the query to eliminate the use of NOT IN,
the client was able to significantly improve the performance of this
query, which in turn had a positive impact on performance for the entire
system.
Hopefully, this example has illustrated the power of IBM
Performance Analyst in assisting with DB2 snapshot analysis. And,
remember, this is just one of the features of the tool for assisting
with analyzing performance data.
For more information on IBM Performance Analyst, see the announcement blog entry written by Brian Tsai.
