SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

• 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 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.
Since most, if not all, attempts at lock escalation fail, the number should usually be a 1. If the
number is greater than 1, 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 always be the same or greater
than RECORDS-USED. The ratio between RECORDS-USED/RECORDS-ACCESSED is the
selectivity of the statement.
A query is most efficient when the number of records used is the same or slightly lower than
the number of records accessed. If the number of records accessed is much larger than the
number used, the query is accessing many unnecessary rows. To improve the selectivity,
consider building an index if it can be done.
• DISK-READS stores the number of physical disk reads performed on the file.
• LOCK-WAITS stores the number of times a call to the disk process waited on locked data.
• ESCALATIONS stores the number of attempts made to escalate a record lock to a file (table)
lock.
• READS stores the number of fetches.
288 Measuring Performance