Oracle SQL tuning 步驟
SQL是的全稱是Structured Query Language(結構化查詢語言 (SQL))。SQL是一個在80年代中期被使用的工業標準資料庫查詢語言。不要把SQL語言與商業化產品如Microsoft SQL server或開源產品MySQL相混淆。所有的使用SQL縮減詞的這些都是SQL標準的一部分。
更多Oracle相關資訊見Oracle 專題頁面 http://www.bkjia.com/topicnews.aspx?tid=12
一、SQL tuning之前的調整
下面這個粗略的方法能夠節省數千小時乏味的SQL tuning，因為一旦調整它將影響數以百計的SQL查詢。記住，你必須優先調整它，否則後
續的最佳化器參數改變或統計資訊可能不會有助於你的SQL調整。
記住，你應當總是優先考慮系統層級的SQL tuning，否則在SQL tuning之後再進行調整可能會使得你先前調整的SQL功虧一簣。
1、最佳化系統核心
首先應當考慮調整磁碟和網路I/O子系統(象RAID，DASD頻寬，網路等)去最小化I/O時間，網路包的大小以及調度頻率。
2、調整最佳化器統計資訊
應當定期收集和儲存最佳化器的統計資訊以便最佳化器根據資料的分布產生最佳的執行計畫。此外，長條圖有助於最佳化表的串連以及為有傾斜的
where 子句謂詞資訊做出正確的訪問決定。
3、調整最佳化器參數
下列最佳化器參數應當被調整
optimizer_mode， optimizer_index_caching， optimizer_index_cost_adj
4、最佳化執行個體
下列執行個體/會話層級參數將影響SQL效能
db_block_size，db_cache_size， and OS parameters (db_file_multiblock_read_count， cpu_count， &c)，
5、使用索引或物化視圖調整SQL訪問負載
Oracle 10g之後可以使用SQL Access advisor來為SQL產生索引或物化視圖的建議。應當總是使用索引來最佳化SQL，特別是基於函數的索引。
Oracle 11g的改進：
Oracle 11g中新增的SQL Performance Analyzer (SPA)是一個從整體上加快SQL調整的新特性。
通過SPA，一旦建立一個負載(稱為SQL tuning set，或者STS)，Oracle將根據不同環境情況，使用複雜的預測模組重複的執行工作負載(使
用迴歸測試方法)，來得到當前負載的最佳SQL執行計畫。使用SPA，我們可以預測一個SQL負載基於系統變化造成的影響，以及預測象參數
調整，系統schema調整，硬體調整，操作調整，Oracle升級之後當前SQL語句的回應時間。更多詳細的細節請參考:Oracle 11g New Feature
當運行環境，Oracle執行個體以及對象被調整之後，更多地關注則是資料庫中的效能影響最大的單個單個的SQL語句。下面將針對單個SQL調整給
出一些常規建議以提高 Oracle 效能。
二、Oracle SQL tuning的目標
Oracle SQL tuning是一個複雜的課題。Oracle Tuning: The Definitive Reference 這整本書描述了關於SQL tuning的細節。儘管如此，
為了提高系統系能，Oracle DBA應當遵從下面一些總的指導原則。
1、SQL tuning 目標
是以最小的資料庫訪問次數提取更多地資料行來產生最佳的執行計畫(儘可能最小化物理讀(PIO)與邏輯讀(LIO)。
指導原則
移除不必要的大型全表掃描
大型表的全表掃描將產生龐大的系統I/O且使得整個資料庫效能下降。最佳化專家首先會評估當前SQL查詢所返回的行數。最常見的辦
法是為走全表掃描的大表增加索引。B樹索引，位元影像索引，以及基於函數的索引等能夠避免全表掃描。有時候，對一些不必要的全表掃
描通過添加提示的方法來避免全表掃描。
緩衝小表全表掃描
有時候全表掃描是最快的訪問方式，管理員應當確保專用的資料緩衝區(keep buffer cache，nk buffer cache)對這些表可用。在
Oracle 8 以後小表可以被強制緩衝到 keep 池。
使用最佳索引
Oracle 訪問對象有時候會有一個以上的索引選擇。因此應當檢查當前查詢對象上的每一個索引以確保Oracle使用了最佳索引。
物化彙總運算以靜態化表統計
Oracle 10g的特性之一SQL Access advisor 會給出索引建議以及物化視圖的建議。物化視圖可以預串連表和預摘要表資料。(譯者
按，即Oracle可以根據特定的更新方式來提前更新物化視圖中的資料，而在查詢時僅僅查詢物化視圖即可得到最終所需的統計資料
結果。物化視圖實際上是一張實體表)
以上這些概括了SQL tuning的目標。然而看是簡單，調整起來並不容易，因為這需要對Oracle SQL內部有一個徹底的瞭解。接下來讓我們從
整體上來認識 Oracle SQL 最佳化。
2、Oracle SQL 最佳化器
Oracle DBA首先要查看的是當前資料庫預設的最佳化器模式。Oracle初始化參數提供很多基於成本最佳化的最佳化器模式以及之前廢棄的基於規則
的最佳化器模式(或hint)供選擇。基於成本的最佳化器主要依賴於表對象使用analyze命令收集的統計資訊。Oracle根據表上的統計資訊得以決定
並為當前的SQL產生最高效的執行計畫。需要注意的是在一些場合基於成本最佳化器可能會做出不正確的決定。基於成本的最佳化器在不斷的改進，
但是依然有很多場合使用基於規則的最佳化器能夠使得查詢更高效。
在Oracle 10g之前，Oracle 預設的最佳化器模式是CHOOSE模式。在該模式下，如果表對象上缺乏統計資訊則此時Oracle使用基於規則的最佳化
器；如果統計資訊存在則使用基於成本的最佳化器。使用CHOOSE模式存在的隱患即是對一些複雜得查詢有些對象上有統計資訊，而另一些對象
缺乏統計資訊。
在Oracle 10g開始，預設的最佳化器模式是 ALL_ROWS，這有助於全表掃描優於索引掃描。ALL_ROWS最佳化器模式被設計成最小化計算資源且有
助於全表掃描。索引掃描(first_rows_n)增加了額外的I/O開銷。但是他們能更快地返回資料。
因此，大多數OLTP系統選擇first_rows，first_rows_100 或者 first_rows_10以使得Oracle使用索引掃描來減少讀塊數量。
注意：從Oracle 9i R2開始，Oracle 效能調整指導指出了first_rows 最佳化器模式已經被廢棄，且使用first_rows_n代替
當僅有一些表包含CBO統計資訊，而另一些缺乏統計資訊時，Oracle使用基於成本的最佳化模式來預估其他表在運行時的統計資訊(即動態採樣
)，這在很大程度上影響單個查詢效能下降。
總之，Oracle 資料庫管理員應當總是將嘗試改變最佳化器模式作為SQL tuning的第一步。Oracle SQL tuning的首要原則是避免可怕的全表掃
描。一個特性之一是一個非高效的SQL語句為提高查詢效能使用所有的索引此仍然為一個失敗的SQL語句。
當然，有些時候使用全表掃描是合適的，尤其是在做彙總操作象sum，avg等操作，因為為了獲得結果，表上的絕大部分資料行必須被讀入到
緩衝。SQL tuning 高手應當合理的評估每一個全表掃描並要核實使用索引能否提高效能。
在大多數Oracle 系統，SQL語句檢索的僅僅是表上資料一個子集。Oracle 最佳化器會檢查使用索引是否會導致更多的I/O。然而，如果構建了
一個低效的查詢，基於成本的最佳化器難以選擇最佳的資料訪問路徑，轉而傾向於使用全表掃描。故Oracle資料庫管理員應當總是審查那些走
全表掃描的SQL語句。
更多有關全表掃描的問題，以及選擇正確的最佳化模式請 ："Oracle Tuning: The Definitive Reference"
