SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Measuring Performance
HP NonStop SQL/MX Installation and Management Guide—523723-004
15-8
SQL/MX Measurement Models
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. (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 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 16,
Enhancing SQLMX Database Performance), 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.