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-10
Reduction of Plan Fragment Size for Unique 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 Using SCF STATS DISK to Monitor SQL/MX Statistics and Reuse
Failures on page 15-11.
Reduction of Plan Fragment Size for Unique Queries
SQL/MX Release 2.3.1 and later releases of NonStop SQL/MX achieve 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.