SQL/MP Installation and Management Guide

Measuring Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
13-4
SQL Statistics Area (SQLSA)
Estimated Cost 9
Start Time 89/04/01 13:07:12.822479
End Time 89/04/01 13:07:18.865150
Elapsed Time 00:00:06.042671
SQL Execution Time 00:00:00.392796
Records Records Disk Message Message Lock
Table Name Accessed Used Reads Count Bytes WE
\a.$b.c.d 123 22 3 10 3245
\w.$x.y.z 9987231 1 99999 1 100 e
\sanfran.$mamoth.longestt.filename
1 1 0 1 100 w
With these statistics, you can quickly monitor the performance of a specific statement
on specific objects. The information provided can help you to:
•
Determine the comparative performance of similar objects. For instance, you can
determine the effect of a new index on a table compared to the performance
without the index, or you can determine the performance after an UPDATE
STATISTICS statement.
•
Display the statistics of various queries or DML statements.
•
Monitor the estimated cost of a compiled statement or an ad hoc query. The larger
the estimated cost, the greater the execution time. You can then investigate costly
SQL statements for additional indexes, for out-of-date statistics on referenced
tables, or for poorly designed queries.
SQL Statistics Area (SQLSA)
The SQL statistics area (SQLSA) is a data area programmers can use to receive
statistics after SQL statement execution. To use this area, programmers must include
the INCLUDE SQLSA statement in the host language program. When the SQLSA is
present, the program passes the data area to the SQL executor; then the executor
accumulates and returns statistics.
The DML statements for which statistics are returned are: OPEN CURSOR, FETCH,
SELECT, INSERT, UPDATE, and DELETE. Statistics are also returned for prepared
DML statements executed with either the EXECUTE or EXECUTE IMMEDIATE
statement.
Statistics are kept on a table-by-table basis for a maximum of 16 tables. These
statistics include the number of tables accessed, records accessed, records used,
number of disk reads, number of wait times for locks, and so forth.
SQLSA statistics also return the total processor time used by all ESPs and sort
processes (SORTPROGs). These statistics are useful for queries that use parallel
execution plans. They are not kept for each individual table or for each individual ESP
or SORTPROG, but rather for all tables and ESP and SORTPROG processes involved
in the query.