1.1. Introduction1.1.1. Acknowledgments1.1.2. Caveats1.1.3. Conventions1.1.4. What’s New in Oracle9i1.2. The SQL Optimizers1.2.1. Understanding the Rule-Based Optimizer1.2.1.1. What the RBO rules don’t tell you #11.2.1.2. What the RBO rules don’t tell you #21.2.1.3. What the RBO rules don’t tell you #31.2.1.4. What the RBO rules don’t tell you #41.2.1.5. What the RBO rules don’t tell you #51.2.1.6. What the RBO rules don’t tell you #61.2.1.7. What the RBO rules don’t tell you #71.2.2. Understanding the Cost-Based Optimizer1.2.2.1. ANALYZE command1.2.2.2. Tuning prior to releasing to production1.2.2.3. Inner workings of the cost-based optimizer1.2.2.4. EXPLAIN PLAN for the cost-based optimizer1.2.3. Some Common Optimizer Misconceptions1.2.4. Which Optimizer to Use?1.3. Rule-Based Optimizer Problems and Solutions1.3.1. Problem 1: Incorrect Driving Table1.3.2. Problem 2: Incorrect Index1.3.3. Problem 3: Incorrect Driving Index1.3.4. Problem 4: Using the ORDER BY Indexand not the WHERE Index1.4. Cost-Based Optimizer Problems and Solutions1.4.1. Problem 1: The Skewness Problem1.4.2. Problem 2: Analyzing with Wrong Data 1.4.2.1. How to check the last analyzed date1.4.2.2. When to analyze1.4.3. Problem 3: Mixing the Optimizers in Joins1.4.4. Problem 4: Choosing an Inferior Index1.4.5. Problem 5: Joining Too Many Tables1.4.6. Problem 6: Incorrect INIT.ORA Parameter Settings1.5. Problems Common to Rule and Cost with Solutions1.5.1. Problem 1: Statement Not Written for Indexes1.5.2. Problem 2: Indexes Are Missing or Inappropriate1.5.2.1. Indexing versus full table scans1.5.2.2. Adding columns to indexes1.5.2.3. Should I index small tables?1.5.3. Problem 3: Use of Single-Column Index Merge1.5.4. Problem 4: Misuse of Nested Loop, Sort Merge, or Hash Join1.5.5. Problem 5: Misuse of IN, EXISTS, NOT IN, NOT EXISTS, or Table Joins1.5.5.1. When a join outperforms a subquery1.5.5.2. Which is faster, IN or EXISTS?1.5.6. Problem 6: Unnecessary Sorts1.5.6.1. Consider UNION ALL in place of UNION1.5.6.2. Consider using an index to avoid a sort1.5.7. Problem 7: Too Many Indexes on a Table1.5.8. Problem 8: Use of OR Instead of UNION1.5.9. Problem 9: Tables and Indexes with Many Deletes1.5.10. Other Problems: Heavy Usage of Views1.5.11. Other Problems: Joining Too Many Tables1.6. Handy SQL Tuning Tips1.6.1. Identify Bad SQL1.6.2. Identify Long-Running SQL Statements1.6.3. Use DECODE for IF/ELSE Selection1.6.4. Encourage Bind Variables 1.7. Using SQL Hints1.7.1. When Are Hints Ignored?1.7.2. Using Hints in Views1.7.3. Available Hints1.8. Using DBMS_STATS to Manage Statistics1.8.1. Using DBMS_STATS to Analyze Faster1.8.2. Copying Statistics Using DBMS_STATS1.8.3. Manipulating Statistics Using DBMS_STATS1.8.4. Reverting to Previous Statistics1.9. Using Outlines for Consistent Execution Plans1.9.1. Recording Outlines1.9.1.1. Recording an outline for a SQL statement1.9.1.2. Recording outlines for all of a session’s SQL1.9.1.3. Recording outlines for the whole system 1.9.2. Enabling Outlines1.9.2.1. Enabling outlines for a session1.9.2.2. Enabling outlines for the whole system1.9.3. Managing Outlines1.9.3.1. Viewing your outlines1.9.3.2. Transferring outlines between databases 1.9.3.3. Dealing with literals