Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities

Written by a Senior Database Administrator who has worked with the Oracle RDBMS for thirty years, this is a book which teaches the skill of SQL Tuning for the Oracle Database. Not a list of one-off tricks or tips, nor a glossing over of topics; this book offers an in-depth process covering discovery, analysis, and problem resolution. Learn the science behind SQL Tuning.

• Learn and apply the FILTERED ROWS PERCENTAGE Cardinality based method of tuning
• Determine a query's Driving Table and Join Order
• Construct Query Diagrams, Data Models, and Join Trees
• Build and use Count / Filter / and Reconstruction Queries
• Identify Waste in a Query Execution Plan
• Zero in on Cardinality Divergence using Estimated vs. Actuals
• Use the ACCESS / FILTER / COVERAGE strategy to build indexes for Problem Queries
• Exploit THE 2% RULE in analyzing Access method and Join method
• Classify queries as Precision Style or Warehouse Style
• Understand Hash Join mechanics and make Hash Joins go faster
• Make HINTS work as Detection Tools rather than clubs
• Avoid early Database Design flaws
• Manage Statistics and deal with common Statistics problems
• (NDV, Uniform Distribution, Independence, Dynamic Sampling)
• (Staleness, Skew, Dependence, Defaulting, Out-Of-Bounds, Transiency, Bloat)
• Perfect your Question Based Analysis Technique
• and more

Included are: a special chapter for EXADATA, a LAB which demonstrates the cardinality based process of SQL Tuning, and twenty three magical SQL scripts that make the process of SQL tuning easy to do. Learn the skill of SQL Tuning as taught by an expert who does it for a living, and become the go-to specialist in your company.

• Chapter 1: DRIVING TABLE and JOIN ORDER
• Chapter 2: Ways to Use a Query Execution Plan
• Chapter 3: The Best Indexes for a Query
• Chapter 4: JOINS
• Chapter 5: HINTS
• Chapter 6: BASICS
• Chapter 7: ROW COUNTS and RUN TIMES
• Chapter 8: EXADATA
• LAB: Reverse Engineering the QEP
• Appendix: Know Your Scripts
• Scripts for analyzing queries and plans
• Scripts for examining an active database
• Scripts for looking at metadata
• showplan
• showplanshort
• showplanconstraints
• showplancountqueries
• showplandatamodel
• showplandrivingtable
• showplanfilterqueries
• showplanfrpspreadsheetcode
• showplanindexes
• showplannumrows
• showplanquerydiagram
• showplantables
• showplantablesunique
• loadplanfromcache
• loadplanfromhist
• showtopcpu
• showowner
• showindexes
• showconstraints
• showcolstats
• showhistograms
• showallscanrates
• showallworkareas

It's all about the Cardinalities

這本書由一位有三十年Oracle RDBMS工作經驗的高級資料庫管理員撰寫，教授Oracle資料庫的SQL調校技巧。這不是一本提供一次性技巧或提示的清單，也不是對主題的概述；這本書提供了一個深入的過程，涵蓋了發現、分析和問題解決。學習SQL調校背後的科學原理。

學習並應用基於「過濾行百分比基數」的調校方法。
確定查詢的「驅動表」和「連接順序」。
建立查詢圖表、資料模型和連接樹。
建立並使用計數/過濾/重建查詢。
識別查詢執行計劃中的浪費。
使用「存取/過濾/覆蓋」策略為問題查詢建立索引。
利用「2%規則」分析存取方法和連接方法。
將查詢分類為「精確風格」或「倉儲風格」。
了解哈希連接的機制，使哈希連接更快。
使HINTS成為檢測工具而不是強制工具。
避免早期的資料庫設計缺陷。
管理統計資料並處理常見的統計問題（如NDV、均勻分佈、獨立性、動態抽樣、過時、偏斜、相依性、默認值、越界、瞬態、膨脹）。
完善你的基於問題的分析技巧。
以及更多。

書中還包括：一個專門為EXADATA設計的章節，一個實驗室示範基於基數的SQL調校過程，以及二十三個神奇的SQL腳本，使SQL調校過程變得容易。學習由一位專業從事SQL調校的專家所教授的SQL調校技巧，成為公司中的專家。

章節包括：
第一章：驅動表和連接順序
第二章：使用查詢執行計劃的方法
第三章：查詢的最佳索引
第四章：連接
第五章：提示
第六章：基礎知識
第七章：行數和執行時間
第八章：EXADATA
實驗室：反向工程QEP
附錄：了解你的腳本
- 用於分析查詢和計劃的腳本
- 用於檢查活動資料庫的腳本
- 用於查看元數據的腳本
- showplan
- showplanshort
- showplanconstraints
- showplancountqueries
- showplandatamodel
- showplandrivingtable
- showplanfilterqueries
- showplanfrpspreadsheetcode
- showplanindexes
- showplannumrows
- showplanquerydiagram
- showplantables
- showplantablesunique
- loadplanfromcache
- loadplanfromhist
- showtopcpu
- showowner
- showindexes
- showconstraints
- showcolstats
- showhistograms
- showallscanrates
- showallworkareas

這本書的核心在於基數。