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

Measuring Performance
HP NonStop SQL/MX Installation and Management Guide—523723-004
15-9
SQL/MX Measurement Models
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 could create an index to reduce the number of
records searched before returning records that satisfy the query.
•
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
examine the cause. You might consider a finer locking granularity (for example,
row locks instead of generic locks or table locks) or redesigning the database.
•
ESCALATIONS stores the number of times a record lock was escalated to a file
(table) lock. This number should be zero. If this number is greater than zero, you
should consider using a table lock for the program.
Database Access Costs
Use the FILE entity to measure database access costs for SQL tables and indexes.
Use these counters to analyze disk processing costs for the database to determine the
cost of queries:
•
RECORDS-USED stores the number of rows returned to the SQL executor on
reads, inserts, writes, updates, or deletes.
•
RECORDS-ACCESSED stores the number of rows read by the disk process or file
system to return the RECORDS-USED value. RECORDS-ACCESSED should