SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Reviewing Query Execution Plans
HP NonStop SQL/MX Query Guide—523728-003
4-22
Using Measure
•
The number of times static SQL statements were recompiled and the elapsed time
needed for recompilation
•
The number of times the executor server processes were started up and the
elapsed time to do this
•
The number of open requests issued by SQL and the elapsed time to do this
Statement Execution (SQLSTMT)
The SQLSTMT report provides information for specific statements of modules
executed by an SQL process. For each statement, the Measure product provides these
statistics:
•
The number of times the statement was executed
•
The total elapsed time to execute the statement
•
The number of rows accessed and returned or altered
•
The number of disk reads needed for execution
•
The number and length of messages sent to execute the statement
•
The number of sorts performed and the elapsed time to do them
•
The number of recompilations and the elapsed time to do them
•
The number of timeouts, lock escalations, and lock waits
SQLSTMT entities gather statistics for all statements of a process selected for
measurement; there is one SQLSTMT entity for each statement. The SQLSTMT report
identifies the SQLSTMT section for each statement by the module name and the
statement index. A statement index identifies each SQL statement that can be
measured. This number appears in the generated SQL module definition file, and you
can use it to look up the corresponding Measure SQLSTMT counters.
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 do 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 the number of I/O operations, total time consumed relative to
Note. The run unit reported by Measure is the name of the procedure or COBOL run unit for
SQL/MP. In SQL/MX, the run unit is the name of the SQL module. The length of the name is
128 bytes.
Note. To translate the statement index back into SQL statements, you can also use the
EXPLAIN function. See the statement_index and statement tokens for the ROOT Operator
on
page 7-40.