SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

The DISKFILE entity gathers activity statistics against all partitions of a file object. Use DISKFILE to
determine if data is being evenly distributed across all the partitions in a database.
You must use DISCOPEN to determine how data is distributed among all partitions of a FILE. The
DISCOPEN entity gathers statistics for partition opens by process. Use 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 “Enhancing SQL/MX Database Performance” (page 290)), you
can directly compare the statement values with 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 time the process spent executing the SQL statement. You can
calculate the average elapsed time for each call by dividing the ELAPSED-BUSY-TIME counter
value by the CALLS counter value.
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.
Measure Performance Measurement Tool 287