SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

• “Using SCF ALTER DISK to Resize the SQLMXBUFFER Attribute” (page 302)
• “Strategies for Reducing Reuse Failures” (page 302)
For every disk volume running NonStop SQL/MX on your system, approximately 1.1 GB of virtual
space must be shared between the DP2 data cache, SQL/MX buffer, and lock tables. The DP2
data cache provides temporary storage for disk blocks containing table and index data that are
referenced frequently. The SQL/MX buffer stores the query plan fragments that get downloaded
into DP2 and working data in transit between the Executor or an ESP and the local DP2 process
or another DP2 process.
An important challenge in managing this limited virtual space is achieving and maintaining the
proper balance between the SQL/MX buffer and DP2 data cache. If you allow either one to use
too much of this space, it can adversely affect the performance of the other and the system in
general.
Any increase in the size of SQL/MX buffer space reduces the maximum size of the DP2 data cache
by a corresponding amount. The default size of the SQL/MX buffer is 128 MB, and the maximum
size of the DP2 data cache with this default is about 768 MB. If, for example, you increase the
SQL/MX buffer space from 128 MB to 512 MB, the maximum size of the DP2 data cache is
reduced from about 768 MB to a little less than 400 MB.
Whether or not the SQL/MX buffer on a given disk is likely to need larger or smaller amounts of
buffer space depends on the SQL/MX environment of that disk. DP2 processes running a high
volume of OLTP transactions with a large number of opens 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 more
information about configuring the SQL/MX buffer, see “Using SCF ALTER DISK to Resize the
SQLMXBUFFER Attribute” (page 302).
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.
298 Enhancing SQL/MX Database Performance