SQL Tuning (e-book)

SQL Tuning (e-book) - Opis

A poorly performing database application not only costs users time, but also has an impact on other applications running on the same computer or the same network. SQL Tuning provides an essential next step for SQL developers and database administrators who want to extend their SQL tuning expertise and get the most from their database applications.There are two basic issues to focus on when tuning SQL: how to find and interpret the execution plan of an SQL statement and how to change SQL to get a specific alternate execution plan. SQL Tuning provides answers to these questions and addresses a third issue that's even more important: how to find the optimal execution plan for the query to use.Author Dan Tow outlines a timesaving method he's developed for finding the optimum execution plan--rapidly and systematically--regardless of the complexity of the SQL or the database platform being used. You'll learn how to understand and control SQL execution plans and how to diagram SQL queries to deduce the best execution plan for a query. Key chapters in the book include exercises to reinforce the concepts you've learned. SQL Tuning concludes by addressing special concerns and unique solutions to "unsolvable problems."Whether you are a programmer who develops SQL-based applications or a database administrator or other who troubleshoots poorly tuned applications, SQL Tuning will arm you with a reliable and deterministic method for tuning your SQL queries to gain optimal performance. Spis treści:
SQL Tuning
Dedication
SPECIAL OFFER: Upgrade this ebook with OReilly
A (...) więcej Note Regarding Supplemental Files
Foreword
Preface
Objectives of This Book
Audience for This Book
Structure of This Book
Conventions Used in This Book
Comments and Questions
Acknowledgments
1. Introduction
1.1. Why Tune SQL?
1.2. Who Should Tune SQL?
1.3. How This Book Can Help
1.4. A Bonus
1.5. Outside-the-Box Solutions
2. Data-Access Basics
2.1. Caching in the Database
2.2. Tables
2.2.1. Continuous Growth
2.2.2. Purge Eldest
2.2.3. Purge, Not by Age
2.2.4. Complete Purge and Regrowth
2.3. Indexes
2.3.1. B-Tree Indexes
2.3.2. Index Costs
2.4. Uncommon Database Objects
2.4.1. Index-Organized Tables
2.4.2. Single-Table Clusters
2.4.3. Multitable Clusters
2.4.4. Partitioned Tables
2.4.5. Bit-Mapped Indexes
2.5. Single-Table Access Paths
2.5.1. Full Table Scans
2.5.2. Indexed Table Access
2.5.3. Choosing Between a Full Table Scan and Indexed Access
2.6. Calculating Selectivity
2.6.1. Filter Selectivity
2.6.2. Index Range-Condition Selectivity
2.6.3. Selectivity on Table Rows Reached from the Index
2.6.4. Combining Indexes
2.7. Joins
2.7.1. Join Types
2.7.1.1. Inner joins
2.7.1.2. Outer joins
2.7.2. Join Execution Methods
2.7.2.1. Nested-loops joins
2.7.2.2. Hash joins
2.7.2.3. Sort-merge joins
2.7.2.4. Join methods summary
3. Viewing and Interpreting Execution Plans
3.1. Reading Oracle Execution Plans
3.1.1. Prerequisites
3.1.2. The Underlying Process of Displaying Execution Plans
3.1.3. The Practical Process of Displaying Execution Plans
3.1.4. Robust Execution Plans
3.1.4.1. How to interpret the plan
3.1.4.2. Narrative interpretation of the execution plan
3.1.5. Nonrobust Execution Plans
3.1.6. Complex Execution Plans
3.2. Reading DB2 Execution Plans
3.2.1. Prerequisites
3.2.2. The Underlying Process of Displaying Execution Plans
3.2.3. The Practical Process of Displaying Execution Plans
3.2.4. Robust Execution Plans
3.2.4.1. How to interpret the plan
3.2.4.2. Narrative interpretation of the execution plan
3.2.5. Nonrobust Execution Plans
3.2.6. Complex Execution Plans
3.3. Reading SQL Server Execution Plans
3.3.1. Displaying Execution Plans
3.3.1.1. Displaying execution plans graphically
3.3.1.2. Displaying execution plans textually
3.3.2. How to Interpret the Plan
3.3.3. Narrative Interpretation of the Execution Plan
3.3.4. Interpreting Nonrobust Execution Plans
3.3.5. Complex Execution Plans
4. Controlling Execution Plans
4.1. Universal Techniques for Controlling Plans
4.1.1. Enabling Use of the Index You Want
4.1.2. Preventing Use of the Wrong Indexes
4.1.3. Enabling the Join Order You Want
4.1.3.1. Outer joins
4.1.3.2. Missing redundant join conditions
4.1.4. Preventing Join Orders You Do Not Want
4.1.5. Forcing Execution Order for Outer Queries and Subqueries
4.1.6. Providing the Cost-Based Optimizer with Good Data
4.1.7. Fooling the Cost-Based Optimizer with Incorrect Data
4.2. Controlling Plans on Oracle
4.2.1. Controlling the Choice of Oracle Optimizer
4.2.2. Controlling Oracle Rule-Based Execution Plans
4.2.3. Controlling Oracle Cost-Based Execution Plans
4.2.3.1. Oracle cost-based optimizer prerequisites
4.2.3.2. General hint syntax
4.2.3.3. Approaches to tuning with hints
4.2.3.4. Table-access hints
4.2.3.5. Execution-order hints
4.2.3.6. Join-method hints
4.2.3.7. Example
4.3. Controlling Plans on DB2
4.3.1. DB2 Optimization Prerequisites
4.3.2. Choosing the Optimization Level
4.3.3. Modifying the Query
4.3.3.1. Place inner joins first in your FROM clause
4.3.3.2. Prevent too many outer joins from parsing at once
4.3.3.3. Let DB2 know when to optimize the cost of reading just the first few rows
4.4. Controlling Plans on SQL Server
4.4.1. SQL Server Optimization Prerequisites
4.4.2. Modifying the Query
4.4.3. Hint Examples
4.4.4. Using FORCEPLAN
5. Diagramming Simple SQL Queries
5.1. Why a New Method?
5.2. Full Query Diagrams
5.2.1. Information Included in Query Diagrams
5.2.1.1. Nodes
5.2.1.2. Links
5.2.1.3. Underlined numbers
5.2.1.4. Nonunderlined numbers
5.2.2. What Query Diagrams Leave Out
5.2.2.1. Select lists
5.2.2.2. Ordering and aggregation
5.2.2.3. Table names
5.2.2.4. Detailed join conditions
5.2.2.5. Absolute table sizes (as opposed to relative sizes)
5.2.2.6. Filter condition details
5.2.3. When Query Diagrams Help the Most
5.2.4. Conceptual Demonstration of Query Diagrams in Use
5.2.5. Creating Query Diagrams
5.2.6. A More Complex Example
5.2.6.1. Diagram joins to the first focus
5.2.6.2. Diagram joins from the first focus
5.2.6.3. Change focus and repeat
5.2.6.4. Compute filter and join ratios
5.2.7. Shortcuts
5.3. Interpreting Query Diagrams
5.4. Simplified Query Diagrams
5.5. Exercises (See Chapter 5 Exercise Solutions for the solution to each exercise.)
6. Deducing the Best Execution Plan
6.1. Robust Execution Plans
6.2. Standard Heuristic Join Order
6.3. Simple Examples
6.3.1. Join Order for an Eight-Way Join
6.3.2. Completing the Solution for an Eight-Way Join
6.3.3. A Complex 17-Way Join
6.4. A Special Case
6.4.1. The Oracle Solution
6.4.2. Solving the Special Case Outside of Oracle
6.5. A Complex Example
6.6. Special Rules for Special Cases
6.6.1. Safe Cartesian Products
6.6.2. Detail Join Ratios Close to 1.0
6.6.3. Join Ratios Less than 1.0
6.6.3.1. Rules for join ratios less than 1.0
6.6.3.2. Detail join ratios less than 1.0
6.6.3.3. Optimizing detail join ratios less than 1.0 with the rules
6.6.3.4. Master join ratios less than 1.0
6.6.4. Close Filter Ratios
6.6.5. Cases to Consider Hash Joins
6.7. Exercise (See Chapter 6 Exercise Solution for the solution to the exercise.)
7. Diagramming and Tuning Complex SQL Queries
7.1. Abnormal Join Diagrams
7.1.1. Cyclic Join Graphs
7.1.1.1. Case 1: Two one-to-one master tables share the same detail table
7.1.1.2. Case 2: Master-detail tables each hold copies of a foreign key that points to the same third tables primary key
7.1.1.3. Case 3: Two-node filter (nonunique on both ends) between nodes is already linked through normal joins
7.1.1.4. Case 4: Multipart join from two foreign keys is spread over two tables to a multipart primary key
7.1.1.5. Cyclic join summary
7.1.2. Disconnected Query Diagrams
7.1.3. Query Diagrams with Multiple Roots
7.1.3.1. Case 1: Missing join conditions
7.1.3.2. Case 2: Breaking the Cartesian product into multiple queries
7.1.3.3. Case 3: Root detail tables that are usually no more than one-to-one
7.1.3.4. Case 4: Converting an existence check to an explicit subquery
7.1.4. Joins with No Primary Key
7.1.5. One-to-One Joins
7.1.5.1. One-to-one join to a subset table
7.1.5.2. Exact one-to-one joins
7.1.5.3. One-to-one join to a much smaller subset
7.1.5.4. One-to-one joins with hidden join filters in both directions
7.1.5.5. Conventions to display one-to-one joins
7.1.6. Outer Joins
7.1.6.1. Filtered outer joins
7.1.6.2. Outer joins leading to inner joins
7.1.6.3. Outer joins pointing toward the detail table
7.1.6.4. Outer joins to a detail table with a filter
7.2. Queries with Subqueries
7.2.1. Diagramming Queries with Subqueries
7.2.1.1. Diagramming EXISTS subqueries
7.2.1.2. Diagramming NOT EXISTS subqueries
7.2.2. Tuning Queries with Subqueries
7.3. Queries with Views
7.3.1. Diagramming View-Using Queries
7.3.2. Tuning Queries with Views
7.3.2.1. Outer joins to views
7.3.2.2. Redundant reads in view-using queries
7.3.2.3. Unnecessary nodes and joins
7.4. Queries with Set Operations
7.5. Exercise (See Chapter 7 Exercise Solution for the solution to the exercise.)
8. Why the Diagramming Method Works
8.1. The Case for Nested Loops
8.2. Choosing the Driving Table
8.3. Choosing the Next Table to Join
8.3.1. Accounting for Unequal Per-Row Costs
8.3.2. Accounting for Benefits from Later Joins
8.3.3. When to Choose Early Joins to Upstream Nodes
8.4. Summary
9. Special Cases
9.1. Outer Joins
9.1.1. Steps for Normal Outer Join Order Optimization
9.1.2. Example
9.2. Merged Join and Filter Indexes
9.3. Missing Indexes
9.4. Unfiltered Joins
9.5. Unsolvable Problems
10. Outside-the-Box Solutions to Seemingly Unsolvable Problems
10.1. When Very Fast Is Not Fast Enough
10.1.1. Caching to Avoid Repeated Queries
10.1.2. Consolidated Queries
10.1.3. Merging Repeated Queries into a Preexisting Query
10.2. Queries that Return Data from Too Many Rows
10.2.1. Large Online Queries
10.2.2. Large Batch Reports
10.2.2.1. Reasons for large reports
10.2.2.2. Ways reports are triggered
10.2.2.3. Reasons batch performance is a concern
10.2.2.4. Report information types
10.2.2.5. Solutions
10.2.3. Aggregations of Many Details
10.2.4. Middleware Processes Handling Too Many Rows
10.3. Tuned Queries that Return Few Rows, Slowly
10.3.1. Why Queries Sometimes Read Many Rows to Return Few
10.3.2. Optimizing Queries with Distributed Filters
A. Exercise Solutions
A.1. Chapter 5 Exercise Solutions
A.1.1. Exercise 1
A.1.2. Exercise 2
A.1.3. Exercise 3
A.1.4. Exercise 4
A.1.5. Exercise 5
A.1.6. Exercise 6
A.2. Chapter 6 Exercise Solution
A.3. Chapter 7 Exercise Solution
B. The Full Process, End to End
B.1. Reducing the Query to a Query Diagram
B.1.1. Creating the Query Skeleton
B.1.2. Creating a Simplified Query Diagram
B.1.3. Creating a Full Query Diagram
B.2. Solving the Query Diagram
B.3. Checking the Execution Plans
B.3.1. Getting the Oracle Execution Plan
B.3.2. Getting the DB2 Execution Plan
B.3.3. Getting the SQL Server Execution Plan
B.4. Altering the Database to Enable the Best Plan
B.5. Altering the SQL to Enable the Best Plan
B.6. Altering the Application
B.7. Putting the Example in Perspective
Glossary
Index
About the Author
Colophon
SPECIAL OFFER: Upgrade this ebook with OReilly
Copyright mniej