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

Enhancing SQL/MX Database Performance
HP NonStop SQL/MX Installation and Management Guide—544536-007
15-15
Maximizing Disk Process Prefetch Capabilities
DP2 data cache size is controlled through the Subsystem Control Facility (SCF). For
more information on using SCF to set cache size, see the SCF Reference Manual for
the Storage Subsystem.
Maximizing Disk Process Prefetch Capabilities
NonStop SQL/MX can enhance performance by reading blocks of data into the DP2
data cache asynchronously before they are needed. This disk (DP2) 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/MX 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 DP2 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 DP2 data cache, in parallel,
while the DP2 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 DP2 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.
Use the Tandem Reload Analyzer to determine when FUP RELOAD needs to be
run.
•
Multiple PINs. DP2 automatically increases the number of PINS to six when an
SQL/MX query is started and read ahead (prefetch) is used.
Optimizing SQL/MX Memory Management
Certain embedded SQL/MX programs can use very large portions of the process flat
segment address range for:
•
Application-addressable memory
•
Memory space for the master executor to execute SQL/MX statements
The memory space requirement for the master executor can be minimized by using
parallelism to distribute the work to other processes, as explained in the discussion of
parallelism in the SQL/MX Query Guide.