SQL/MX 3.1 Installation and Management Guide (H06.23+, J06.12+)

Enhancing SQL/MX Database Performance
HP NonStop SQL/MX Release 3.1 Installation and Management Guide—663852-001
15-9
How DP2 Manages and Reuses Query Plan
Fragments
cache function. For information about configuring the SQL/MX buffer, see Using SCF
ALTER DISK to Resize the SQLMXBUFFER Attribute on page 15-13.
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.

Too many client processes are connected to and communicating with the DP2
process, sending more queries than DP2 or its buffers can accommodate and
retain for reuse.

Too many tables are partitioned on the affected disk, resulting in too many plan
fragments. Usually, one new plan fragment is created and stored in the SQL/MX
buffer with each table access. Sometimes, however, NonStop SQL/MX can
optimize and use a single plan fragment to access multiple tables, as when a join
is pushed down to DP2.

Too many complex queries involving rowsets and compound statements are
executing and contributing to a large plan fragment footprint. Ad hoc queries, data
loading, and database maintenance can have a negative impact on concurrent
OLTP performance by causing additional reuse failures.