Oracle SQL Tuning Pocket Reference

Oracle SQL Tuning Pocket Reference

by Mark Gurry
Released January 2002
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596002688

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

One of the most important challenges faced by Oracle database administrators and Oracle developers is the need to tune SQL statements so that they execute efficiently. Poorly tuned SQL statements are one of the leading causes of substandard database performance and poor response time. SQL statements that perform poorly result in frustration for users, and can even prevent a company from serving its customers in a timely manner.

In this book, Mark Gurry shares his in-depth knowledge of Oracle's SQL statement optimizers. Mark's knowledge is the result of many hard-fought tuning battles during his many years of providing Oracle tuning services to clients. Mark provides insights into the workings of the rule-based optimizer that go well beyond what the rules tell you. Mark also provides solutions to many common problems that occur with both the rule-based and cost-based optimizers.

In addition to the specific problem/solution scenarios for the optimizers, Mark provides a number of handy SQL tuning tips. He discusses the various optimizer hints, telling you when they can be used to good effect. Finally, Mark discusses the use of the DBMS_STATS package to manage database statistics, and the use of outlines to specify execution plans for SQL statements in third-party applications that you can't otherwise modify.

Publisher resources

View/Submit Errata

Table of contents

  1. 1. Oracle SQL TuningPocket Reference
    1. 1.1. Introduction
      1. 1.1.1. Acknowledgments
      2. 1.1.2. Caveats
      3. 1.1.3. Conventions
      4. 1.1.4. What’s New in Oracle9i
    2. 1.2. The SQL Optimizers
      1. 1.2.1. Understanding the Rule-Based Optimizer
        1. 1.2.1.1. What the RBO rules don’t tell you #1
        2. 1.2.1.2. What the RBO rules don’t tell you #2
        3. 1.2.1.3. What the RBO rules don’t tell you #3
        4. 1.2.1.4. What the RBO rules don’t tell you #4
        5. 1.2.1.5. What the RBO rules don’t tell you #5
        6. 1.2.1.6. What the RBO rules don’t tell you #6
        7. 1.2.1.7. What the RBO rules don’t tell you #7
      2. 1.2.2. Understanding the Cost-Based Optimizer
        1. 1.2.2.1. ANALYZE command
        2. 1.2.2.2. Tuning prior to releasing to production
        3. 1.2.2.3. Inner workings of the cost-based optimizer
        4. 1.2.2.4. EXPLAIN PLAN for the cost-based optimizer
      3. 1.2.3. Some Common Optimizer Misconceptions
      4. 1.2.4. Which Optimizer to Use?
    3. 1.3. Rule-Based Optimizer Problems and Solutions
      1. 1.3.1. Problem 1: Incorrect Driving Table
      2. 1.3.2. Problem 2: Incorrect Index
      3. 1.3.3. Problem 3: Incorrect Driving Index
      4. 1.3.4. Problem 4: Using the ORDER BY Indexand not the WHERE Index
    4. 1.4. Cost-Based Optimizer Problems and Solutions
      1. 1.4.1. Problem 1: The Skewness Problem
      2. 1.4.2. Problem 2: Analyzing with Wrong Data
        1. 1.4.2.1. How to check the last analyzed date
        2. 1.4.2.2. When to analyze
      3. 1.4.3. Problem 3: Mixing the Optimizers in Joins
      4. 1.4.4. Problem 4: Choosing an Inferior Index
      5. 1.4.5. Problem 5: Joining Too Many Tables
      6. 1.4.6. Problem 6: Incorrect INIT.ORA Parameter Settings
    5. 1.5. Problems Common to Rule and Cost with Solutions
      1. 1.5.1. Problem 1: Statement Not Written for Indexes
      2. 1.5.2. Problem 2: Indexes Are Missing or Inappropriate
        1. 1.5.2.1. Indexing versus full table scans
        2. 1.5.2.2. Adding columns to indexes
        3. 1.5.2.3. Should I index small tables?
      3. 1.5.3. Problem 3: Use of Single-Column Index Merge
      4. 1.5.4. Problem 4: Misuse of Nested Loop, Sort Merge, or Hash Join
      5. 1.5.5. Problem 5: Misuse of IN, EXISTS, NOT IN, NOT EXISTS, or Table Joins
        1. 1.5.5.1. When a join outperforms a subquery
        2. 1.5.5.2. Which is faster, IN or EXISTS?
      6. 1.5.6. Problem 6: Unnecessary Sorts
        1. 1.5.6.1. Consider UNION ALL in place of UNION
        2. 1.5.6.2. Consider using an index to avoid a sort
      7. 1.5.7. Problem 7: Too Many Indexes on a Table
      8. 1.5.8. Problem 8: Use of OR Instead of UNION
      9. 1.5.9. Problem 9: Tables and Indexes with Many Deletes
      10. 1.5.10. Other Problems: Heavy Usage of Views
      11. 1.5.11. Other Problems: Joining Too Many Tables
    6. 1.6. Handy SQL Tuning Tips
      1. 1.6.1. Identify Bad SQL
      2. 1.6.2. Identify Long-Running SQL Statements
      3. 1.6.3. Use DECODE for IF/ELSE Selection
      4. 1.6.4. Encourage Bind Variables
    7. 1.7. Using SQL Hints
      1. 1.7.1. When Are Hints Ignored?
      2. 1.7.2. Using Hints in Views
      3. 1.7.3. Available Hints
    8. 1.8. Using DBMS_STATS to Manage Statistics
      1. 1.8.1. Using DBMS_STATS to Analyze Faster
      2. 1.8.2. Copying Statistics Using DBMS_STATS
      3. 1.8.3. Manipulating Statistics Using DBMS_STATS
      4. 1.8.4. Reverting to Previous Statistics
    9. 1.9. Using Outlines for Consistent Execution Plans
      1. 1.9.1. Recording Outlines
        1. 1.9.1.1. Recording an outline for a SQL statement
        2. 1.9.1.2. Recording outlines for all of a session’s SQL
        3. 1.9.1.3. Recording outlines for the whole system
      2. 1.9.2. Enabling Outlines
        1. 1.9.2.1. Enabling outlines for a session
        2. 1.9.2.2. Enabling outlines for the whole system
      3. 1.9.3. Managing Outlines
        1. 1.9.3.1. Viewing your outlines
        2. 1.9.3.2. Transferring outlines between databases
        3. 1.9.3.3. Dealing with literals
  2. Index
  3. About the Author
  4. Copyright

Product information

  • Title: Oracle SQL Tuning Pocket Reference
  • Author(s): Mark Gurry
  • Release date: January 2002
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596002688