IBM DB2 Universal database 重重 Administration guide performance Version 8 sc09-4821-00
IBM® DB2 Universal Database™ Administration Guide: Performance Version 8 SC09-4821-00
IBM DB2 Universal database 重重 Administration guide performance Version 8 sc09-4821-00
IBM® DB2 Universal Database™ Administration Guide: Performance Version 8 SC09-4821-00
efore using this information and the product it supports. be sure to read the general information under Notices. This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties, and any tatements provided in this manual should not be interpreted as such. You can order IBM publications online or through your local IBM representative ToorderpublicationsonlinegototheIbMPublicationsCenteratwww.ibm.com/shop/publications/order To find your local IBM representative go to the IBM Directory of Worldwide Contacts at www.ibm.com/planetwide o order DB2 publications from DB2 Marketing and Sales in the United States or Canada, call 1-800-IBM-4YOU (426-4968) When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incurring any obligation to you. o Copyright International Business Machines Corporation 1993-2002. All rights US Government Users Restricted Rights- Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp
Before using this information and the product it supports, be sure to read the general information under Notices. This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties, and any statements provided in this manual should not be interpreted as such. You can order IBM publications online or through your local IBM representative. v To order publications online, go to the IBM Publications Center at www.ibm.com/shop/publications/order v To find your local IBM representative, go to the IBM Directory of Worldwide Contacts at www.ibm.com/planetwide To order DB2 publications from DB2 Marketing and Sales in the United States or Canada, call 1-800-IBM-4YOU (426-4968). When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incurring any obligation to you. © Copyright International Business Machines Corporation 1993 - 2002. All rights reserved. US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp
Contents About this book Memory management Who should use this book How this book is structured a brief overview of the other administration Part 2. Tuning application Guide volumes performance Administration Guide: Planning Administration Guide: Implementation Chapter 3 Application considerations . 51 Concurrency control and isolation levels Part 1. Introduction to performance 51 Performance impact of isolation levels.. 52 Chapter 1 Introduction to performance ..3 Concurrency control and locking Elements of performance Locks and concurrency control Performance-tuning guidelines Lock attributes The performance-tuning process Locks and performance Developing a performance-improvement Guidelines for locking 5 Correcting lock escalation problen Performance information that users can Lock type compatibility Lock modes and access paths for standard Performance-tuning limits 7 7391880247 Quick-start tips for performance tuning Lock modes for table and rid index scans of mDC tables Chapter 2. Architecture and processes Locking for block index scans for MDC DB2 architecture and process overview Deadlocks between application 14 Factors that affect locking Disk ste 15 Factors that affect locking Disk-storage performance factors Database directories and files Locks and data-access methods Table space overview 19 Index types and next-key locking Table spaces Optimization factors SMS table spaces Optimization class guidelines DMs table spaces Optimization classes 70888588803 Locks and types of application processing 84 llustration of the DMS table-space address Setting the optimization cla map. Tuning applications Tables and indexes Guidelines for restricting select statements 95 Table and index management for standard Specifying row blocking to reduce Table and index management for MDC Query tuning guidelines Efficient Select statements 101 Compound SQl guidelines 103 Processes Character-conversion guidelin Insert process Parallel processing for applications Update process Client-server processing model Chapter 4. Environmental considerations 111 o Copyright IBM Corp. 1993-2002
Contents About this book . . . . . . . . . . ix Who should use this book. . . . . . . .x How this book is structured . . . . . . .x A brief overview of the other Administration Guide volumes . . . . . . . . . . . xi Administration Guide: Planning . . . . xi Administration Guide: Implementation . . xii Part 1. Introduction to performance 1 Chapter 1. Introduction to performance . . 3 Elements of performance . . . . . . . .3 Performance-tuning guidelines . . . . . .4 The performance-tuning process . . . . .5 Developing a performance-improvement process . . . . . . . . . . . . .5 Performance information that users can provide . . . . . . . . . . . . .6 Performance-tuning limits . . . . . . .7 Quick-start tips for performance tuning . . . 7 Chapter 2. Architecture and processes . . 11 DB2 architecture and process overview . . . 11 Deadlocks between applications . . . . . 14 Disk storage overview . . . . . . . . 15 Disk-storage performance factors . . . . 15 Database directories and files . . . . . 16 Table space overview . . . . . . . . . 19 Table spaces . . . . . . . . . . . 19 SMS table spaces . . . . . . . . . 19 DMS table spaces . . . . . . . . . 20 Illustration of the DMS table-space address map. . . . . . . . . . . . . . 22 Tables and indexes . . . . . . . . . . 23 Table and index management for standard tables . . . . . . . . . . . . . 23 Table and index management for MDC tables . . . . . . . . . . . . . 28 Index structure . . . . . . . . . . 31 Processes . . . . . . . . . . . . . 32 Logging process. . . . . . . . . . 33 Insert process . . . . . . . . . . 34 Update process . . . . . . . . . . 35 Client-server processing model. . . . . 36 Memory management. . . . . . . . 44 Part 2. Tuning application performance . . . . . . . . . . 49 Chapter 3. Application considerations . . 51 Concurrency control and isolation levels . . 51 Concurrency issues. . . . . . . . . 51 Performance impact of isolation levels . . 52 Specifying the isolation level . . . . . 57 Concurrency control and locking . . . . . 59 Locks and concurrency control . . . . . 59 Lock attributes . . . . . . . . . . 61 Locks and performance . . . . . . . 63 Guidelines for locking. . . . . . . . 68 Correcting lock escalation problems . . . 70 Lock type compatibility . . . . . . . 72 Lock modes and access paths for standard tables . . . . . . . . . . . . . 74 Lock modes for table and RID index scans of MDC tables . . . . . . . . . . 77 Locking for block index scans for MDC tables . . . . . . . . . . . . . 80 Factors that affect locking . . . . . . . 83 Factors that affect locking . . . . . . . 84 Locks and types of application processing 84 Locks and data-access methods . . . . 85 Index types and next-key locking . . . . 86 Optimization factors . . . . . . . . . 88 Optimization class guidelines . . . . . 88 Optimization classes . . . . . . . . 90 Setting the optimization class . . . . . 93 Tuning applications . . . . . . . . . 95 Guidelines for restricting select statements 95 Specifying row blocking to reduce overhead . . . . . . . . . . . . 99 Query tuning guidelines . . . . . . 101 Efficient SELECT statements . . . . . 101 Compound SQL guidelines . . . . . 103 Character-conversion guidelines . . . . 105 Guidelines for stored procedures. . . . 106 Parallel processing for applications . . . 107 Chapter 4. Environmental considerations 111 © Copyright IBM Corp. 1993 - 2002 iii
Database partition group impact on query Compiler rewrite example: implied predicates 173 Table space impact on query optimization 111 Column correlation for multiple rvel options affecting fede era predicates 174 Data access methods Chapter 5. System catalog statistics 117 Data-access methods and concepts Catalog statistics 117 Data access through index scans Collecting and analyzing catalog statistics 119 Types of index access 181 Guidelines for collecting and updating Index access and cluster ratios statistics Predicate terminology Collecting catalog statistics Join methods and strategies Collecting distribution statistics for Joins Join methods Collecting index statistics Strategies for selecting optimal joins 191 Statistics collected Replicated materialized-query tables in Catalog statistics tables artitioned database Statistical information that is collected 131 Join strategies in partitioned databases 196 Distribution statistics Join methods in partitioned databases.. 198 Optimizer use of distribution statistics Effects of sorting and grouping Extended examples of Optimization strategies distribution-statistics use Optimization strategies for intra-partition Detailed index statistics 142 parallelism Sub-element statistics Optimization strategies for MDC tables 209 sers can Automatic summary tables Statistics for user-defined function 146 Federated database query-compiler phases 213 Catalog statistics for modeling and Federated database pushdown analysis 213 what-if planning 147 Guidelines for analyzing where a Statistics for modeling production federated query is evaluated databases Remote SQL generation and global General rules for updating catalog mization in federated databases 220 statistics manually al analysis of federated database Rules for updating column statistics manually 153 Rules for updating distribution statistics Chapter 7. SQL Explain facility manually SOL explain facilit 227 Rules for updating table and nickname Tools for collecting and analyzing explain statistics manually information Rules for updating index statistics Explain tools 228 Guidelines for using explain information 230 Explain information collected 32 Chapter 6. Understanding the SQL The explain tables and organization of The SQL compiler process Explain information for data objects Configuration parameters that affect query Explain information for data operators 234 Explain information for instances Query rewriting Guidelines for capturing explain Query rewriting methods and examples 166 information Guidelines for analyzing explain information 24 Compiler rewrite example: DISTINCT The Design Advisor Iv Administration Guide: Performance
Database partition group impact on query optimization . . . . . . . . . . . 111 Table space impact on query optimization 111 Server options affecting federated databases 115 Chapter 5. System catalog statistics. . . 117 Catalog statistics . . . . . . . . . . 117 Collecting and analyzing catalog statistics 119 Guidelines for collecting and updating statistics . . . . . . . . . . . . 119 Collecting catalog statistics. . . . . . 120 Collecting distribution statistics for specific columns . . . . . . . . . 121 Collecting index statistics . . . . . . 123 Statistics collected. . . . . . . . . . 124 Catalog statistics tables . . . . . . . 124 Statistical information that is collected 131 Distribution statistics. . . . . . . . 134 Optimizer use of distribution statistics 136 Extended examples of distribution-statistics use . . . . . . 141 Detailed index statistics . . . . . . . 142 Sub-element statistics . . . . . . . 144 Catalog statistics that users can update . . 145 Statistics for user-defined functions . . . 146 Catalog statistics for modeling and what-if planning . . . . . . . . . 147 Statistics for modeling production databases . . . . . . . . . . . 149 General rules for updating catalog statistics manually . . . . . . . . 152 Rules for updating column statistics manually . . . . . . . . . . . 153 Rules for updating distribution statistics manually . . . . . . . . . . . 153 Rules for updating table and nickname statistics manually . . . . . . . . 154 Rules for updating index statistics manually . . . . . . . . . . . 155 Chapter 6. Understanding the SQL compiler . . . . . . . . . . . . 159 The SQL compiler process . . . . . . . 159 Configuration parameters that affect query optimization . . . . . . . . . . . 163 Query rewriting . . . . . . . . . . 166 Query rewriting methods and examples 166 Compiler rewrite example: view merges 168 Compiler rewrite example: DISTINCT elimination . . . . . . . . . . . 171 Compiler rewrite example: implied predicates . . . . . . . . . . . 173 Column correlation for multiple predicates . . . . . . . . . . . 174 Data access methods . . . . . . . . . 176 Data-access methods and concepts . . . 176 Data access through index scans . . . . 177 Types of index access . . . . . . . 181 Index access and cluster ratios . . . . 183 Predicate terminology . . . . . . . . 184 Join methods and strategies . . . . . . 186 Joins . . . . . . . . . . . . . 187 Join methods . . . . . . . . . . 190 Strategies for selecting optimal joins . . 191 Replicated materialized-query tables in partitioned databases . . . . . . . 194 Join strategies in partitioned databases 196 Join methods in partitioned databases . . 198 Effects of sorting and grouping . . . . . 204 Optimization strategies . . . . . . . . 206 Optimization strategies for intra-partition parallelism . . . . . . . . . . . 206 Optimization strategies for MDC tables 209 Automatic summary tables. . . . . . . 210 Federated database query-compiler phases 213 Federated database pushdown analysis 213 Guidelines for analyzing where a federated query is evaluated . . . . . 218 Remote SQL generation and global optimization in federated databases. . . 220 Global analysis of federated database queries . . . . . . . . . . . . 223 Chapter 7. SQL Explain facility . . . . 227 SQL explain facility . . . . . . . . . 227 Tools for collecting and analyzing explain information . . . . . . . . . . . . 228 Explain tools . . . . . . . . . . 228 Guidelines for using explain information 230 Explain information collected . . . . . . 232 The explain tables and organization of explain information . . . . . . . . 232 Explain information for data objects . . 234 Explain information for data operators 234 Explain information for instances . . . 235 Guidelines for capturing explain information . . . . . . . . . . . 239 Guidelines for analyzing explain information 241 The Design Advisor . . . . . . . . . 242 iv Administration Guide: Performance