SQL/MP Installation and Management Guide

Measuring Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
13-8
SQL/MP Measurement Models
•
In the case of a C, Pascal, or TAL program, no separate preprocessing occurs, and
no preprocessor listing is generated. The source listing produced by the compiler,
however, includes comments that show the value of SLT-INDEX. By using this
value, you can find the corresponding information in the SQLSTMT report in much
the same way as you can with a COBOL program.
FILE Statistics
You can monitor database files with the FILE entity. The FILE report provides
information on specific file and record use by a user process. Counters accumulate
information for these events: busy time, reads, writes, updates, deletes, records used,
message activity, lock waits, timeouts, and escalations of locks. The FILE report can
provide you with specific data on SQL tables. You can use the FILE report along with
other reports on a specific volume or subvolume.
SQL/MP Measurement Models
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.
This subsection describes three types of statistics:
•
Startup cost of an application program
•
Execution cost of a running process
•
Database access costs for SQL tables and indexes
Startup Cost
You can use these counters to analyze the startup cost of an application program.
These statistics are gathered by the SQLPROC entity.
•
SQL-OBJ-RECOMPILE-TIME contains the elapsed time spent on recompiling an
invalid program. The recompile time should be zero when a valid program
executes and is not recompiled. (An SQL program is automatically recompiled at
run time if the SQL compiler option RECOMPILEALL is specified at explicit compile
time and the program has been subsequently invalidated.)
•
SQL-NEWPROCESS contains the number of times the SQL compiler or SQL
catalog manager was started. The call to NEWPROCESS should be zero when a
valid program executes if the program is not automatically recompiled and does
not contain any DDL statements.
For SQLCI or for programs being automatically recompiled, the count should be 2
or fewer. A number higher than 2 indicates that multiple catalog managers might