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-6
Statistics and Reports for NonStop SQL/MX
Statistics and Reports for NonStop SQL/MX
Use the Measure entities to gather statistics on an SQL/MX database and application
programs. After gathering the statistics, you can generate reports about the statistics.
Information gathered by the entities is described next.
SQLPROC Statistics
The SQLPROC report provides information about specific statistics concerning
recompilations, NEWPROCESS calls, and opens of files or processes.
You can monitor automatic recompilation time with the SQLPROC report, but not for
the purpose of determining the best compiler option. SQL/MX supports no compiler
options and only recompiles on demand when an SQL statement needs to be
automatically recompiled at run time.
SQLSTMT Statistics
The SQLSTMT report provides information for specific statements of an SQL process.
SQLSTMT entities gather statistics for all statements of a process selected for
measurement. These statistics encompass the sum of activity on all files used by the
SQL query.
One SQLSTMT entity exists for each statement. The SQLSTMT report contains the
module name and the statement index that you can use to identify the statement in a
compiled module. See the embedded module definition or the module definition file for
a list of statement indexes and associated statement names. For a dynamic SQL
statement, the module name appears as SQLMX^EXECUTE_statement-name, and
the statement index is zero.
The SQLSTMT report gathers statistics on these measurements:
•
Busy time
•
Calls
•
Disk reads
•
Attempts at escalation of locks (successful or not)
•
Lock waits
•
Message activity
•
Records accessed
•
Records used
Stream access statements do not collect SQLSTMT statistics, but FILE statistics are
collected.
FILE Statistics
Monitor database files with the FILE entity. The FILE report provides information on all
partitions of a specific file and record use by a user process. The FILE report gathers
statistics on these measurements: