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

Query Plan Caching
HP NonStop SQL/MX Query Guide—523728-003
6-6
Choosing an Appropriate Size for the Query Cache
•
Data Definition Language (DDL) statements are not cacheable.
•
Data Control Language (DCL) statements are not cacheable.
Choosing an Appropriate Size for the Query
Cache
To adequately choose an appropriate size for the query cache, examine your
applications.
Static applications that precompile their queries once during application development
and rarely recompile their queries during application deployment and operation should
turn off plan caching by specifying a QUERY_CACHE default setting of 0.
Dynamic applications, such as a book search engine that processes many queries
whose text is not known beforehand, can specify a QUERY_CACHE size that can hold
most of the frequently processed queries. For example, if an application processes 40
classes of queries and the average plan size of a query is 100 KB, a QUERY_CACHE
size of 4000 KB might be optimal. The steps for finding the size of an entry are
explained under QUERYCACHE Function on page 6-9.
An application can change the QUERY_CACHE size during operation with the
CONTROL QUERY DEFAULT command. For example, a mixed mode application that
does both transaction processing (TP) and decision support system (DSS) queries can
increase the QUERY_CACHE size just before it switches to TP mode to hold and
cache more TP queries. Likewise, the application might reduce the QUERY_CACHE
size just before it switches to DSS mode.
Dynamic applications that spend a significant amount of time compiling and executing
queries can hold and cache more queries for any given QUERY_CACHE size by
turning off the GENERATE_EXPLAIN default setting. Turning off
GENERATE_EXPLAIN reduces the average plan size by about 15 percent for TP-style
queries. As a result, a query plan cache can hold about 15 percent more queries.
Query Plan Caching Statistics
SQL/MX provides a convenient way to determine important information about the
caching process in addition to the current state of stored plans. This information is
provided in two virtual tables. You query these tables at the MXCI prompt by using the
SELECT statement as if they were physical tables.
If no query plans have been cached, no rows are returned.
For syntax information, see the QUERYCACHE and QUERYCACHEENTRIES
functions in the SQL/MX Reference Manual.