SQL/MX Release 2.0 Best Practices

Possible Implementations 35
To see if cache is operating efficiently, use the STAT option of the PUP LISTCACHE command. If CACHE
READ HITS are less than 90 percent, consider increasing the cache size. If the ratio of CACHE FAULTS to
CACHE CALLS is greater than one percent, consider reducing the cache size, adding more physical
memory to the CPU, or processing to other CPUs.
Cache size is controlled through the Subsystem Control Facility (SCF). For more information about using
SCF to set cache size, see the SCF documentation.
Maximizing Disk Process Prefetch Capabilities
NonStop SQL/MX can enhance performance by reading blocks of data into cache asynchronously
before data is 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/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 disk process attempts to use a single I/O operation to read a group
of several consecutive blocks. 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 Reference Manual).
To maximize disk process prefetch operations, use:
• Large cache.
• Mirrored disks.
• Well-organized, key-sequenced tables (whose physical sequence closely maps to their 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 prefetch is used.
To check whether the disk process uses prefetch capabilities for your queries, set statistics on, and use the
SCF STATS disk command and the Measure DISK and DISKOPEN entities.
Column Alignment
It is a good practice to align each column on a word boundary whenever possible. Data types vary in
length and may not always be word-aligned. Numeric values are always word-aligned, and vary in size
from 2 bytes to 4 bytes and 8 bytes. Character columns occupy the number of bytes specified in the
declaration. For example, CHAR (3) occupies 3 bytes. If CHAR (3) is placed between two numeric
columns, 1 byte is wasted to align the second numeric column in the program’s memory structure. If an
even number of odd-length columns appears in the row, place them together because they will
terminate on a word-boundary, wasting no space.
However, always select the partition and key-column order based on the access path required for the
table. Column alignment is secondary.
These are other good practices:
• If a numeric column will contain only positive values, use an unsigned numeric.
• Avoid the use of DECIMAL data types. This is an ASCII character representation of a number that will
need to be converted to binary for arithmetic operations.
• Align VARCHAR columns at the end of the row.
• Avoid using VARCHAR for small length columns.