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  

Oracle資料庫管理員和開發人員面臨的最重要挑戰之一是調整SQL語句以實現高效執行。調整不良的SQL語句是導致資料庫性能不佳和響應時間長的主要原因之一。執行效能不佳的SQL語句會給用戶帶來挫折感，甚至可能阻礙公司及時為客戶提供服務。

在這本書中，Mark Gurry分享了他對Oracle SQL語句優化器的深入了解。這些知識是Mark多年來為客戶提供Oracle調優服務時通過多次激烈的調優戰鬥所積累的。Mark提供了超越規則的優化器運作的深入見解。他還提供了解決規則優化器和成本優化器常見問題的解決方案。

除了針對優化器的具體問題/解決方案場景外，Mark還提供了一些實用的SQL調優技巧。他討論了各種優化器提示，告訴您何時可以有效使用它們。最後，Mark討論了使用DBMS_STATS套件管理資料庫統計信息以及使用大綱為無法修改的第三方應用程序中的SQL語句指定執行計劃的方法。

目錄：

- 引言
- SQL優化器
- 理解基於規則的優化器
- 理解基於成本的優化器
- 一些常見的優化器誤解
- 如何選擇優化器

- 基於規則的優化器問題和解決方案
- 問題1：錯誤的驅動表
- 問題2：錯誤的索引
- 問題3：錯誤的驅動索引
- 問題4：使用ORDER BY索引而不是WHERE索引

- 基於成本的優化器問題和解決方案
- 問題1：偏斜問題
- 問題2：使用錯誤的數據進行分析
- 問題3：在連接中混合使用優化器
- 問題4：選擇次優的索引
- 問題5：連接過多的表
- 問題6：不正確的INIT.ORA參數設置

- 基於規則和成本的共同問題及解決方案
- 問題1：未為索引編寫語句
- 問題2：缺少或不適用的索引
- 問題3：使用單列索引合併
- 問題4：錯誤使用嵌套循環、排序合併或哈希連接
- 問題5：錯誤使用IN、EXISTS、NOT IN、NOT EXISTS或表連接
- 問題6：不必要的排序
- 問題7：表上有太多索引
- 問題8：使用OR而不是UNION
- 問題9：具有大量刪除的表和索引
- 其他問題：大量使用視圖