Neoview Control Query Default (CQD) Reference Guide (R2.5)

14 Compiler Cache
This section describes this CQD: “QUERY_CACHE” (page 57).
QUERY_CACHE
Transaction control and lockingCategory
Attempts to reduce compilation times by storing and reusing previously compiled query
plans. It maximizes the chances of plan reuse by parameterizing literals in equality
predicates. Two equality predicates, "col = val1" and "col = val2", are considered to match
if their selectivities match.
A query cache setting of '16384' means a maximum of 16,384 KB of compiler memory can
be used for keeping previously compiled plans before evicting the oldest unused plan(s)
to make room for the latest cacheable plan.
Description
Kilobytes of memory allocated to query cache.Up through 4294967295
Turns off query plan caching.‘0’
The default value is ‘16384’ (16 MB).
Values
To choose the appropriate size for the query cache, examine your applications. Applications
that use a PREPARE statement to pre-compile queries once and then EXECUTE the prepared
plan, should turn off plan caching.
Ad hoc query applications can specify a size that can hold most of the frequently processed
queries. For example, if an application processes 40 classes of queries frequently with an
average plan size of 100 KB per query, a cache size of 4000 KB might be optimal. (Plan size
is not the same as the size of the SQL statement and is not easy to assess.)
There may be applications that are operational in nature, with many small queries, and
others that are analytical in nature with large complex queries. Cache size can be set
differently for different service levels handling such workloads based on the classes and
types of queries, size of the queries, and propensity to get cache hits.
Another consideration is how frequently the cache is getting flushed due to the compiler
being shutdown and a new one started by an MXOSRVR (ODBC/Connect server), in order
to run queries on behalf of a different role than the role that was using the compiler before.
If this happens often and not enough static servers can be started to reduce this from
happening, then creating a large cache may not be useful, because it has to be flushed and
filled too often.
After taking the above into account the best way to really assess whether caching is effective,
and tune it for your specific applications, is to understand the cache hit statistics, how many
queries are forced to be removed from cache (on a least recently used basis), and a number
of other statistics about the efficiency of query plan caching for your applications. Contact
your HP representative for information about how caching works.
Usage
Not applicableProduction usage
A larger cache size allows more query plans to be cached. This increases the probability of
finding a plan in cache that can be reused for a query, thereby reducing compile time. It
does mean that the compiler uses more memory, but because there are usually not that
many compilers running in a CPU, the negative effects may be minimal.
However, you do need to know the amount of physical memory available on each node
and the number of compilers that run on a node (influenced by the number of concurrent
connections configured to run on the cluster). If the cache size is disproportionately large,
it is likely to result in reduced performance as the operating system may repeatedly swap
the compiler (bloated by a huge cache) in and out of physical memory.
Impact
ServiceLevel
QUERY_CACHE 57