SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-8
Evaluating Measure Data
compilation. An SQL section is generated for each SQL statement and is listed in the
compilation output following the program code. The exception is for the statements on
cursors: OPEN, FETCH, and CLOSE cursor statements. The counters of the OPEN,
FETCH, and CLOSE cursor statements all contribute to the counter of the DECLARE
CURSOR section number.
Evaluating Measure Data
Use the SQLSTMT report to form a baseline performance picture, which you can then
use to compare to subsequent versions as you tune your queries.
Optimally, measure each transaction or query in isolation; otherwise, you will not get a
clear view of the transaction of interest. If you do not know which of several
transactions is performing poorly, you can execute each transaction separately,
measure it, and compare performance among the group of transactions.
When reviewing the SQLSTMT reports for poorly performing queries, examine and
isolate queries based on number of I/O operations, total time consumed relative to
other queries, frequency of execution within a single transaction, and other
performance-related measurements. Then generate EXPLAIN plans for the queries;
the plans should help identify reasons for poor performance. Sometimes a specific type
of problem is common to a set of queries.
Stopwatch measurements can also be helpful; when compared to Measure
information, they can reveal network problems or other types of delays.
It can be important to establish response-time requirements for specific queries; this
strategy permits identification of a specific goal and completion framework for tuning.
When evaluating changes to queries, consider other transactions that might be
adversely affected by the change. For example, if you add an index, then compare
performance before and after for insert and update transactions. Consider the volume
of the query being addressed and compare it with the volume of update and delete
transactions.
Using EXPLAIN
An EXPLAIN report describes the execution plan for a DML statement. Each plan is
divided into one or more steps: one for a scan of each table in a FROM clause and one
for each union operator in a query. Each step can involve one or more of these:
•
Scan of a table
•
Join of two or more tables
•
UNION operation
•
Insert into a table
•
Sort operations
•
Parallel execution
•
Sequential buffering