SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Measuring Performance
HP NonStop SQL/MX Installation and Management Guide—544536-007
14-9
SQL/MX Measurement Models
After a program begins running, startup costs have already been incurred. The costs
associated with processing the statements are stored in the SQLSTMT entity. The first
time a statement in a procedure executes, overhead is added for setting up the
counters for the procedure.
You can use these SQLSTMT counters to analyze a running process:
•
CALLS stores the number of times the SQL statement was executed.
•
ELAPSED-BUSY-TIME stores the time the process spent executing the SQL
statement. You can calculate the average elapsed time for each call by dividing the
ELAPSED-BUSY-TIME counter value by the CALLS counter value.
Note that the first time a statement in a procedure executes after measurement has
been started, a setup time is included for allocating all the SQLSTMT counters for
the procedure.
•
DISK-READS stores the number of physical disk I/O operations performed for this
statement.
•
RECOMPILES stores the number of times the statement was recompiled. For valid
statements, this number should be zero. If the statement has been recompiled, the
counter for each session would be one because an invalid statement is usually
recompiled only once in a session. If this number is one or greater, you should
consider explicit SQL compiling the program.
•
ELAPSED-RECOMPILE-TIME stores the time spent in recompiling this statement.
The ELAPSED-RECOMPILE-TIME includes the actual compilation time as well as
the disk reads, messages, and NEWPROCESS time to initiate the SQL compiler.
•
RECORDS-ACCESSED stores the number of records accessed by the disk
process or the file system to evaluate statement. If the statement accesses many
records but uses only a few, you can create an index to reduce the number of
records searched before returning records that satisfy the query. If you plan to
create an index for this purpose, be aware that while SQLSTMT gives records
accessed for all files involved in the query and so can suggest the need for an
index, SQLSTMT might not point to an individual file on which to build the index.
You should look at both SQLSTMT and DISKFILE to determine which file is being
accessed.
•
RECORDS-USED stores the number of records inserted, updated, deleted, or read
by the SQL executor through this statement.
•
MESSAGES stores the number of messages sent by the system on behalf of this
statement, including messages sent by the file system to the disk process.
•
MESSAGE-BYTES stores the number of message bytes sent for this statement by
the system. This counter accumulates the number of bytes sent for the messages
reported by the MESSAGES counter.
•
LOCK-WAITS stores the number of times the statement waited for a lock request.
This number should be zero or be quite small. If the number is large, you should