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-12
Using SCF ALTER DISK to Resize the
SQLMXBUFFER Attribute
Failed ID under Reuse identifies the number of plan fragments that are present
in the free space tables but whose IDs reported that they could not be reused.
Together, Failed FST and Failed ID indicate the current number of unsuccessful
attempts that have been made to reuse plan fragments, whether missing or
unusable. For example, an unsuccessful attempt might result because one or more
4 KB blocks of the query fragment space has been stolen by another query. When
this happens, the missing query plan must be reloaded.
The Failed FST and Failed ID fields provide the most important information about
reuse failures. You should monitor these values over a period of time, noting changes
and trends in the failure rates as different applications execute or when new resources
are added.
For information about methods for reducing the incidence of reuse failures in an
SQL/MX buffer, see Strategies for Reducing Reuse Failures on page 15-13.
For more information about using SCF STATS DISK to display SQL/MX buffer
statistics, see the SCF Reference Manual for the Storage Subsystem.
Using SCF ALTER DISK to Resize the SQLMXBUFFER Attribute
Use the SCF ALTER DISK command to change the size of the SQL/MX buffer on a
disk volume. 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-> alter disk $D*,SQLMXBUFFER 128
•
If possible, you should always stop the affected disk volume before you alter the
SQLMXBUFFER attribute, using the STOP DISK command to put the disk in the
STOPPED state. If you cannot first stop the disk (for example, if the disk volume
contains swap files that cannot be stopped without creating problems), perform the
SCF ALTER DISK command just before system coldload to minimize or eliminate
the risks of CPU failure and configuration mismatches between primary and
backup CPUs.
•
The maximum size of the SQLMXBUFFER space is 768 MB, and the minimum
size is 32 MB. The default value is 128 MB. If you specify a value below the current
minimum supported size, DP2 automatically uses the current minimum value. This
minimum value might not be sufficient to perform even moderately complex
queries, so you should avoid using it unless it is appropriate. If you specify a value
above the current maximum supported size, DP2 automatically uses the current
maximum value.
•
As a general rule, try to set SQLMXBUFFER to the lowest size possible that
achieves acceptable plan reuse failure rates. Try not to set the size above 512 MB.
Note. The default, minimum, and maximum SQLMXBUFFER sizes listed are subject to
change. The amount of total memory available to the DP2 data cache and SQL/MX buffer
space might even be reduced.