CONTENTS Title Page Copyright Page Send Us Your Comments 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 OpenVMS 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 OpenVMS Utilities, Tools, and Layered Products 1.3.2 RMU Commands 1.3.3 DEC Rdb 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.5 Sample Procedure for Performance Evaluation 1.4.6 VMScluster Performance Considerations 2 Database Performance Analysis Tools 2.1 RMU/ANALYZE Command 2.1.1 RMU/ANALYZE Command Qualifiers 2.1.1.1 Excluding DEC Rdb Information from RMU/ANALYZE Command Output 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 Syntax for the RMU/SHOW STATISTICS Command 2.2.3 Selecting a Display Mode 2.2.4 Using Menus to Select Display Pages 2.2.5 Selecting a Display Format 2.2.5.1 Graphic Display Format 2.2.5.2 Numbers Display Format 2.2.5.3 Time Plot Display Format 2.2.5.4 Table Display Format 2.2.6 Writing a Display to a File 2.2.7 Getting Online Help 2.2.8 Types of Database Statistics 2.2.9 Understanding the Display Pages 2.2.10 Getting Statistics Output in a Formatted Binary Output File 2.3 DEC Rdb Logical Names 2.3.1 RMU/SHOW STATISTICS Defined Logicals Display 2.4 DECtrace for OpenVMS 2.4.1 DEC Rdb Instrumentation 2.4.2 Overview of Using DECtrace 2.4.2.1 Creating a Selection 2.4.2.2 Scheduling Data Collection 2.4.2.3 Stopping a Collection 2.5 Collecting Workload Information for RdbExpert 2.5.1 Displaying Event-Data Interactively 2.5.2 Creating a Report Based on Collected Data 2.5.2.1 Formatting and Merging Data Files 2.5.2.2 Generating a Report 2.5.3 Creating a Customized Report 2.5.4 Improving Report Performance 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 Active User Stall Messages Display 3.2.1.5 RMU/SHOW STATISTICS Transaction Durations Display 3.2.1.6 Reducing Disk I/O 3.2.2 Data Distribution 3.2.3 Data Content-Active Versus Inactive Rows 3.2.4 Asynchronous Prefetch of Database Pages 3.2.5 Asynchronous Batch-Write Operations 3.3 After-Image Journaling 3.3.1 Using the AIJ Log Server (ALS) to Improve the Performance of After-Image Journal File Write Operations to Disk 3.3.2 Improving ALS Process Performance by Using an AIJ Cache on an Electronic Disk 3.3.3 Improving Performance by Disabling After-Image Journaling for WORM Storage Areas 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.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.1.1 Interpreting the Meaning of the Granted and Requested Modes in RMU/SHOW LOCKS Output Displays 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.1.5 RMU/SHOW STATISTICS Lock Deadlock History Display 3.8.1.6 RMU/SHOW STATISTICS Lock Timeout History Display 3.8.1.7 System Dump Analyzer 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 Optimizing Update Carry-Over Locks for Tables 3.8.3.4 Explanation of ``Lock Conflict on Freeze Lock'' Errors 3.8.3.5 Batch-Update Transactions 3.8.3.6 Update Locking for Cursors 3.8.4 Transaction Scope 3.8.5 Adjustable Lock Granularity 3.8.6 Selecting Page-Level or Record-Level Locking 3.8.7 Recoverable Latches 3.8.8 Read-Only Storage Areas 3.8.9 Using an RDB$SYSTEM Read-Only Storage Area 3.9 Index Retrieval 3.9.1 Index Compression 3.9.1.1 Prefix and Suffix Compression 3.9.1.2 SIZE IS Segment Truncation 3.9.1.3 MAPPING VALUES Compression 3.9.1.4 Run-Length Compression 3.9.2 System Index Compression 3.9.3 Gathering Index Information 3.9.3.1 RMU/ANALYZE/INDEXES Display 3.9.3.2 RMU/SHOW STATISTICS Hash Index Statistics Display 3.9.3.3 RMU/SHOW STATISTICS Index Statistics (Retrieval) Display 3.9.3.4 RMU/SHOW STATISTICS Index Statistics (Insertion) Display 3.9.3.5 RMU/SHOW STATISTICS Index Statistics (Removal) Display 3.9.4 Disabling an Index 3.9.5 Sorted Index Structure 3.9.5.1 Chronological Key 3.9.5.2 Duplicate Nodes Key 3.9.5.3 Clustering Indexes 3.9.5.4 Preventing Performance Degradation That May Occur over Time Using Sorted Indexes 3.9.5.5 Forward and Reverse Scans Using a Sorted Index 3.9.6 Sequential Retrieval 3.9.7 Hashed Index Structure 3.9.7.1 Hashed Index Performance Factors 3.9.7.2 Potential Sizing Problems 3.9.7.3 Shadow Pages 3.9.8 Selecting the HASHED SCATTERED or HASHED ORDERED Hashing Algorithms 3.10 Recognizing Poor Insert Performance Caused by Excessive Page Checking 3.10.1 Incorrect Threshold Settings 3.10.2 Locked Free Space 3.10.3 Stored Values for AIP Lengths May Reflect the Actual Length of Table Records 3.10.3.1 AIP Length Problems in Indexes That Allow Duplicates 3.10.3.2 AIP Length Problems in Unique Indexes and Modified Tables Prior to DEC Rdb Version 4.2 3.10.3.3 AIP Length Problems in Segmented Strings 4 Adjusting Parameters 4.1 Adjusting Database Parameters 4.1.1 Gathering Database Parameter Information 4.1.1.1 RMU/SHOW STATISTICS PIO Statistics-Writes Display 4.1.1.2 RMU/SHOW STATISTICS PIO Statistics-Data Fetches Display 4.1.1.3 RMU/SHOW STATISTICS PIO Statistics-SPAM Fetches Display 4.1.1.4 RMU/SHOW STATISTICS Asynchronous PIO Statistics Display 4.1.1.5 RMU/SHOW STATISTICS Process Accounting Display 4.1.1.6 RMU/SHOW STATISTICS Record Statistics Display 4.1.1.7 RMU/SHOW STATISTICS AIJ Statistics Display 4.1.1.8 RMU/SHOW STATISTICS AIJ Information Display 4.1.1.9 RMU/SHOW STATISTICS Snapshot Statistics Display 4.1.1.10 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 NUMBER IS Parameter 4.1.2.7 USER LIMIT Parameter 4.1.2.8 Tuning Global Buffers 4.1.2.9 Benefits of Global Buffer Overflow Management 4.1.2.10 Benefits of Data Persistence in Global Buffer Memory 4.1.2.11 Modifying Parameters When Global Buffers Are Enabled 4.1.2.12 Analyzing Global Buffer Performance 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.3.5 Optimized Page Transfers 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 DEC Rdb 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.2.8 Initializing, Moving, and Changing the Allocation of Snapshot Files 4.2.9 Snapshot File Growth and Prestarted Transactions 4.3 Adjusting Storage Map Parameters 4.3.1 Gathering Storage Map Parameter Information 4.3.1.1 Using the RMU/ANALYZE/PLACEMENT/OPTION=NORMAL Command 4.3.1.2 Using the RMU/ANALYZE/PLACEMENT/OPTION=FULL Command 4.3.1.3 Using the RMU/ANALYZE/PLACEMENT/OPTION=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 OpenVMS Parameters for DEC Rdb 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.2.4.1 How the Optimizer Estimates Page Size for Tables That Store Data in Multiple Storage Areas 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 5.8 Ensuring Query Stability, Controllability, and Performance with Query Outlines 5.8.1 Using Optimizer Output to Define an Outline to Be Stored 5.8.2 Specifying Outline Directives 5.8.3 Defining and Storing an Outline for a Stored Procedure 5.8.4 Modifying an Existing Outline 5.8.4.1 Creating Multiple Outlines for a Single Query 5.8.4.2 Complete Outlines 5.8.4.3 Partial Outlines 5.8.4.4 Mandatory Outlines 5.8.4.5 Optional Outlines 5.8.5 Using Logical Names to Control Which Outlines the Optimizer Uses 5.8.5.1 Visible Effects of Outlines to Users 5.8.6 Invalidation of Stored Outlines 5.8.7 Deleting an Outline 6 Using DEC Rdb in a VMScluster Environment 6.1 Overview of a VMScluster Environment 6.1.1 Definition of a VMScluster Environment 6.1.2 Shared Storage Devices 6.1.3 Shared Disk Files 6.1.4 Dual-Ported Disks 6.1.5 Dual Pathing 6.1.6 Device-Naming Conventions 6.1.7 Common System Disk 6.1.8 OpenVMS Lock Manager 6.1.9 Distributed Transactions 6.1.10 Client/Server Computing 6.1.11 Partitioned Data Access and Shared Data Access 6.2 DEC Rdb in a VMScluster Environment 6.2.1 Single-Node Environments and VMScluster Environments 6.2.2 Making a Database Accessible and Available in a VMScluster Environment 6.2.3 Specifying Maximum VMScluster Nodes 6.2.4 Multiple Monitor Processes 6.2.5 Deciding Where to Place DEC Rdb Files 6.2.6 CDD/Repository Requirements 6.3 Creating the MF_PERSONNEL Database in a VMScluster Environment 6.4 Converting a Single-Node Database to a VMScluster Database 6.5 Automatic Recovery Procedure 6.5.1 RMU/SHOW STATISTICS DBR Activity Display 6.6 Maintaining and Monitoring Your Database 6.6.1 Local Area VMScluster Configuration Considerations 6.6.2 Monitoring Your Database Part II Database Tuning 7 Tuning Concepts and Methodology 7.1 What Is Tuning? 7.2 Determining When to Tune 7.3 Types of Resources 7.4 Sample Database Application 7.5 Tuning Methodology 7.6 Determining What to Tune 7.6.1 Tuning the System 7.6.2 Tuning the Database 7.6.3 Tuning the Application 8 Diagnosing a Database Resource Bottleneck 8.1 Analyzing I/O Resources 8.1.1 Detecting I/O Resource Bottlenecks 8.1.2 Balancing I/O Load 8.1.2.1 Checking CDD/Repository 8.1.2.2 Checking AIJ 8.1.2.3 Checking Data Distribution 8.1.3 Reducing I/O Operations 8.1.3.1 Checking Constraints 8.1.3.2 Checking Indexes 8.1.3.3 Checking Node Size 8.1.3.4 Checking Clustering 8.1.3.5 Checking Hashed Indexes 8.1.3.6 Checking Snapshots 8.2 Analyzing Memory Resources 8.3 Analyzing CPU Resources 8.4 Analyzing Lock Resources A DEC Rdb Logical Names A.1 RDB$CHARACTER_SET A.2 RDB$RDBSHR_EVENT_FLAGS A.3 RDB$REMOTE_BUFFER_SIZE A.4 RDB$REMOTE_MULTIPLEX_OFF A.5 RDBVMS$CREATE_DB A.6 RDM$BIND_ABW_DISABLED A.7 RDM$BIND_AIJ_STALL A.8 RDM$BIND_APF_DEPTH A.9 RDM$BIND_APF_DISABLED A.10 RDM$BIND_BATCH_MAX A.11 RDM$BIND_BUFFERS A.12 RDM$BIND_CKPT_TRANS_INTERVAL A.13 RDM$BIND_CLEAN_BUF_CNT A.14 RDM$BIND_COMMIT_STALL A.15 RDM$BIND_LOCK_TIMEOUT_INTERVAL A.16 RDM$BIND_READY_AREA_SERIALLY A.17 RDM$BIND_RUJ_EXTEND_BLKCNT A.18 RDM$BIND_STATS_DISABLED A.19 RDM$BIND_VM_SEGMENT A.20 RDM$BUGCHECK_DIR A.21 RDM$MAILBOX_CHANNEL A.22 RDM$MONITOR A.23 RDM$MON_USERNAME A.24 RDMS$AUTO_READY A.25 RDMS$BIND_OUTLINE_FLAGS A.26 RDMS$BIND_OUTLINE_MODE A.27 RDMS$BIND_QG_CPU_TIMEOUT A.28 RDMS$BIND_QG_REC_LIMIT A.29 RDMS$BIND_QG_TIMEOUT A.30 RDMS$BIND_SEGMENTED_STRING_BUFFER A.31 RDMS$BIND_SEGMENTED_STRING_COUNT A.32 RDMS$BIND_SEGMENTED_STRING_DBKEY_SCOPE A.33 RDMS$BIND_SORT_WORKFILES A.34 RDMS$BIND_VALIDATE_CHANGE_FIELD A.35 RDMS$BIND_WORK_FILE A.36 RDMS$BIND_WORK_VM A.37 RDMS$DEBUG_FLAGS A.38 RDMS$DEBUG_FLAGS_OUTPUT A.39 RDMS$DIAG_FLAGS A.40 RDMS$KEEP_PREP_FILES A.41 RDMS$RUJ A.42 RDMS$USE_OLD_CONCURRENCY A.43 RDMS$USE_OLD_SEGMENTED_STRING A.44 RDMS$USE_OLD_UPDATE_RULES A.45 RDO$EDIT A.46 RDOINI A.47 SQL$DATABASE A.48 SQL$DISABLE_CONTEXT A.49 SQL$EDIT A.50 SQLINI A.51 SQL$KEEP_PREP_FILES B DEC Rdb Event-Based Data Tables B.1 DEC Rdb PERFORMANCE Class Database Tables B.2 DEC Rdb RDBEXPERT Class Database Tables C Using RDMS$DEBUG_FLAGS to Analyze the Query Optimizer C.1 Displaying Optimization Strategy with the S Flag C.2 Displaying Outlines Generated by the Optimizer with the Ss Flag C.3 Displaying Constraint Names and the Query Strategy with the Sn Flag C.4 Displaying Optimization Statistics with the O Flag C.5 Displaying the Optimization Strategy and Cost of Optimization Using the SO Flags C.6 Displaying the Optimization Strategy and Execution Trace with the SE Flags C.7 Displaying Sort Statistics with the R Flag C.8 Displaying Transaction Activity with the T Flag C.9 Logging the TRACE Control Statement with the Xt Flag