SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

Metadata Tables
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
10-69
Query Optimization and Performance
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 additional 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 additional 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.
MEMORY_USAGE_SAFETY_NET Specifies the SQL/MX compiler memory threshold
used for generating optimal query plan in megabytes
(MB). For complex queries, when the optimizer
memory reaches this threshold, plans are pruned to
reduce the memory growth. Setting this attribute too
low can result in sub-optimal plans. The actual
memory available to a process on the NonStop
operating system is limited to approximately 1.4 GB.
Therefore, setting this value greater than 1.4 GB
does not imply that the process will have memory
larger than the system limit. Memory size is confined
to the limits of the underlying operating system.
When the optimizer reaches the set threshold, a
warning 6020 is displayed and the query compiles
successfully. The generated plan might not be
optimal and execution might be slow.
This attribute can have a value in the range 800
through 4096. The default value is OFF, which
implies optimizer can use the maximum available
memory.
Attribute Setting