SQL/MX 3.x Installation and Management Guide (H06.22+, J06.11+)

Enhancing SQL/MX Database Performance
HP NonStop SQL/MX Installation and Management Guide—640325-001
15-10
Reduction of Plan Fragment Size for Unique Queries
•
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-10.
Reduction of Plan Fragment Size for Unique Queries
SQL/MX Release 3.0 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 - T9082G02 - (04MAY04) (07APR04) - 05/12/2005 22:26:46 System \DRIS1 (C)
1986 Tandem (C) 2004 Hewlett Packard Development Company, L.P.
(Invoking \DRIS1.$SYSTEM.STARTUP.SCFCSTM)
1-> stats disk $D*,sqlmx
STORAGE - Stats DISK \DRIS1.$D01001
SQL/MX Statistics:
Session Data bytes....... 131072 KB Max Data bytes... 131072 KB
Total Sessions........... 1708 Active Sessions.. 0
- 4KB Blocks - - - - - - - - - - - - Reuse - - - - - - - - - -
Max...................... 195840 Attempts......... 60155
Number................... 195840 OK............... 58214