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_CACHESystem-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_VICTIMSSystem-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,
Query Plan Caching
HP NonStop SQL/MX Query Guide —640323-001 6 -8
SYSTEM_DEFAULTS Table Settings for Query Plan Caching Attributes the new entry is not added to the cache, and no resident entries can be displaced. Because the query plan cache feature is transparent, no error messages are issued.
If QUERY_CACHE_MAX_VICTIMS is later set to a nonzero value, replacement resumes as usual. The number of entries that the cache can hold depends on the size of the cache and the size of the cached plans. The system-defined default setting limits the number of cache entries that can be displaced to 10 cache entries.
•
QUERY_CACHE_REQUIRED_PREFIX_KEYS System-defined default setting: 255Allowable values: 0 to 255
This attribute specifies how many and which columns of a composite primary or partition key are required for an equality predicate to be considered cacheable. If the attribute is set to a value greater than the number of columns in a composite key, all columns of the key are required. The system-defined default setting is 255, which means that only complete primary or partition key equality predicates are cacheable. To avoid compromising query plan quality, it is recommended that you keep the system-defined default setting of 255.
The value 0 means that the presence of any one column of a composite primary or partition key in an equality key predicate is sufficient to make that predicate
cacheable. A value n that is greater than zero but less than the number of columns in the key indicates that the first n columns of the key are required to be present in a key predicate for that predicate to be considered cacheable.
Suppose that the QUERY_CACHE_REQUIRED_PREFIX_KEYS setting is 1, and the table T has a composite primary key consisting of columns (a, b, and c). With the setting of 1, provided that the first column of the key (a) is an equality
predicate, the query is cacheable. If the
QUERY_CACHE_REQUIRED_PREFIX_KEYS setting is 2, the only valid prefixes of (a, b, c) are (a, b) and (a, b, c). That is, these queries are cacheable:
SELECT * FROM T WHERE a=1 AND b = 20; DELETE FROM T WHERE (a,b,c)=(9,909,10);
However, these queries are not cacheable:
SELECT * FROM T WHERE a=77;
DELETE FROM T WHERE (b,c)=(1,23);
This attribute can be used to force certain noncacheable queries into cacheable queries. In the previous example, SELECT * FROM T WHERE A=77 is not
cacheable because its equi-predicate specifies only the first of a three-column key. To make it cacheable, specify CONTROL QUERY DEFAULT
QUERY_CACHE_REQUIRED_PREFIX_KEYS ‘1,’ and the query becomes cacheable.
Query Plan Caching
HP NonStop SQL/MX Query Guide —640323-001 6 -9
QUERYCACHE Function
•
QUERY_CACHE_STATEMENT_PINNING System-defined default setting: OFF Allowable values: ON, OFF, CLEARThis attribute controls whether queries are entered into the cache as pinned or unpinned. You might have important, compile-time critical queries that you want to ensure are in the cache when needed. When a query is pinned in the cache, it usually cannot be displaced from the cache unless the cache becomes full of pinned queries. In this case, the least recently used pinned entries also become displaceable.
The system-defined default setting, OFF, means that all subsequent query cache entries are unpinned.
The value CLEAR means that all subsequent query cache entries are unpinned, and all pinned entries in the cache are also unpinned.
The value ON means that all subsequent query cache entries are pinned.
QUERYCACHE Function
The query plan cache automatically collects statistics regarding its use. When invoked, the QUERYCACHE table-valued stored function collects and returns the current state of these statistics in a single row table. The statistics are reinitialized when an mxcmp
session is started, and each mxcmp session maintains an independent set of statistics. This table describes the various statistics of the QUERYCACHE table:
Column Name Data Type Description
AVG_PLAN_SIZE INT UNSIGNED Total KB size of all cache entries divided by the number of entries. CURRENT_SIZE INT UNSIGNED Current KB size of the query cache. MAX_CACHE_SIZE INT UNSIGNED Maximum cache size in KB.
MAX_NUM_VICTIMS INT UNSIGNED Maximum number of plans that can be removed from the cache to make room for a new entry.
NUM_ENTRIES INT UNSIGNED Total number of query entries in the cache.
NUM_PINNED INT UNSIGNED Total number of pinned entries. NUM_COMPILES INT UNSIGNED Total number of complete compile
requests (excludes DESCRIBE and SHOWSHAPE).
NUM_RECOMPILES INT UNSIGNED Total number of recompilations. Recompilation of a cached plan occurs when a referenced table has been re-created or altered.
Query Plan Caching
HP NonStop SQL/MX Query Guide —640323-001 6- 10
QUERYCACHE Function
NUM_RETRIES INT UNSIGNED Number of successful compiles that initially fail with caching on (caused by a defect in mxcmp) and that succeed with caching off.
NUM_CACHEABLE_PARSING INT UNSIGNED Total number of SQL statements that mxcmp has processed after parsing and before binding the query that satisfy the conditions for caching.
NUM_CACHEABLE_BINDING INT UNSIGNED Total number of SQL statements that mxcmp has processed after binding and before transformation of the query that satisfy the conditions for caching.
NUM_CACHE_HITS_PARSING INT UNSIGNED Total number of SQL statements that mxcmp has processed after parsing and before binding that have produced hits.
NUM_CACHE_HITS_BINDING INT UNSIGNED Total number of SQL statements that mxcmp has processed after binding and before transformation of the query that have produced hits.
NUM_PIN_HITS_PARSING INT UNSIGNED Total number of hits on pinned entries that occurred after parsing and before binding.
NUM_PIN_HITS_BINDING INT UNSIGNED Total number of hits on pinned entries that occurred after binding and before transformation.
NUM_CACHEABLE_TOO_LARGE INT UNSIGNED Number of SQL statements
processed by mxcmp that satisfy the conditions for cacheability but with plans too large to fit in the cache. NUM_DISPLACED INT UNSIGNED Number of entries removed from the cache to make room for new entries or as a consequence of a resizing of the cache or
recompilation.
OPTIMIZATION_LEVEL CHAR(10) Indicates the current desired level of query optimization. Can be 0, 2, 3, or 5.
PINNING_STATE CHAR(4) Current state of pinning. Can be ON or OFF.
Query Plan Caching
HP NonStop SQL/MX Query Guide —640323-001 6- 11