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-8
SQL/MX Measurement Models
DISCOPEN to determine if the partitions in a database are being accessed evenly by
the processes in an application.
SQL/MX Measurement Models
Measure always updates counters, so starting a measurement adds only the overhead
of writing the counters out to disk files. Still, when using the Measure product, you must
determine whether the overhead for gathering Measure statistics is worth the
information provided by the reports. You might find certain statistical information more
meaningful with a few samplings. You should, of course, use the Measure product for
gathering detailed statistical information for problem analysis.
Three types of statistics are discussed next:
•
Startup cost of an application program
•
Execution cost of a running process
•
Database access costs for SQL tables and indexes
Startup Cost
Use the following counters to analyze the startup cost of an application program.
These statistics are gathered by the SQLPROC entity.
•
SQL-STATEMENT-RECOMPILES contains the number of statement recompiles
done on this process.
•
SQL-STATEMENT-RECOMPILES-TIME contains the elapsed time spent on
recompiling SQL statements. The recompile time should be zero when a valid
program executes and is not recompiled.
•
SQL-NEWPROCESS contains the number of times an ESP process was started.
•
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 OPEN calls performed by the SQL executor on
behalf of this process.
•
OPEN-TIME contains the time this process spent executing opens. After an SQL
program is started, the files are open and remain open for the duration of the
session.
Execution Costs
Use SQLSTMT counters to analyze the execution costs of a running SQL/MX process.
These counters provide information on a statement basis. For counters that have the
same names as counters for database access costs (described in Section 15,
Enhancing SQL/MX Database Performance), you can directly compare the statement
values with table values returned by those counters.