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

Metadata Tables
HP NonStop SQL/MX Reference Manual—544517-008
10-61
Query Optimization and Performance
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.
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.
MSCF_ET_REMOTE_MSG_
TRANSFER
The value of this default is used to factor in the cost
of transferring messages to and from a remote node.
It reflects the bandwidth of the physical
communication link. You should set it to a value
greater than the cost factor of transferring messages
to or from a local node, which is 0.000046.
Allowable values: 1.175494351e-38 through
3.402823466e+038.
The default value is 0.00005.
MULTIUNION Set to ON, OFF, RESET, or SYSTEM. The default is
ON. When set to ON, NonStop SQL/MX generates a
MultiUnion node. When set to OFF, NonStop
SQL/MX does not generate a MultiUnion node.
When set to RESET or SYSTEM, the default value
is reset.
For information on the MultiUnion operator, see the
SQL/MX Query Guide.
Attribute Setting