Oracle SQL Tuning Pocket Reference

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.

Table of Contents

      Introduction  

      The SQL Optimizers  

                Understanding the Rule-Based Optimizer  

                Understanding the Cost-Based Optimizer  

                Some Common Optimizer Misconceptions  

                Which Optimizer to Use?  

      Rule-Based Optimizer Problems and Solutions  

                Problem 1: Incorrect Driving Table  

                Problem 2: Incorrect Index  

                Problem 3: Incorrect Driving Index  

                Problem 4: Using the ORDER BY Index and notthe WHERE Index  

      Cost-Based Optimizer Problems and Solutions  

                Problem 1: The Skewness Problem  

                Problem 2: Analyzing with Wrong Data  

                Problem 3: Mixing the Optimizers in Joins  

                Problem 4: Choosing an Inferior Index  

                Problem 5: Joining Too Many Tables  

                Problem 6: Incorrect INIT.ORA Parameter Settings  

      Problems Common to Rule and Cost with Solutions  

                Problem 1: Statement Not Written for Indexes  

                Problem 2: Indexes Are Missing or Inappropriate  

                Problem 3: Use of Single-Column Index Merge  

                Problem 4: Misuse of Nested Loop, Sort Merge,or Hash Join  

                Problem 5: Misuse of IN, EXISTS, NOT IN, NOT EXISTS,or Table Joins  

                Problem 6: Unnecessary Sorts  

                Problem 7: Too Many Indexes on a Table  

                Problem 8: Use of OR Instead of UNION  

                Problem 9: Tables and Indexes with Many Deletes  

                Other Problems: Heavy Usage of Views  

                Other Problems: Joining Too Many Tables  

      Handy SQL Tuning Tips  

                Identify Bad SQL  

                Identify Long-Running SQL Statements  

                Use DECODE for IF/ELSE Selection  

                Encourage Bind Variables  

      Using SQL Hints  

                When Are Hints Ignored?  

                Using Hints in Views  

                Available Hints  

      Using DBMS_STATS to Manage Statistics  

                Using DBMS_STATS to Analyze Faster  

                Copying Statistics Using DBMS_STATS  

                Manipulating Statistics Using DBMS_STATS  

                Reverting to Previous Statistics  

      Using Outlines for Consistent Execution Plans  

                Recording Outlines  

                Enabling Outlines  

                Managing Outlines