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

• Memory Pressure denotes the number of failures caused because the memory pressure was
detected.
• No Memory denotes the number of failures caused because of non-availability of physical
memory for the allocation.
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.
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.
• 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.
• You cannot specify a value that is too small. For example, if you specify 0 (zero), the default
value of 128 MB is used. However, if you specify a buffer size that is too large, SCF rejects
the request.
• DP2 automatically rounds any positive value you specify to the modulo16 value.
• There should be sufficient physical memory in both the primary and backup DP2 CPUs to
accommodate the altered buffer configuration.
For more information, see the SCF Reference Manual for the Storage Subsystem.
Strategies for Reducing Reuse Failures
After you have confirmed that the SQL/MX buffers on one or more disks are experiencing a large
number of reuse failures, you should investigate these strategies for reducing them:
• “Verify Sufficient CPU Physical Memory” (page 299)
• “Increase SQL/MX Buffer Space” (page 300)
• “Reduce the Number of Client Processes Accessing Each Disk” (page 300)
• “Perform Data Routing at the Application Level” (page 300)
Verify Sufficient CPU Physical Memory
Check that sufficient physical memory has been provided for the system CPUs. Both the SQL/MX
buffer and DP2 data cache must be configured with adequate physical memory in both the primary
Managing SQL/MX Buffer Space 299