SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Enhancing SQL/MX Database Performance
HP NonStop SQL/MX Installation and Management Guide—544536-007
15-9
How DP2 Manages and Reuses Query Plan
Fragments
generally require more SQL/MX buffer space than those running DSS transactions with
relatively few opens.
Both SQL/MX buffer space and DP2 data cache can be taken by the memory manager
when they are not in use. When there is no approaching memory pressure, DP2 issues
long-term locks on both DP2 data cache and SQL/MX buffer pages, thereby preventing
those pages from being taken. When memory pressure occurs (for example, when
there is insufficient physical memory to meet the demand for pages), DP2 releases the
locks so that pages can again be stolen by the memory manager.
Use the SCF ALTER DISK command to set or change the size of the SQL/MX buffer
on a disk volume. Although you can configure the buffer size to a maximum of 768 MB,
you should avoid sizing it above 512 MB to minimize the adverse impact on DP2 data
cache function. For information about configuring the SQL/MX buffer, see Using SCF
ALTER DISK to Resize the SQLMXBUFFER Attribute on page 15-12.
How DP2 Manages and Reuses Query Plan Fragments
A query plan is created whenever an SQL statement is compiled. Depending on the
statement, the query plan can execute against a single nonpartitioned table, several
partitions of a table, or several tables in a multi-table join. The part of the query plan
executed by the DP2 process is called the query plan fragment. For partitioned or
multi-table queries, query plan fragments are sent to the DP2 for each of the disk
volumes on which the accessed table data resides.
DP2 continuously manages the placement of new plan fragments in the SQL/MX buffer
and, as required, the removal of inactive plans to make room for them. After a query
plan stored in the buffer completes, it becomes inactive and eligible for removal.
As long as a query plan remains in the SQL/MX buffer, it can be reused whenever an
application repeats the query, eliminating the need to reload the query plan from the
executor or ESP. After a plan fragment has been removed from the buffer, any reuse
attempt fails, and the plan must be reloaded. Reuse failure requires additional
message traffic between applications and DP2. This reload message traffic is
transparent to the application.
Likely candidates for reuse are sets of small static or dynamic queries that are
executed repeatedly.
Causes and Symptoms of Query Plan Fragment Reuse Failures
Query plan reuse failures occur when DP2 is forced to quickly drop inactive plan
fragments, even those that are repeatedly used. The most likely causes for these
reuse failures are:
•
The SQL/MX buffer is too small to store all the plan fragments repeatedly being
used to support application queries.