SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

12 Measuring Performance
During the life of an SQL application, you might need to measure the performance of all, or part,
of the application. Several NonStop software products can provide statistical information about
performance.
Collecting these statistics requires an in-depth understanding of the system, the layout of the database
tables, and the use of the application programs. You usually gather statistics when you are:
• Running a performance benchmark.
You obtain statistics for a benchmark during ideal conditions when all volumes and nodes are
available and functioning at peak performance. You obtain these statistics on a finite set of
data loaded for the best possible performance.
• Diagnosing a performance problem.
You obtain statistics to determine the cause of a problem. You might have to obtain several
samples during different periods of time to compare the results.
• Taking a general sampling.
To monitor performance as the SQL database grows and changes, periodically obtain a
sample of statistics and compare the results against previous samples.
• Making an equipment change or move requiring database relocation.
Whenever the database is moved or changed, you should obtain a performance sampling.
The move or change can affect performance.
This section addresses these topics:
• “SQL/MX Tools for Gathering Statistics” (page 284)
• “Measure Performance Measurement Tool” (page 287)
SQL/MX Tools for Gathering Statistics
Both MXCI and the SQL programmatic interface have tools for gathering statistics. The MXCI
commands that display statistics are:
• SHOWLABEL command
• SET STATISTICS ON command
• DISPLAY STATISTICS command
NOTE: Measure typically collects statistics for all SQL statements executed during a given Measure
collection interval. MXCI collects performance information for every dynamic SQL statement, but
reports only for the latest executed statement.
SHOWLABEL Command
The MXCI SHOWLABEL command displays file-label information for SQL/MX objects. (SHOWLABEL
does not support SQL/MP objects or SQL/MP aliases.) For performance statistics, use SHOWLABEL
to determine index levels and extent information.
This example returns detailed information about the EMPLOYEE table. Use the DETAIL option to
display information about partitions and indexes:
SHOWLABEL SAMDBCAT.PERSNL.EMPLOYEE, DETAIL;
===============================================================
GuardianName: \DMR15.$SYSTEM.ZSDG5WVN.FVRZ1T00
AnsiName: SAMDBCAT.PERSNL.EMPLOYEE
AnsiNameSpace: TA
AnsiNameObjectType: BT
284 Measuring Performance