Appendix C. DB2 Performance Expert For Business Intelligence 567
Understanding long-running queries
In this section, we discuss how to monitor long-running queries.
Identifying the top 10 statements
Whether you want to tune you BI system or you want to find the source for poor
performance in general, you need a starting point. A good way to start is to find
the top 10 statements with regard to execution time. This can easily be achieved
by opening the Statistics Details window and accessing the Dynamic SQL
Statements pane. Click the Receive statement cache information button at the
bottom of the pane, and PE will request and display this data. Now sort the Avg.
time per execution column. You might also want to use other columns to find the
top 10 statements, such as the
Executions and Elapsed time columns
(Figure C-44 on page 568).
You can also find the top 10 statements by examining the performance history. To
do so, switch to history mode and scroll to the point in time for which you want to
perform this check.
568 DB2 Performance Expert for Multiplatforms V2.2
Figure C-44 Identify the top 10 statements
Linking top 10 statements to execution details
The dynamic SQL statement information shown in the previous section
represents aggregated data over all executions of these statements. This means
that it is not directly possible to drill down to the applications (as displayed in the
application summary) that executed these statements.
But you can leverage the Query facility in Performance Warehouse to define and
execute a query that shows you all entries when one of the top 10 statements
has been captured in the application summary. To do so:
1. Open the Performance Warehouse - Analysis window, go to Query groups,
and create a private group, if one does not already exist.
Appendix C. DB2 Performance Expert For Business Intelligence 569
2. Right-click the group and select Create. Give the new query a name and
description and paste the query shown in Example C-1 into the appropriate
entry field on the Definition pane.
Example: C-1 SQL to shows entries on the top 10 statements
WITH LAST(last_ts) AS
(SELECT MAX(HT_TIMESTAMP)
FROM DB2PM.HISTORYTOC
WHERE HT_DATA = 'DYNAMICSTATEMENTCACHE'
), TOP10(statement, ms_per_execution, top) AS
(SELECT DISTINCT sql.STMT_TEXT, sql.ATIMEP_EXECUTIONS, ROW_NUMBER( ) over (
ORDER BY ATIMEP_EXECUTIONS DESC
)
AS TOP
FROM DB2PM.DYNSQL sql, LAST
WHERE sql.INTERVAL_TO = LAST.last_ts
AND MEMBER_ID = - 2
ORDER BY ATIMEP_EXECUTIONS DESC
FETCH FIRST 10 ROWS ONLY
) SELECT TOP10.top top_stmt_number, MAX(applstmt.INTERVAL_TO)
last_captured_at
, applstmt.AGENT_ID application_handle, applstmt.STMT_START
statement_started_at, applstmt.STMT_TEXT text
FROM DB2PM.STATEMENT applstmt, TOP10
WHERE applstmt.STMT_TEXT = TOP10.statement
AND MEMBER_ID = - 2
GROUP BY applstmt.STMT_START, applstmt.STMT_TEXT, applstmt.AGENT_ID,
top10.statement, top10.top
ORDER BY top10.top
3. Click OK to save the query.
570 DB2 Performance Expert for Multiplatforms V2.2
4. Right-click the query and select Execute. In the menu that opens, click
Execute again (Figure C-45). Depending on how much data you have
recorded in the history, this query might run for several minutes.
Figure C-45 Execute SQL saved in PWH
When the query finishes running, the results shown in Figure C-46 on
page 571 are displayed. These results show one row per point in time when
an application executed one of the top 10 statements.
Appendix C. DB2 Performance Expert For Business Intelligence 571
Figure C-46 View SQL execution result
Get DB2 Performance Expert for Multiplatforms V2.2, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
