| |
| |
| Foreword | |
| |
| |
| Preface | |
| |
| |
| |
| Basic Principles | |
| |
| |
| |
| The Power of Principles | |
| |
| |
| |
| Five Basic Principles | |
| |
| |
| |
| Think Globally; Fix Locally | |
| |
| |
| |
| Partitioning Breaks Bottlenecks | |
| |
| |
| |
| Start-Up Costs Are High; Running Costs Are Low | |
| |
| |
| |
| Render unto Server What Is Due unto Server | |
| |
| |
| |
| Be Prepared for Trade-Offs | |
| |
| |
| |
| Basic Principles and Knowledge | |
| |
| |
| |
| Tuning The Guts | |
| |
| |
| |
| Goal of Chapter | |
| |
| |
| |
| Locking and Concurrency Control | |
| |
| |
| |
| Correctness Considerations | |
| |
| |
| |
| Lock Tuning | |
| |
| |
| |
| Logging and the Recovery Subsystem | |
| |
| |
| |
| Principles of Recovery | |
| |
| |
| |
| Tuning the Recovery Subsystem | |
| |
| |
| |
| Operating System Considerations | |
| |
| |
| |
| Scheduling | |
| |
| |
| |
| Database Buffer | |
| |
| |
| |
| How Much Memory Is Economical | |
| |
| |
| |
| Multiprogramming Level | |
| |
| |
| |
| Files: Disk Layout and Access | |
| |
| |
| |
| Hardware Tuning | |
| |
| |
| |
| Tuning the Storage Subsystem | |
| |
| |
| |
| Enhancing the Hardware Configuration | |
| |
| |
| Bibliography | |
| |
| |
| Exercises | |
| |
| |
| |
| Index Tuning | |
| |
| |
| |
| Goal of Chapter | |
| |
| |
| |
| Types of Queries | |
| |
| |
| |
| Key Types | |
| |
| |
| |
| Data Structures | |
| |
| |
| |
| Structures Provided by Database Systems | |
| |
| |
| |
| Data Structures for In-Memory Data | |
| |
| |
| |
| Sparse Versus Dense Indexes | |
| |
| |
| |
| To Cluster or Not to Cluster | |
| |
| |
| |
| Evaluation of Clustering Indexes | |
| |
| |
| |
| Nonclustering Indexes | |
| |
| |
| |
| Composite Indexes | |
| |
| |
| |
| Joins, Foreign Key Constraints, and Indexes | |
| |
| |
| |
| Avoid Indexes on Small Tables | |
| |
| |
| |
| Summary: Table Organization and Index Selection | |
| |
| |
| |
| Distributing the Indexes of a Hot Table | |
| |
| |
| |
| General Care and Feeding of Indexes | |
| |
| |
| Bibliography | |
| |
| |
| Exercises | |
| |
| |
| |
| Tuning Relational Systems | |
| |
| |
| |
| Goal of Chapter | |
| |
| |
| |
| Table Schema and Normalization | |
| |
| |
| |
| Preliminary Definitions | |
| |
| |
| |
| Some Schemes Are Better Than Others | |
| |
| |
| |
| Normalization by Example | |
| |
| |
| |
| A Practical Way to Design Relations | |
| |
| |
| |
| Functional Dependency Test | |
| |
| |
| |
| Tuning Normalization | |
| |
| |
| |
| Tuning Denormalization | |
| |
| |
| |
| Clustering Two Tables | |
| |
| |
| |
| Aggregate Maintenance | |
| |
| |
| |
| Record Layout | |
| |
| |
| |
| Query Tuning | |
| |
| |
| |
| Minimizing DISTINCTs | |
| |
| |
| |
| Rewriting of Nested Queries | |
| |
| |
| |
| Triggers | |
| |
| |
| |
| Uses of Triggers | |
| |
| |
| |
| Trigger Performance | |
| |
| |
| Bibliography | |
| |
| |
| Exercises | |
| |
| |
| |
| Communicating With the Outside | |
| |
| |
| |
| Talking to the World | |
| |
| |
| |
| Client-Server Mechanisms | |
| |
| |
| |
| Objects, Application Tools, and Performance | |
| |
| |
| |
| Beware of Small Objects | |
| |
| |
| |
| Beware of Application Development Tools | |
| |
| |
| |
| Tuning the Application Interface | |
| |
| |
| |
| Avoid User Interaction Within a Transaction | |
| |
| |
| |
| Minimize the Number of Round-Trips Between the Application and the Database Server | |
| |
| |
| |
| Retrieve Needed Columns Only | |
| |
| |
| |
| Retrieve Needed Rows Only | |
| |
| |
| |
| Minimize the Number of Query Compilations | |
| |
| |
| |
| Bulk Loading Data | |
| |
| |
| |
| Accessing Multiple Databases | |
| |
| |
| Bibliography | |
| |
| |
| |
| Case Studies From Wall Street | |
| |
| |
| |
| Techniques for Circumventing Superlinearity | |
| |
| |
| |
| Perform Data Integrity Checks at Input Time | |
| |
| |
| |
| Distribution and Heterogeneity | |
| |
| |
| |
| Interoperability with Other Databases | |
| |
| |
| |
| Global Systems | |
| |
| |
| |
| Managing Connections Socialistically in a Distributed Setting | |
| |
| |
| |
| Trading Space for Time in History-Dependent Queries | |
| |
| |
| |
| Chopping to Facilitate Global Trades | |
| |
| |
| |
| Clustering Index Woes | |
| |
| |
| |
| Beware the Optimization | |
| |
| |
| |
| Disaster Planning and Performance | |
| |
| |
| |
| Keeping Nearly Fixed Data Up to Date | |
| |
| |
| |
| Deletions and Foreign Keys | |
| |
| |
| |
| Partitioning Woes: The Hazards of Meaningful Keys | |
| |
| |
| |
| The Problem of Time | |
| |
| |
| |
| Present Value | |
| |
| |
| |
| Regular Time Series and Statistics | |
| |
| |
| |
| Irregular Time Series and Frequency Counting | |
| |
| |
| |
| Bitemporality | |
| |
| |
| |
| What to Do with Time | |
| |
| |
| Bibliography | |
| |
| |
| Exercises | |
| |
| |
| |
| Troubleshooting | |
| |
| |
| |
| |
| Introduction | |
| |
| |
| |
| A Consumption Chain Approach | |
| |
| |
| |
| The Three Questions | |
| |
| |
| |
| How to Gather Information: The Tools | |
| |
| |
| |
| Query Plan Explainers | |
| |
| |
| |
| Performance Monitors | |
| |
| |
| |
| Event Monitors | |
| |
| |
| |
| Now What? | |
| |
| |
| |
| Queries from Hell | |
| |
| |
| |
| Finding "Suspicious" Queries | |
| |
| |
| |
| Analyzing a Query's Access Plan | |
| |
| |
| |
| Profiling a Query Execution | |
| |
| |
| |
| Are DBMS Subsystems Working Satisfactorily? | |
| |
| |
| |
| Disk Subsystem | |
| |
| |
| |
| Buffer (Cache) Manager | |
| |
| |
| |
| Logging Subsystem | |
| |
| |
| |
| Locking Subsystem | |
| |
| |
| |
| Is the DBMS Getting All It Needs? | |
| |
| |
| |
| Checking on CPU | |
| |
| |
| |
| Checking on Disks and Controllers | |
| |
| |
| |
| Checking on Memory | |
| |
| |
| |
| Checking the Network | |
| |
| |
| |
| Conclusion | |
| |
| |
| Bibliography | |
| |
| |
| |
| Tuning E-Commerce Applications | |
| |
| |
| |
| Goal | |
| |
| |
| |
| E-commerce Architecture | |
| |
| |
| |
| Tuning the E-commerce Architecture | |
| |
| |
| |
| Caching | |
| |
| |
| |
| Connection Pooling | |
| |
| |
| |
| Indexing | |
| |
| |
| |
| Case Study: Shop Comparison Portal | |
| |
| |
| |
| Capacity Planning in a Nutshell | |
| |
| |
| |
| Capacity Planning Essentials | |
| |
| |
| |
| What to Purchase | |
| |
| |
| Bibliography | |
| |
| |
| Exercises | |
| |
| |
| |
| Celko on Data Warehouses: Techniques, Successes, and Mistakes | |
| |
| |
| |
| |
| Early History | |
| |
| |
| |
| Forget What the Elders Taught You | |
| |
| |
| |
| Building a Warehouse Is Hard | |
| |
| |
| |
| The Effect on the Bottom Line | |
| |
| |
| |
| Wal-Mart | |
| |
| |
| |
| Supervalu | |
| |
| |
| |
| Harrah's | |
| |
| |
| |
| Data Warehouse Tuning | |
| |
| |
| |
| What's Different About Data Warehouses | |
| |
| |
| |
| Uses of Data Warehouses | |
| |
| |
| |
| Technology for Data Warehousing | |
| |
| |
| |
| Tuning for Customer Relationship Management Systems | |
| |
| |
| |
| Federated Data Warehouse Tuning | |
| |
| |
| |
| Product Selection | |
| |
| |
| Bibliography | |
| |
| |
| Exercises | |
| |
| |
| |
| Real-Time Databases | |
| |
| |
| |
| Overview | |
| |
| |
| |
| Replicated State Machine Approach | |
| |
| |
| |
| Transaction Chopping | |
| |
| |
| |
| Assumptions | |
| |
| |
| |
| Correct Choppings | |
| |
| |
| |
| Finding the Finest Chopping | |
| |
| |
| |
| Optimal Chopping Algorithm | |
| |
| |
| |
| Application to Typical Database Systems | |
| |
| |
| |
| Chopping and Snapshot Isolation | |
| |
| |
| |
| Related Work | |
| |
| |
| Bibliography | |
| |
| |
| |
| Time Series, Especially for Finance | |
| |
| |
| |
| Setting Up a Time Series Database | |
| |
| |
| |
| FAME | |
| |
| |
| |
| S-Plus | |
| |
| |
| |
| SAS | |
| |
| |
| |
| KDB | |
| |
| |
| |
| Oracle 8i Time Series | |
| |
| |
| |
| Features You Want for Time Series | |
| |
| |
| |
| Time Series Data Mining | |
| |
| |
| |
| Understanding Access Plans | |
| |
| |
| |
| Data Access Operators | |
| |
| |
| |
| Query Structure Operators | |
| |
| |
| |
| Auxiliary Operators | |
| |
| |
| Bibliography | |
| |
| |
| |
| Configuration Parameters | |
| |
| |
| |
| Oracle | |
| |
| |
| |
| SQL Server | |
| |
| |
| |
| DB2 UDB | |
| |
| |
| Glossary | |
| |
| |
| Index | |