SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Query Plan Caching
HP NonStop SQL/MX Query Guide—523728-003
6-7
SYSTEM_DEFAULTS Table Settings for Query Plan
Caching Attributes
SYSTEM_DEFAULTS Table Settings for Query
Plan Caching Attributes
This subsection provides additional information about the query plan caching
externalized attributes. The SYSTEM_DEFAULTS Table entry of the SQL/MX
Reference Manual provides reference information about these settings:
•
QUERY_CACHE
System-defined default setting: 1024 kilobytes (KB)
Allowable values: 0 to 4194303
The value of QUERY_CACHE indicates the KB size to which the cache is allowed
to grow. The default setting, 1024, activates a query cache that can grow to 1024
KB in the current session.
Although the maximum value for QUERY_CACHE is 4194303, you should not set
the QUERY_CACHE limit to a value greater than or equal to a fraction of the
physical memory of the host machine. Doing so is likely to result in reduced
performance as the HP NonStop operating system repeatedly swaps the SQL/MX
compiler (bloated by a huge cache) in and out of the host machine's physical
memory. A good strategy might be to avoid setting QUERY_CACHE to more than
10 percent of the host machine’s physical memory.
If a new entry causes the size of the query cache to exceed the value of the
QUERY_CACHE default, current entries are removed on a “least recently used”
basis, taking into account pinned entries and the value of the default
QUERY_CACHE_MAX_VICTIMS. See QUERY_CACHE_STATEMENT_PINNING
on page 6-9.
To deactivate the query cache in the current session, set QUERY_CACHE to 0. If a
query cache is allocated, this setting frees it.
•
QUERY_CACHE_MAX_VICTIMS
System-defined default setting: 10 cache entries
Allowable values: 0 to 4194303
This attribute 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.
When considering displacement of entries in the cache, the compiler looks for the
least recently used unpinned entries of a combined size that is greater than the
size of the new entry. If there are not enough least recently used unpinned entries,
the compiler looks for any least recently used pinned entries to displace. 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. The first n queries occupy the cache (where n is the number of
entries it takes to fill the cache). If the cache is full and a new query comes along,