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-8
Creating More Accurate Histogram Tables With
UPDATE STATISTICS
Creating More Accurate Histogram Tables With
UPDATE STATISTICS
SQL/MX Release 2.1.1 introduced a new internal algorithm for estimating the UEC of a
column in a full table from a sample of rows in the table. This algorithm achieves
acceptable levels of both histogram accuracy and UPDATE STATISTICS performance
using relatively small table row samples.
For detailed information about these improvements and the test results that
substantiate them, see the white paper SQL/MX UPDATE STATISTICS
Enhancements in NTL at docs.hp.com.
Managing SQL/MX Buffer Space
This subsection contains this information:
•
How DP2 Manages and Reuses Query Plan Fragments on page 15-9
•
Causes and Symptoms of Query Plan Fragment Reuse Failures on page 15-9
•
Reduction of Plan Fragment Size for Unique Queries on page 15-10
•
Using SCF STATS DISK to Monitor SQL/MX Statistics and Reuse Failures on
page 15-11
•
Using SCF ALTER DISK to Resize the SQLMXBUFFER Attribute on page 15-12
•
Strategies for Reducing Reuse Failures on page 15-13
For every disk volume running NonStop SQL/MX on your system, approximately 1.1
GB of virtual space must be shared between the DP2 data cache, SQL/MX buffer, and
lock tables. The DP2 data cache provides temporary storage for disk blocks containing
table and index data that are referenced frequently. The SQL/MX buffer stores the
query plan fragments that get downloaded into DP2 and working data in transit
between the Executor or an ESP and the local DP2 process or another DP2 process.
An important challenge in managing this limited virtual space is achieving and
maintaining the proper balance between the SQL/MX buffer and DP2 data cache. If
you allow either one to use too much of this space, it can adversely affect the
performance of the other and the system in general.
Any increase in the size of SQL/MX buffer space reduces the maximum size of the
DP2 data cache by a corresponding amount. The default size of the SQL/MX buffer is
128 MB, and the maximum size of the DP2 data cache with this default is about
768 MB. If, for example, you increase the SQL/MX buffer space from 128 MB to
512 MB, the maximum size of the DP2 data cache is reduced from about 768 MB to a
little less than 400 MB.
Whether or not the SQL/MX buffer on a given disk is likely to need larger or smaller
amounts of buffer space depends on the SQL/MX environment of that disk. DP2
processes running a high volume of OLTP transactions with a large number of opens