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

Metadata Tables
HP NonStop SQL/MX Reference Manual—540440-003
10-59
Query Plan Caching
For more information about query optimization, see the SQL/MX Query Guide.
Query Plan Caching
These attributes enable NonStop SQL/MX to cache query plans:
SORT_MAX_HEAP_SIZE_MB The default value is used for allocating the heap
memory size for operations involving the sort
operator. The minimum and maximum values are 0
and 1024 respectively.
The default value is 20.
UPD_ORDERED Set to ON or OFF. If ON, the optimizer generates
and considers plans where the rows must be
inserted, updated, or deleted in clustering key order.
If OFF, the optimizer does not generate plans where
the rows must be inserted, updated, or deleted in
clustering key order.
The default is ON.
ZIG_ZAG_TREES Set to ON or OFF. Enables (ON) or disables (OFF)
the optimizer to consider zig-zag trees in addition to
linear trees.
For additional information about this setting, see the
SQL/MX Query Guide.
The default is OFF.
Attribute Setting
QUERY_CACHE Set to a value between 0 to 4194303. Indicates the size
in kilobytes to which the cache is allowed to grow.
The default setting is 1024, which activates a query
cache that can grow to 1024 KB in the current session.
To deactivate the query cache in the current session,
set QUERY_CACHE to 0. If a query cache was
allocated, this setting frees it.
For additional information about this setting, see the
SQL/MX Query Guide.
QUERY_CACHE_MAX_VICTIMS Set to a value between 0 and 4194303. Indicates the
maximum number of cache entries that can be
displaced to accommodate a new entry and stay within
the size limit of the cache. Setting this attribute to a very
large value means that all the cache entries could be
displaced to accommodate one very large query.
Setting this attribute to 0 means that, when the cache
becomes full, no cache entries (pinned or unpinned)
can be displaced, and no new entries can be entered
into the cache.
For additional information about this setting, see the
SQL/MX Query Guide.
The default setting is 10 cache entries.
Attribute Setting