SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-18
Maximizing Disk Process Prefetch Capabilities
Maximizing Disk Process Prefetch Capabilities
SQL can enhance performance by reading blocks of data into cache asynchronously
before they are needed. This disk process prefetch operation works best when you
request long sequential scans through data or when your access plan has a low
selectivity value (as described in the SQL/MP Query Guide).
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.
A prefetch operation can be done for all table types, 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/MP 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 LOAD operation can help reorganize an existing table
•
Multiple PINs (for more information, see the NUMDISKPROCESSES sysgen
parameter)
To check whether the disk process uses prefetch capabilities for your queries, set
statistics on, use the PUP LISTCACHE command (D-series only) and the SCF INFO
DISK, CACHE command (G-series only) with the STATISTICS option, and use the
Measure DISK and DISKOPEN entities.
Managing File System Double Buffering
SQL can enhance performance by allowing the SQL file system to asynchronously
prefetch blocks of data from the disk process. When the file system receives a block of
data from the disk process, it asynchronously requests the next block without waiting
until it has processed the current block. By the time the file system is ready to process
the next block, that block is likely to be in memory already and can be processed
immediately. This performance enhancement, called file system double buffering, is
especially advantageous when the file system and disk process reside on different
processors.