SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

CACHE FAULTS to CACHE CALLS is even one or two percent, consider reducing the cache size,
adding more physical memory to the CPU, or processing to other CPUs.
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.
SQL/MX processes can consume large amounts of addressable memory space as a result of:
• Parallelism among a large number of ESPs
• The execution of plans that use sort and grouping operators
• The optimization of complex plans
The heavy consumption of addressable memory space by SQL/MX processes can lead to insufficient
swap file space. As a result, you should provide more kernel-managed swap space on each CPU
by increasing the size of existing swap files or adding new swap files.
You should periodically monitor your kernel-managed swap files while SQL/MX programs are
being compiled and executed to ensure that adequate swap file space is available. If the required
304 Enhancing SQL/MX Database Performance