SQL/MP Installation and Management Guide

Measuring Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
13-3
SET SESSION STATISTICS and DISPLAY
STATISTICS Commands
SET SESSION STATISTICS and DISPLAY STATISTICS
Commands
SQLCI provides the STATISTICS option of the SET SESSION command; this option
displays the statistics after each DDL, DML, or DCL statement executed in the session.
You can also use the DISPLAY STATISTICS command to get statistics on a single
statement. The DISPLAY STATISTICS command displays statistics for the immediately
preceding DDL, DML, or DCL statement.
To obtain statistics, you use either of these commands in your SQLCI session:
>> SET SESSION STATISTICS ON; --Enter before statements
>> DISPLAY STATISTICS; --Enter after a statement
The statistics displayed after each statement appear in this format, preceded by
information about statement execution timing:
Records Records Disk Message Message Lock
Table Name Accessed Used Reads Count Bytes WE
Elements of the display follow:
•
Table Name is the name of the table for which statistics are being displayed.
•
Records Accessed gives a count of the number of records accessed in each table.
This count includes records examined by the disk process, the file system, and the
SQL executor.
•
Records Used gives a count of records actually used by the statement. For
INSERT and FETCH operations, the count is always 0 or 1. For UPDATE,
DELETE, and SELECT operations, the count can be greater than 1.
•
Disk Reads gives a count of the number of disk reads caused by accessing this
table.
•
Message Count gives a count of the number of messages sent to execute
operations on this table. For example, a FETCH operation through a secondary
index generally sends two messages.
•
Message Bytes gives a count of the message bytes sent to access this table.
•
Lock displays flags indicating that lock waits occurred (W) or that lock escalations
occurred (E) for the table. If this field is blank, no locks were obtained during the
processing of this statement.
For example, the DISPLAY STATISTICS command might present this data: