SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Contents

HP NonStop SQL/MX Installation and Management Guide—544536-007

xiii

15. Enhancing SQL/MX Database Performance

Using Queries in an SQL/MX Database 15-2

Understanding the Implications of Concurrency 15-2

Minimizing Contention 15-4

Avoiding Contention Between DDL or Utility Operations 15-6

Other Operational Considerations 15-6

Keeping Statistics Current 15-7

Creating More Accurate Histogram Tables With UPDATE STATISTICS 15-8

Managing SQL/MX Buffer Space 15-8

How DP2 Manages and Reuses Query Plan Fragments 15-9

Causes and Symptoms of Query Plan Fragment Reuse Failures 15-9

Reduction of Plan Fragment Size for Unique Queries 15-10

Using SCF STATS DISK to Monitor SQL/MX Statistics and Reuse Failures 15-11

Using SCF ALTER DISK to Resize the SQLMXBUFFER Attribute 15-12

Strategies for Reducing Reuse Failures 15-13

Managing DP2 Data Cache Memory Size 15-14

Maximizing Disk Process Prefetch Capabilities 15-15

Optimizing SQL/MX Memory Management 15-15

Optimizing Index Use 15-16

Maximizing Parallel Index Maintenance 15-17

Using Co-located Indexes 15-17

Performing FUP RELOADs to Generate More Accurate Query Plans 15-17

Checking Data Integrity 15-18

Creating Logical Views of Data 15-19

Adding and Dropping Partitions 15-19

Avoiding Automatic Recompilations 15-20

Matching Block Split Operation to Table Usage 15-20

A. Using Guardian Names with TMF, RDF, and Measure

Using the MXGNAMES Utility A-1

MXGNAMES Input Files A-2

MXGNAMES Output Files A-2

MXGNAMES Examples A-4

Index

Figures

Figure 4-1. Key-Sequenced B-Tree Structure 4-7

Figure 8-1. SQL/MX Metadata Table Structure 8-5

Figure 8-2. SQL/MX Metadata Tables 8-7