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

HP NonStop SQL/MX Query Guide—523728-003
6-1
6 Query Plan Caching
Use the information in this section to understand query plan caching:
•
Types of Cacheable Queries on page 6-2
•
Choosing an Appropriate Size for the Query Cache on page 6-6
•
Query Plan Caching Statistics on page 6-6
•
SYSTEM_DEFAULTS Table Settings for Query Plan Caching Attributes on
page 6-7
Overview
Query Plan Caching is a feature of the SQL/MX compiler that provides the ability to
cache the plans of certain queries. This feature improves performance when the plan
can be produced from the cache rather than through a full compilation. The
performance improvement is typically 60 to 80 percent (compile time) for simple
TP-style queries.
Certain default settings used with the CONTROL QUERY DEFAULT statement apply to
query plan caching. For more information, see SYSTEM_DEFAULTS Table Settings
for Query Plan Caching Attributes on page 6-7.
The query plan caching feature has been designed to operate transparently. No
SQL/MX application source code changes are required. When given a query, SQL/MX
produces the same plan with or without query caching. This correctness requirement
implies that the SQL/MX compiler honors CONTROL QUERY DEFAULT and
CONTROL TABLE statements even when query caching is active. To illustrate this
behavior, suppose that:
•
The table timeout setting for table T is set to infinite (-1).
•
The SQL/MX compiler is asked to compile “SELECT * FROM T”
•
The SQL/MX compiler compiles and caches a plan for “SELECT * FROM T”
•
A CONTROL TABLE statement changes the timeout setting for table T to five
seconds
•
The SQL/MX compiler is asked to compile “SELECT * FROM T” again
The SQL/MX compiler cannot use the previously cached plan for “SELECT * FROM T”
because the plan has an infinite timeout setting for Table T. If the compiler were to use
the cached plan, the compiler would effectively ignore the CONTROL TABLE
statement that changed the timeout for Table T to five seconds. Therefore, the compiler
can compile and cache one or more plans for one query with each plan associated with
a different set of control settings.
A query that is compiled repeatedly, each time with a new set of control settings, does
not result in a cache hit. A query that is compiled subsequently, with a set of control