SQL/MP Installation and Management Guide

Measuring Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
13-9
SQL/MP Measurement Models
be required on distributed nodes or that compiler or catalog manager timeouts
have occurred.
The counter SQL-NEWPROCESS-TIME contains the amount of time spent waiting
for the call to NEWPROCESS to complete and is included in the total startup time.
•
OPENS contains the number of calls to open tables that were required by this
program. The elapsed time spent executing the opens is stored in OPEN-TIME.
After an SQL program is started, the files are open and remain open for the
duration of the session.
Execution Costs
Use these SQLSTMT counters to analyze the execution costs of a running SQL/MP
process. These counters provide information on a statement basis. For counters that
have the same names as counters for database access costs (described in the
following subsection), you can directly compare the statement values with the table
values returned by those counters.
After a program begins running, startup costs have already been incurred. The costs
associated with processing the statements are stored in the SQLSTMT entity. The first
time a statement in a procedure executes, overhead is added for setting up the
counters for the procedure.
You can use these SQLSTMT counters to analyze a running process:
•
CALLS stores the number of times the SQL statement was executed.
•
ELAPSED-BUSY-TIME stores the wall-clock elapsed time spent on the particular
statement. To compute the average elapsed time per call, divide the elapsed busy
time by the number of calls.
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 reads performed for a particular
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 1, because an invalid statement is usually
recompiled only once in a session. If this number is 1 or greater, you should
consider explicit SQL compiling the program.
The time spent in recompiling this statement is stored in the ELAPSED-
RECOMPILE-TIME value. This value includes the actual compilation time, plus
disk read, message, and NEWPROCESS time, involved in initiating the SQL
compiler.
•
RECORDS-ACCESSED stores the number of records accessed for the statement.
If the statement accesses many records but uses only a few, you could create an