CONTENTS Title Page Copyright Page Preface Technical Changes and New Features Part I Database Performance 1 Database Performance Overview 1.1 Performance and Tuning 1.2 Performance Factors 1.2.1 System Resources and Memory Management 1.2.2 VMS System Parameters and Process Parameters 1.2.3 Database Design 1.2.4 Application Design 1.2.5 Possible Performance-Related Changes 1.3 Performance Utilities and Tools 1.3.1 VMS Utilities, Tools, and Layered Products 1.3.2 RMU Commands 1.3.3 Rdb/VMS Logical Names 1.4 Performance Analysis Methodology 1.4.1 Tuning Guidelines 1.4.2 Establishing a Context to Interpret Tuning Results 1.4.3 Using a Test Database 1.4.4 Making Changes 1.4.4.1 Make Single Changes 1.4.4.2 Make Changes in Order of Difficulty 1.4.4.3 Develop SQL ALTER DATABASE, RDO CHANGE DATABASE, SQL or RDO EXPORT and IMPORT Procedures 1.4.5 Sample Performance Evaluation Procedure 1.4.6 VAXcluster Performance Considerations 2 Database Performance Analysis Tools 2.1 RMU/ANALYZE Command 2.1.1 RMU/ANALYZE Command Qualifiers 2.1.2 Creating a Binary Output File for Further Analysis 2.2 RMU/SHOW STATISTICS Command 2.2.1 Using Database Statistics 2.2.2 Selecting a Display Mode 2.2.3 Selecting a Display Page 2.2.4 Selecting a Display Format 2.2.4.1 Graphic Display Format 2.2.4.2 Numbers Display Format 2.2.4.3 Time Plot Display Format 2.2.5 Writing a Display to a File 2.2.6 Getting Online Help 2.2.7 Types of Database Statistics 2.2.8 Understanding the Display Pages 2.2.9 Getting Statistics Output in a Formatted Binary Output File 2.3 Rdb/VMS Logical Names 2.4 DECtrace for VMS 2.4.1 Collecting Event Data for Rdb/VMS 2.4.1.1 Describing Rdb/VMS Events and Items 2.4.1.2 Creating a Selection 2.4.1.3 Scheduling Data Collection 2.4.2 Using Registration IDs 2.5 Collecting Workload Information for RdbExpert 2.5.1 Creating a Report Based on Collected Data 2.5.1.1 Formatting and Merging Data Files 2.5.1.2 Generating a Report 2.5.2 Creating a Customized Report 3 Analyzing Performance Factors 3.1 Database Design Considerations 3.2 Disk I/O 3.2.1 Gathering Disk I/O Information 3.2.1.1 RMU/SHOW STATISTICS Summary I/O Statistics Display 3.2.1.2 RMU/SHOW STATISTICS I/O Stall Time Display 3.2.1.3 RMU/SHOW STATISTICS Stall Messages Display 3.2.1.4 RMU/SHOW STATISTICS Transaction Durations Display 3.2.1.5 Reducing Disk I/O 3.2.2 Data Distribution 3.2.3 Data Content-Active Versus Inactive Rows 3.3 After-Image Journaling 3.4 Database Integrity Considerations 3.5 Constraint Optimizations 3.5.1 Existence Constraint 3.5.2 Uniqueness Constraint 3.5.3 Modification Operation 3.5.4 Database Key Retrieval and Erasing 3.5.5 Multiple Evaluations 3.6 Loading the Database 3.7 Remote Access 3.8 Locking 3.8.1 Gathering Lock Information 3.8.1.1 RMU/SHOW LOCKS Command 3.8.1.2 RMU/SHOW STATISTICS Summary Locking Statistics Display 3.8.1.3 RMU/SHOW STATISTICS Lock Statistics for One Lock Type Display 3.8.1.4 RMU/SHOW STATISTICS Lock Statistics for One Statistics Field Display 3.8.2 Lock Considerations 3.8.3 Reserving Options 3.8.3.1 Incompatible Share Mode and Lock Type 3.8.3.2 Carry-Over Locks and the [NO]WAIT Option 3.8.3.3 Batch-Update Transactions 3.8.3.4 Update Locking for Cursors 3.8.4 Transaction Scope 3.8.5 Adjustable Lock Granularity 3.8.6 Read-Only Storage Areas 3.8.7 Using an RDB$SYSTEM Read-Only Storage Area 3.9 Indexed Retrieval 3.9.1 Index Compression 3.9.2 Gathering Index Information 3.9.2.1 RMU/ANALYZE/INDEXES Display 3.9.2.2 Hashed Index Statistics 3.9.2.3 RMU/SHOW STATISTICS Index Statistics (Retrieval) Display 3.9.2.4 RMU/SHOW STATISTICS Index Statistics (Insertion) Display 3.9.2.5 RMU/SHOW STATISTICS Index Statistics (Removal) Display 3.9.3 Sorted Index Structure 3.9.3.1 Chronological Key 3.9.3.2 Duplicate Nodes Key 3.9.3.3 Clustering Indexes 3.9.4 Sequential Retrieval 3.9.5 Setting Sorted Index Characteristics for Performance 3.9.6 Hashed Index Structure 3.9.6.1 Hashed Index Performance Factors 3.9.6.2 Potential Sizing Problems 3.9.6.3 Shadow Pages 4 Adjusting Parameters 4.1 Adjusting Database Parameters 4.1.1 Gathering Database Parameter Information 4.1.1.1 RMU/SHOW STATISTICS PIO Statistics Display 4.1.1.2 RMU/SHOW STATISTICS Record Statistics Display 4.1.1.3 RMU/SHOW STATISTICS AIJ Statistics Display 4.1.1.4 RMU/SHOW STATISTICS Snapshot Statistics Display 4.1.1.5 RMU/SHOW STATISTICS Checkpoint Statistics Display 4.1.2 Managing Buffers 4.1.2.1 Specifying Buffer Size 4.1.2.2 Specifying the Default Number of User Buffers 4.1.2.3 Tuning Local Buffers 4.1.2.4 Global Buffer Pools 4.1.2.5 Enabling Global Buffers 4.1.2.6 Tuning Global Buffers 4.1.3 Fast Commit Transaction Processing 4.1.3.1 Fast Commit Processing Method 4.1.3.2 Checkpointing 4.1.3.3 The Journal Optimization Option 4.1.3.4 Enabling Fast Commit Transaction Processing 4.1.4 Row or Record Fragmentation 4.1.5 Specifying the Number of Recovery Buffers 4.1.6 Allocation for the After-Image Journal File 4.1.7 Allocation for Snapshot Files 4.1.8 Extents for After-Image Journal and Snapshot Files 4.1.9 Accessing the Snapshot File 4.1.10 Making the Snapshot File Optional for Rdb/VMS 4.1.11 Deferred Snapshots Capability 4.2 Adjusting Storage Area Parameters 4.2.1 Gathering Storage Area Parameter Information 4.2.1.1 RMU/ANALYZE/AREAS Command 4.2.1.2 RMU/ANALYZE/LAREAS Display 4.2.1.3 RMU/SHOW STATISTICS I/O Statistics Display 4.2.2 Page Size 4.2.3 Allocation Size 4.2.4 Page Format 4.2.5 General Guidelines for Selecting SPAM Threshold Values 4.2.5.1 Thresholds for Mixed Format Pages 4.2.5.2 Thresholds for Uniform Format Pages 4.2.6 Optimizing SPAM Intervals 4.2.7 Placing Snapshot, Storage Area, and Database Files on Separate Disks 4.3 Adjusting Storage Map Parameters 4.3.1 Gathering Storage Map Parameter Information 4.3.1.1 Using the RMU/ANALYZE/PLACEMENT/OPTIONS=NORMAL Command 4.3.1.2 Using the RMU/ANALYZE/PLACEMENT/OPTIONS=FULL Command 4.3.1.3 Using the RMU/ANALYZE/PLACEMENT/OPTIONS=DEBUG Command 4.3.2 PLACEMENT VIA INDEX Option 4.3.3 Data Compression Option for a Table 4.3.3.1 Examples of Setting Data Compression 4.3.3.2 Summary of Data Compression Options 4.4 Adjusting VMS Parameters for Rdb/VMS Applications 4.4.1 RMU/SHOW STATISTICS Virtual Memory Usage Statistics Display 4.4.2 Checking and Setting System Parameters 4.4.3 Tuning Working Set Adjustment Parameters 4.4.4 Checking and Setting User Account Parameters 5 The Query Optimizer 5.1 Optimizer Responsibilities 5.2 Optimizer Terminology 5.2.1 Cardinality 5.2.1.1 Table Cardinality 5.2.1.2 Index Cardinality 5.2.1.3 Correcting Table and Index Cardinality 5.2.2 Predicate Selectivity 5.2.3 Strategy 5.2.4 Cost 5.3 Query Optimizer Overview 5.4 Single Table Retrieval Methods 5.5 Multiple Table Access Strategies 5.5.1 Cross Join 5.5.2 Match Join 5.5.3 Match, Zigzag 5.5.4 Merge 5.6 Dynamic Optimization 5.6.1 Dynamic OR Optimization 5.6.2 Dynamic Leaf Optimization 5.6.2.1 Background Only Retrieval 5.6.2.2 Fast First Retrieval 5.6.2.3 Index Only Retrieval 5.6.2.4 Sorted Order Retrieval 5.7 Working with the Query Optimizer 5.7.1 Views and Query Optimization 5.7.2 Queries Not Directly Based on the First Key Segment 5.7.3 Index Placement 5.7.4 Specifying a Preferred Optimization Mode 5.7.5 Using the Query Governor 5.7.6 Using RDMS$DEBUG_FLAGS 5.7.7 Using Query Cost Estimates 5.7.8 Miscellaneous Hints 6 Using Rdb/VMS in a VAXcluster Environment 6.1 Overview of a VAXcluster Environment 6.1.1 Definition of a VAXcluster Environment 6.1.2 Sharing Disk Files 6.1.3 Dual-Ported Disks 6.1.4 Disk Naming Conventions 6.1.5 Common System Disk 6.1.6 Distributed Lock Manager 6.2 Rdb/VMS in a VAXcluster Environment 6.2.1 Single-Node Environments and VAXcluster Environments 6.2.2 Specifying Maximum VAXcluster Nodes 6.2.3 Multiple Monitor Processes 6.2.4 Distributed Access to the Root Portion of the Database File