SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-27
Enhancing Query Performance
You can direct FastSort to use a specific set of volumes for its work. Use the
SCRATCH attribute to specify an initial scratch volume. To include or exclude volumes
from the pool of volumes FastSort uses once the initial scratch volume is full, use the
SCRATCHON and NOSCRATCHON attributes, respectively.
For more information about scratch volumes, see the FastSort Manual.
Enhancing Query Performance
You can enhance the performance sorts within SQL queries in several ways. For more
information, see the SQL Query Guide.
Supporting Parallel Query Execution
When =_SORT_DEFAULTS DEFINEs are used to designate a specific scratch file, the
SQL/MP software starts every sort operation with the same SORT DEFINE settings.
If the same scratch file is used during parallel query execution, the first sort request
gains exclusive access to the scratch file, and all later sort requests receive an error.
To avoid this problem, do not explicitly specify a scratch file by name; instead, specify
only the volume name in your =_SORT_DEFAULTS DEFINE and prompt FastSort to
create a temporary file. Now, parallel sort operations can take place on the same
volume but will access individual scratch files.
The same type of contention problems occur, and also multiply, when you use subsorts
to avoid partitioned scratch files. All subsort requests can contend for the same scratch
file, and only the first request gets the file. In addition, all sets of subsorts use the same
groups of processors. So, if you have eight SQL executor processes, you then have
eight sorts, each with subsorts configured in exactly the same way. You must configure
parallel subsort operations very carefully.
When using the SQLCI LOAD utility to conduct parallel database loads into a
partitioned base table, you can avoid scratch file contention problems if you change the
=_SORT_DEFAULTS DEFINE attributes for each load or if you let SQL create scratch
files for the operations.
For parallel DDL statement execution (requested by specifying CREATE
INDEX...PARALLEL EXECUTION ON), you can identify scratch volumes for the sort
processes to use when sorting index records. You identify these volumes in a
configuration file whose name you specify in the CONFIG option of the CREATE
INDEX statement.