SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

Metadata Tables
HP NonStop SQL/MX Reference Manual—523725-004
10-55
Query Optimization and Performance
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.
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.
JOIN_ORDER_BY_USER Enables (ON) or disables (OFF) the join order
specified you specify in the FROM clause of a query.
When set to ON, the optimizer considers only
execution plans that have the join order you specify.
For more information about this setting, see the
SQL/MX Query Guide.
The default is OFF.
MAX_ESPS_PER_CPU_PER_OP Set to the maximum number of ESPs the optimizer
considers starting for each CPU for a given operator.
For more information about this setting, see the
SQL/MX Query Guide.
The default is 1, which limits the optimizer to plans
with only one ESP per CPU for a given operator.
Allowable values: 1, >1.
MDAM_SCAN_METHOD Enables (ON) or disables (OFF) the
MultiDimensional Access Method (MDAM). In certain
situations, the optimizer might choose MDAM
inappropriately, causing poor performance.
SQL/MP Considerations: SQL/MP users know this
attribute as CONTROL TABLE MDAM ENABLE.
The default is ON.
MIN_MAX_OPTIMIZATION Set to ON or OFF. This performance optimization
enables the compiler to read only the result row or a
select number of rows to answer minimum (MIN) or
maximum (MAX) aggregate expressions. The
compiler can perform this type of optimization only
when the rows are naturally ordered on the MIN-
MAX column. If OFF, this type of optimization is
disabled.
The default is ON.
Attribute Setting