SQL/MX 2.x Reference Manual (H06.04+)

Metadata Tables
HP NonStop SQL/MX Reference Manual—540440-003
10-55
Query Optimization and Performance
DP2_CACHE_4096_BLOCKS Specifies the number of 4 KB blocks allocated for
the disk cache. This value is used by the compiler to
determine the cost of a table/index scan operator.
You should set the value of this attribute to the
average value of 4 KB block disk cache settings for
all volumes in the system. The current value for the
number of 4 KB blocks allocated to a disk cache can
be determined by using SCF. See the SCF
Reference Manual for G-Series RVUs for details on
that product.
Allowable values: 1 through the value shown in SCF.
The default is 1024.
FFDC_DIALOUTS_FOR_MXCMP Set to ON or OFF. Controls whether FFDC dial-outs
should occur when the compiler terminates
abnormally or detects an internal error.
The default is OFF, disallowing dial-outs.
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.
Attribute Setting