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-10
SQL/MX Measurement Models
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.
•
WRITES stores the number of inserts.
•
UPDATES stores the number of updates.
•
DELETES stores the number of deletes.