2014-12-23 Created By BaoXinjian
一、摘要
SQL是的全稱是Structured Query Language(結構化查詢語言)。SQL是一個在80年代中期被使用的工業标準資料庫查詢語言。
SQL性能優化是Oracle PLSQL調優的重要部分。是以需要仔細研究
本文轉載樂沙彌同學文章，略作格式調整
二、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通路負載
(1). Oracle 10g之後： 可以使用SQL Access advisor來為SQL生成索引或物化視圖的建議。應當總是使用索引來優化SQL，特别是基于函數的索引。
(2). 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)。
指導原則
(1). 移除不必要的大型全表掃描
大型表的全表掃描将産生龐大的系統I/O且使得整個資料庫性能下降。
優化專家首先會評估目前SQL查詢所傳回的行數。
最常見的辦法是為走全表掃描的大表增加索引。
B樹索引，位圖索引，以及基于函數的索引等能夠避免全表掃描。有時候，對一些不必要的全表掃描通過添加提示的方法來避免全表掃描。
(2). 緩存小表全表掃描
有時候全表掃描是最快的通路方式，管理者應當確定專用的資料緩沖區(keep buffer cache，nk buffer cache)對這些表可用。在Oracle 8 以後小表可以被強制緩存到 keep 池。
(3). 使用最佳索引
Oracle 通路對象有時候會有一個以上的索引選擇。是以應當檢查目前查詢對象上的每一個索引以確定Oracle使用了最佳索引。
(4). 物化聚合運算以靜态化表統計
Oracle 10g的特性之一SQL Access advisor 會給出索引建議以及物化視圖的建議。
物化視圖可以預連接配接表和預摘要表資料。(譯者按，即Oracle可以根據特定的更新方式來提前更新物化視圖中的資料，而在查詢時僅僅查詢物化視圖即可得到最終所需的統計資料結果。物化視圖實際上是一張實體表)
(5). 總結
以上這些概括了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語句。
四、SQL調整戰略步驟
很多人問SQL tuning從哪裡着手。首先應當是從Library cache去根據他們的活動狀況捕獲SQL語句。
1. 尋找影響較大的SQL語句
我們可以根據SQL語句執行次數的多少進行排序來獲得執行次數較多的SQL語句。
在v$sqlarea視圖中executions 列以及表stats$sql_summary 或 dba_hist_sql_summary 能夠去定位目前最頻繁使用的SQL語句。注:也可以按照下列方式列出SQL語句。
Rows processed - 處理的行數越多，則相應會有很高的I/O，也有可能耗用大量的臨時表空間
Buffer gets - Buffer gets過高可能表明資源被過度集中化查詢，存在熱塊現象
Disk reads - 高的磁盤讀将引起過度的I/O
Memory KB - 記憶體的配置設定大小可以鑒别該SQL語句是否在記憶體中使用了大量的表連接配接
CPU secs - CPU的開銷表明哪些SQL語句耗用了大量的CPU資源
Sorts - 排序越多，則SQL性能越差，而且會占用大量的臨時表空間
Executions - 執行次數表明了目前SQL語句的頻繁度，應當被首先考慮調整，因為這些語句影響了資料庫的整體性能
2. 決定SQL的執行計劃
每一個SQL語句都可以根據SQL_ID來獲得其執行計劃。有大量的第三方工具來獲得SQL語句的執行計劃。
而獲得執行最常用的方式是使用Oracle自帶的explain plan程式。
通過使用該程式，Oracle DBA能夠在不執行SQL 語句的情形下解析并顯示該SQL語句的執行計劃。
檢視SQL執行計劃的輸出，必須首先建立一個plan table. Oracle提供一個utlxplan.sql腳本來建立該表。執行該腳本并且為該表建立一個公共同義詞。
大多數關系資料庫使用解釋程式将SQL語句作為輸入，然後運作SQL優化器，輸出通路的路徑資訊到一個plan_table。以便我們能夠檢視及調整其通路方式。下面的是一個複雜的SQL查詢。
這個文法使用管道輸入到SQL優化器，解析SQL，存儲執行計劃資訊到表plan_table，且RUN1作為鑒别目前SQL語句的辨別符。注意，該查詢并沒有執行，它僅僅是建立了一個内部通路資訊且輸出到plan_table。plan 表包含下列字段。
Operation - 表明目前語句完成的操作，通常包括table access, table merge, sort, or index operation
Options - 補充說明operation，像full table, range table, join
Object_name - 查詢元件的名字
Process ID - 查詢元件的ID号
Parent_ID - 查詢組建的父ID，注意，有些查詢會有一個相同的父ID
現在plan_table已經被填充，可以使用下面的查詢來檢視目前SQL語句的執行計劃。
下面給出了目前語句執行計劃資訊以及各個部分的執行順序。
從上面的執行計劃中得知目前的SQL語句存在表掃描現象。
去調整該SQL語句，我們應當尋找表where 子句中為planset上的列。
在這裡我們看到了在where子句存在一個且屬于表planset上的列mgc被用作連接配接條件。這說明一個基于表planset.mgs列上的索引是必要的。
Plan table并不能展現整個SQL語句的細節，但對于獲得資料通路路徑是非常有用的。SQL優化器知道每一個表的行數(基數)以及一些索引字段的狀況。但并不了解資料的分布象如一個元件期待傳回的行數。
3. 調整SQL語句
對于那些存在可優化的子執行計劃，SQL應當按照下面的方式進行調整。
通過添加提示來修改SQL的執行計劃
使用全局臨時表來重寫SQL
使用PL/SQL來重寫SQL。對于一些特定查詢該方法能夠有20倍左右的提升。将這些SQL封裝到包含存儲過程的包中去完成查詢。
使用提示來調整SQL
大多數SQL tuning工具中使用較多的莫過于使用提示。一個提示添加的SQL語句後使得SQL查詢的按指定路徑通路。
(1). Troubleshooting tip!
為便于測試，我們能夠随時使用alter session指令來修改一個優化參數的值來觀察調整前後的結果比較。使用新的 opt_param 提示能獲得同樣的效果。
select /*+ opt_param('optimizer_mode','first_rows_10') */ col1, col2 . . .
select /*+ opt_param('optimizer_index_cost_adj',20) */ col1, col2 . .
Oracle 釋出了大量的SQL提示，而且提示随着Oracle版本的不同不斷的增強和複雜化。
注意：提示通常用于調試SQL，最佳的辦法是調整優化器的統計資訊使的CBO模式自動擷取最佳執行路徑，等同于使用提示的功能。
我們來看看提高性能最常用的提示
Mode hints: first_rows_10, first_rows_100
Oracle leading and ordered hints Also see how to tune table join order with histograms
Dynamic sampling: dynamic_sampling
Oracle SQL undocumented tuning hints - Guru's only
The cardinality hint
(2). 表連接配接順序
當表連接配接的順序可優化時，我們可以使用 ORDERED提示來強制表按照from子句中出現的先後順序來進行連接配接
(3). first_rows_n提示
Oracle 有兩個基于成本優化的提示，一個是first_rows_n，一個是all_rows。first_rows模式将盡可能在一查詢到資料時就傳回個用戶端。
而 all_rows 模式則為優化資源而設計，需要等到所有結果計算執行完畢才傳回資料給用戶端。
SELECT /*+ first_rows */
4. 案例
同一個SQL語句有不同的寫法。即簡單的SQL查詢能夠以不同的方式來産生相同的結果集，但其執行效率和通路方式則千差萬别。
下面的例子中的SQL語句使用了3種不同的寫法來傳回相同的結果
(1). A standard join: -->标準連接配接
(2). A nested query: -->嵌套查詢
(3). A correlated subquery: -->相關子查詢
我們應該根據基本的SQL原則來優化目前的SQL語句。
5. 書寫高效SQL語句的技巧
下面給出一些編寫高效SQL語句的總的指導原則，而不論Oracle優化器選擇何種優化模式。這些看是簡單的方式但是按照他們去做将收到事半功倍的效果(已經在實踐中被證明)。
a.使用臨時表重寫複雜的子查詢
Oracle 使用全局臨時表以及WITH操作符去解決那些複雜的SQL子查詢。尤其是那些where子句中的子查詢，SELECT 字句标量子查詢，FROM 子句的内聯視圖。
使用臨時表實作SQL tuning(以及使用WITH的物化視圖)能夠使得性能得以驚人的提升。
b.使用MINUS 代替EXIST子查詢
使用MINUS操作代替NOT IN 或NOT EXISTS将産生更高效的執行計劃(譯者按：此需要測試)。
c.使用SQL分析函數
Oracle 分析函數能夠一次提取資料來做多元聚合運算(象ROLLUP,CUBE)以提高性能。
d.重寫NOT EXISTS和查詢作為外部連接配接NOT EXISTS 子查詢
在一些案例中的NOT 查詢(where 中一個列被定義為NULL值)，能夠将其改寫這個非相關子查詢到IS NULL 的外部連結。如下例：
下面我們在where子句中使用了外部連接配接來替代原來的not exits，得到一個更高效的執行計劃。
e.索引NULL值列
如果你的SQL語句頻繁使用到NULL值，應當考慮基于NULL值建立索引。為使該查詢最優化，可以建立一個使用基于NULL值索引函數。
f.避免基于索引的運算
不要基于索引列做任何運算，除非你建立了一個相應的索引函數。或者重設設計列以使得where子句列上的謂詞不需要轉換。
-->下面都是低效的SQL寫法
where salary*5 > :myvalue
where substr(ssn,7,4) = "1234"
where to_char(mydate,mon) = "january"
g.避免使用NOT IN 和HAVING
在合适的時候使用not exists子查詢更高效。
h.避免使用LIKE謂詞
在合适地時候，如果能夠使用 = 運算應盡可能避免LIKE操作。
i.避免資料類型轉換
如果一個where 子句列是數字型，則不要使用引号。而對一個字元索引列，總是使用引号。下面是資料類型混用的情形。
where cust_nbr = "123"
where substr(ssn,7,4) = 1234
j.使用decode與case
使用decode 與case 函數能夠最小化查詢表的次數。
k.不要害怕全表掃描
并不是所有的OLTP系統在使用索引時是最優化的。如果你的查詢傳回了表中的絕大部分資料，則全表掃描性能優于索引掃描。這取決于
一些因素包括你的配置(db_file_multiblock_read_count, db_block_size)，并行查詢，以及表塊和索引塊在buffer cache中的數量。
l.使用别名
在參照列的地方總是使用表别名。
Thanks and Regards
轉載：樂莎密 - http://blog.csdn.net/leshami/article/details/7422254
<b>ERP技術讨論群: 288307890</b>
<b>技術交流，技術讨論，歡迎加入</b>
<b>Technology Blog Created By Oracle ERP - 鮑建立</b>