SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

Metadata Tables
HP NonStop SQL/MX Reference Manual—544517-008
10-59
Query Optimization and Performance
GENERATE_EXPLAIN Enables generation of EXPLAIN information at
compile time.
For MXCI, the default is automatically turned on by a
CONTROL QUERY DEFAULT
GENERATE_EXPLAIN 'ON' command issued by
MXCI at startup time. For performance testing in
MXCI, you might want to turn off
GENERATE_EXPLAIN.
You must explicitly turn off GENERATE_EXPLAIN if
you do not want to include explain generation time
while preparing statements from MXCI or while
analyzing performance testing in MXCI.
You must explicitly turn on GENERATE_EXPLAIN
for NonStop MXCS and other embedded dynamic
queries if you want to look at access plan or
EXPLAIN information.
The default setting is ON for embedded static
queries and OFF for dynamic queries (from
embedded programs or MXCS).
GEN_EIDR_BUFFER_SIZE Combined with GEN_PA_BUFFER_SIZE,
determines the buffer size for partition access
operations. The two default settings must be equal.
Each partition has one partition access operator and,
by default, each partition access operator has 7
buffers associated with it. For OLTP applications,
reducing buffer size to 4 KB can improve
performance by reducing memory usage. For DSS
applications, use the default.
For additional information about this setting, see the
SQL/MX Query Guide.
The default buffer size is 31 KB.
GEN_PA_BUFFER_SIZE Combined with GEN_EIDR_BUFFER_SIZE,
determines the buffer size for partition access
operations. The two default settings must be equal.
For OLTP applications, reducing buffer size to 4 KB
can improve performance by reducing memory
usage. For DSS applications, use the default.
For additional information about this setting, see the
SQL/MX Query Guide.
The default buffer size is 31 KB.
INDEX_ELIMINATION_LEVEL Set to MINIMUM, MEDIUM, or MAXIMUM to
indicate the degree of heuristic elimination of
indexes consideration by the optimizer. Elimination
of less promising indexes results in improvement in
compile time. MINIMUM value implies no
elimination, and MAXIMUM implies maximum
elimination.
The default value is MAXIMUM.
Attribute Setting