SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

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.
• Planned or unplanned takeovers by the backup DP2 CPU have generated reuse failures. A
planned takeover, such as from using SCF PRIMARY or by automatic switch due to a path
error, checkpoints only SQL/MX session data that is in use at the time of the takeover. An
unplanned takeover, such as from primary CPU failure or softdown failure, ensures that there
will be no SQL/MX session data in the new primary CPU.
• A combination of some or all of these.
The symptoms of high reuse failure rates on one or more disks running NonStop SQL/MX are those
of slower system performance. They include:
• Slower response times
• High CPU usage numbers
• DP2 processes running hot
• Hardware and software not scaling as expected (For example, you increase client processes
by only 5 percent and consume 30 percent more system resources.)
When these symptoms occur, use the SCF STATS DISK command with the SQL/MX option on disks
running NonStop SQL/MX to identify which SQL/MX buffers, if any, are displaying high reuse
failure rates. On disks where high reuse failure rates are found, for the best results, increase the
size of the SQL/MX buffer space. For more information, see the “Using SCF STATS DISK to Monitor
SQL/MX Statistics and Reuse Failures” (page 296).
Reduction of Plan Fragment Size for Unique Queries
SQL/MX Release 3.x achieves significant reductions in the query plan size of unique queries,
which involve single-row INSERT, SELECT, or DELETE operations. OLTP environments running these
single-row queries obtain the most significant improvements in plan fragment size reduction. These
enhancements also reduce the incidence of reuse failures and pressures to increase SQL/MX buffer
size.
Using SCF STATS DISK to Monitor SQL/MX Statistics and Reuse Failures
Use the SCF STATS DISK command to monitor statistics for the SQL/MX buffer, including the
incidence of reuse failures. For example:
SCF - T9082H01 - (04DEC06) (15NOV06) - 12/06/2010 17:52:01 System \DMR11
(C) 1986 Tandem (C) 2006 Hewlett Packard Development Company, L.P.
(Invoking \DMR11.$SYSTEM.SYSTEM.SCFCSTM)
1-> stats disk $*,sqlmx
296 Enhancing SQL/MX Database Performance