SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-10
Testing UPDATE STATISTICS
1. Prepare a sample query from your application. (You should probably use a query
used often in your application.)
This example shows the displayed statistics:
>> SELECT ...;
>> DISPLAY STATISTICS;
Estimated Cost 68
Start Time 89/03/10 14:26:41.494150
End Time 89/03/10 14:27:11.123179
Elapsed Time 00:00:29.629029
SQL Execution Time 00:00:00.686883
Records Records Disk
Table Name Accessed Used Reads . . .
\SYS1.$VOL.SALES.ORDERS 49 10 3 . . .
\SYS1.$VOL.SALES.ODETAIL 246 30 246 . . .
(This example shows only the leftmost fields that actually appear in a statistics
display. For a complete display, see the SQL/MP Query Guide.)
2. Determine the effect of the UPDATE STATISTICS statement by issuing the
statement within a user-defined TMF transaction. You can then back out the
operation if necessary. In an SQLCI session, do these:
a. Issue a BEGIN WORK statement; then issue UPDATE STATISTICS with the
NO RECOMPILE option.
b. Use EXPLAIN to see if the new statistics give you the better query execution
plan. If the estimated cost is significantly less than the original, the UPDATE
STATISTICS statement could improve performance. If the cost is not less, this
table probably does not need its statistics updated.
c. Depending on the EXPLAIN output, you can decide whether to commit the
transaction (COMMIT WORK) or back out the transaction (ROLLBACK
WORK).
This example shows the statement and command sequence that rolls back the
TMF transaction so that statistics are not updated:
>> BEGIN WORK; <--Begins the TMF transaction
>> UPDATE ALL STATISTICS FOR TABLE $VOL.SALES.ORDERS;
>> SELECT ...;