SQL Tuning

SQL Tuning

by Dan Tow
Released November 2003
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596005733

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

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.

Publisher resources

View/Submit Errata

Table of contents

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

Product information

  • Title: SQL Tuning
  • Author(s): Dan Tow
  • Release date: November 2003
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596005733