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

Enhancing SQLMX Database Performance
HP NonStop SQL/MX Installation and Management Guide—523723-004
16-12
Performing FUP RELOADs to Generate More
Accurate Query Plans
The optimizer requests sequential prefetch for all scan operations expected to read
sequentially for more than a few blocks.
When sequential prefetch is used, the disk process attempts to read a group of several
consecutive blocks with a single I/O operation. The successive read operations do not
have to wait for physical I/O and can be satisfied from cache, in parallel, while the disk
process performs other I/O operations. To determine if your query uses sequential
prefetch, look for the words sequential cache in the EXPLAIN output for the query.
You can perform a prefetch operation for forward processing; for certain types of
operations such as scans, updates, and deletes of subsets; and for disk operations
using virtual sequential block buffering (described in the SQL/MX Query Guide).
To maximize disk process prefetch operations, use:
•
Large cache
•
Mirrored disks
•
Well-organized key-sequenced tables (physical sequence closely maps to logical
sequence). The FUP RELOAD operation can help reorganize an existing table.
•
Multiple PINs. DP2 automatically increases the number of PINS to six when an
SQL/MX query is started and read ahead (prefetch) is used.
To check whether the disk process uses prefetch capabilities for your queries, set
statistics on, and then use the SCF STATS disk command and the Measure DISK
and DISKOPEN entities.
Performing FUP RELOADs to Generate More
Accurate Query Plans
As tables and indexes are subjected to repeated INSERT, UPDATE, and DELETE
operations, they become more fragmented and less efficiently organized. Over a
period of weeks, it can become impossible for MXCMP to accurately estimate the costs
for different query plans for those tables. In effect, the B-trees for the tables become
too deep and asymmetrical.
Performing a FUP RELOAD on a table or index reduces data fragmentation and other
structural inefficiencies and considerably decreases the duration of subsequent table
and index queries. Also, FUP RELOAD is the only method available for inserting slack
space into SQL/MX files.
To improve the SQL compiler’s ability to generate accurate cost estimates and improve
table performance:
•
Perform a FUP RELOAD on each table and index at regular intervals (for example,
once a month), depending on the number of updates performed on them and other
site-specific considerations.