??? SQL是的全稱是Structured Query Language(結構化查詢語言)。SQL是一個在80年代中期被使用的工業標準數據庫查詢語言。不要把SQL語言
與商業化產品如Microsoft SQL server或開源產品MySQL相混淆。所有的使用SQL縮略詞的這些都是SQL標準的一部分。
一、SQL tuning之前的調整
?? ?下面這個粗略的方法能夠節省數千小時乏味的SQL tuning，因為一旦調整它將影響數以百計的SQL查詢。記住，你必須優先調整它，否則后
?? ?續的優化器參數改變或統計信息可能不會有助于你的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開銷。但是他們能更快地返回數據。
?? ?當僅有一些表包含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"
三、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腳本來創建該表。執行該腳本并且為該表創建一個
?? ?公共同義詞。
?? ?sqlplus > @utlxplan
?? ?Table created.
?? ?
?? ?sqlplus > create public synonym plan_table for sys.plan_table;
?? ?Synonym created.
?? ?大多數關系數據庫使用解釋程序將SQL語句作為輸入，然后運行SQL優化器，輸出訪問的路徑信息到一個plan_table。以便我們能夠查看及調
?? ?整其訪問方式。下面的是一個復雜的SQL查詢。
?? ?EXPLAIN PLAN SET STATEMENT_ID = 'test1' FOR
?? ?SET STATEMENT_ID = 'RUN1'
?? ?INTO plan_table
?? ?FOR
?? ?SELECT?? 'T'||plansnet.terr_code, 'P'||detplan.pac1
?? ?|| detplan.pac2 || detplan.pac3, 'P1', sum(plansnet.ytd_d_ly_tm),
?? ? sum(plansnet.ytd_d_ty_tm),
?? ? sum(plansnet.jan_d_ly),
?? ? sum(plansnet.jan_d_ty),
?? ?FROM plansnet, detplan
?? ?WHERE
?? ??? ?plansnet.mgc = detplan.mktgpm
?? ?AND
?? ??? ?detplan.pac1 in ('N33','192','195','201','BAI',
?? ??? ?'P51','Q27','180','181','183','184','186','188',
?? ??? ?'198','204','207','209','211')
?? ?GROUP BY 'T'||plansnet.terr_code, 'P'||detplan.pac1 || detplan.pac2 || detplan.pac3;
?? ?這個語法使用管道輸入到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語句的執行計劃。
?? ?
?? ??? ?plan.sql - displays contents of the explain plan table
?? ??? ?SET PAGES 9999;
?? ??? ?SELECT? lpad(' ',2*(level-1))||operation operation,
?? ??? ??? ??? ?options,
?? ??? ??? ??? ?object_name,
?? ??? ??? ??? ?position
?? ??? ?FROM plan_table
?? ??? ?START WITH id=0
?? ??? ?AND
?? ??? ?statement_id = 'RUN1'
?? ??? ?CONNECT BY prior id = parent_id
?? ??? ?AND
?? ??? ?statement_id = 'RUN1';
?? ?下面給出了當前語句執行計劃信息以及各個部分的執行順序。
?? ?SQL> @list_explain_plan
?? ?
?? ?OPERATION
?? ?-------------------------------------------------------------------------------------
?? ?OPTIONS?????????????????????????? OBJECT_NAME??????????????????? POSITION
?? ?------------------------------ -------------------------------------------------------
?? ?SELECT STATEMENT
?? ?SORT
?? ?GROUP BY????????????????????????????????????????????????????? 1
?? ??? ??? CONCATENATION?????????????????????????????????? 1
?? ?NESTED LOOPS??????????????????????????????????? 1
?? ?TABLE ACCESS FULL???????? PLANSNET?????????????????? 1
?? ?TABLE ACCESS BY ROWID???? DETPLAN??????????????????? 2
?? ??? ??? ?? INDEX RANGE SCAN?????? DETPLAN_INDEX5???????????? 1
?? ?NESTED LOOPS
?? ?
?? ?從上面的執行計劃中得知當前的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查詢的按指定路徑訪問。
?? ?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?? ?
?? ?
?? ? 表連接順序
?? ???? 當表連接的順序可優化時，我們可以使用 ORDERED提示來強制表按照from子句中出現的先后順序來進行連接
?? ?
?? ?first_rows_n提示
?? ???? Oracle 有兩個基于成本優化的提示，一個是first_rows_n，一個是all_rows。first_rows模式將盡可能在一查詢到數據時就返回個客
?? ???? 戶端。而 all_rows 模式則為優化資源而設計，需要等到所有結果計算執行完畢才返回數據給客戶端。
?? ?
?? ??? ?SELECT /*+ first_rows */
?? ?
4、案例
?? ?同一個SQL語句有不同的寫法。即簡單的SQL查詢能夠以不同的方式來產生相同的結果集，但其執行效率和訪問方式則千差萬別。
?? ?
?? ?下面的例子中的SQL語句使用了3種不同的寫法來返回相同的結果
?? ?
?? ?A standard join:? -->標準連接
?? ?
?? ?SELECT *
?? ?FROM STUDENT, REGISTRATION
?? ?WHERE
?? ??? ?STUDENT.student_id = REGISTRATION.student_id
?? ?AND
?? ??? ?REGISTRATION.grade = 'A';
?? ?
?? ?A nested query:? -->嵌套查詢
?? ?SELECT *
?? ?FROM STUDENT
?? ?WHERE
?? ??? ?student_id =
?? ??? ?(SELECT student_id
?? ??? ??? ?FROM REGISTRATION
?? ??? ??? ?WHERE
?? ??? ??? grade = 'A'
?? ??? ?);
?? ?A correlated subquery:? -->相關子查詢
?? ?SELECT *
?? ?FROM STUDENT
?? ?WHERE
?? ??? ?0 <
?? ??? ?(SELECT count(*)
?? ??? ??? ?FROM REGISTRATION
?? ??? ??? ?WHERE
?? ??? ??? ?grade = 'A'
?? ??? ??? ?AND
?? ??? ??? ?student_id = STUDENT.student_id
?? ??? ?);
?? ??? ?
?? ?我們應該根據基本的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 的外部鏈接。如下例：
?? ??? ?select book_key from book
?? ??? ?where
?? ??? ?book_key NOT IN (select book_key from sales);
?? ??? ?下面我們在where子句中使用了外部連接來替代原來的not exits，得到一個更高效的執行計劃。
?? ??? ?select b.book_key from book b, sales s
?? ??? ?where
?? ??? ??? b.book_key = s.book_key(+)
?? ??? ?and
?? ??? ??? s.book_key IS NULL;
?? ? e.索引NULL值列
?? ??? ?如果你的SQL語句頻繁使用到NULL值，應當考慮基于NULL值創建索引。為使該查詢最優化，可以創建一個使用基于NULL值索引函數。
?? ??? ?(譯者按，如 create index i_tb_col on tab(nvl(col,null)); create index i_tb_col on tab(col,0);)
?? ? 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.使用別名
?? ??? ?在參照列的地方總是使用表別名。
??? --> Author : Robinson Cheng
?? ?--> Blog?? : http://blog.csdn.net/robinson_0612
四、結論：
?? ?這篇文章從整體上描述SQL tuning的一些步驟，并未涉及SQL tuning的具體細節。更多參考： "Oracle Tuning: The Definitive Reference"
與商業化產品如Microsoft SQL server或開源產品MySQL相混淆。所有的使用SQL縮略詞的這些都是SQL標準的一部分。
一、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"
三、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腳本來創建該表。執行該腳本并且為該表創建一個
?? ?公共同義詞。
?? ?sqlplus > @utlxplan
?? ?Table created.
?? ?
?? ?sqlplus > create public synonym plan_table for sys.plan_table;
?? ?Synonym created.
?? ?大多數關系數據庫使用解釋程序將SQL語句作為輸入，然后運行SQL優化器，輸出訪問的路徑信息到一個plan_table。以便我們能夠查看及調
?? ?整其訪問方式。下面的是一個復雜的SQL查詢。
?? ?EXPLAIN PLAN SET STATEMENT_ID = 'test1' FOR
?? ?SET STATEMENT_ID = 'RUN1'
?? ?INTO plan_table
?? ?FOR
?? ?SELECT?? 'T'||plansnet.terr_code, 'P'||detplan.pac1
?? ?|| detplan.pac2 || detplan.pac3, 'P1', sum(plansnet.ytd_d_ly_tm),
?? ? sum(plansnet.ytd_d_ty_tm),
?? ? sum(plansnet.jan_d_ly),
?? ? sum(plansnet.jan_d_ty),
?? ?FROM plansnet, detplan
?? ?WHERE
?? ??? ?plansnet.mgc = detplan.mktgpm
?? ?AND
?? ??? ?detplan.pac1 in ('N33','192','195','201','BAI',
?? ??? ?'P51','Q27','180','181','183','184','186','188',
?? ??? ?'198','204','207','209','211')
?? ?GROUP BY 'T'||plansnet.terr_code, 'P'||detplan.pac1 || detplan.pac2 || detplan.pac3;
?? ?這個語法使用管道輸入到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語句的執行計劃。
?? ?
?? ??? ?plan.sql - displays contents of the explain plan table
?? ??? ?SET PAGES 9999;
?? ??? ?SELECT? lpad(' ',2*(level-1))||operation operation,
?? ??? ??? ??? ?options,
?? ??? ??? ??? ?object_name,
?? ??? ??? ??? ?position
?? ??? ?FROM plan_table
?? ??? ?START WITH id=0
?? ??? ?AND
?? ??? ?statement_id = 'RUN1'
?? ??? ?CONNECT BY prior id = parent_id
?? ??? ?AND
?? ??? ?statement_id = 'RUN1';
?? ?下面給出了當前語句執行計劃信息以及各個部分的執行順序。
?? ?SQL> @list_explain_plan
?? ?
?? ?OPERATION
?? ?-------------------------------------------------------------------------------------
?? ?OPTIONS?????????????????????????? OBJECT_NAME??????????????????? POSITION
?? ?------------------------------ -------------------------------------------------------
?? ?SELECT STATEMENT
?? ?SORT
?? ?GROUP BY????????????????????????????????????????????????????? 1
?? ??? ??? CONCATENATION?????????????????????????????????? 1
?? ?NESTED LOOPS??????????????????????????????????? 1
?? ?TABLE ACCESS FULL???????? PLANSNET?????????????????? 1
?? ?TABLE ACCESS BY ROWID???? DETPLAN??????????????????? 2
?? ??? ??? ?? INDEX RANGE SCAN?????? DETPLAN_INDEX5???????????? 1
?? ?NESTED LOOPS
?? ?
?? ?從上面的執行計劃中得知當前的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查詢的按指定路徑訪問。
?? ?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?? ?
?? ?
?? ? 表連接順序
?? ???? 當表連接的順序可優化時，我們可以使用 ORDERED提示來強制表按照from子句中出現的先后順序來進行連接
?? ?
?? ?first_rows_n提示
?? ???? Oracle 有兩個基于成本優化的提示，一個是first_rows_n，一個是all_rows。first_rows模式將盡可能在一查詢到數據時就返回個客
?? ???? 戶端。而 all_rows 模式則為優化資源而設計，需要等到所有結果計算執行完畢才返回數據給客戶端。
?? ?
?? ??? ?SELECT /*+ first_rows */
?? ?
4、案例
?? ?同一個SQL語句有不同的寫法。即簡單的SQL查詢能夠以不同的方式來產生相同的結果集，但其執行效率和訪問方式則千差萬別。
?? ?
?? ?下面的例子中的SQL語句使用了3種不同的寫法來返回相同的結果
?? ?
?? ?A standard join:? -->標準連接
?? ?
?? ?SELECT *
?? ?FROM STUDENT, REGISTRATION
?? ?WHERE
?? ??? ?STUDENT.student_id = REGISTRATION.student_id
?? ?AND
?? ??? ?REGISTRATION.grade = 'A';
?? ?
?? ?A nested query:? -->嵌套查詢
?? ?SELECT *
?? ?FROM STUDENT
?? ?WHERE
?? ??? ?student_id =
?? ??? ?(SELECT student_id
?? ??? ??? ?FROM REGISTRATION
?? ??? ??? ?WHERE
?? ??? ??? grade = 'A'
?? ??? ?);
?? ?A correlated subquery:? -->相關子查詢
?? ?SELECT *
?? ?FROM STUDENT
?? ?WHERE
?? ??? ?0 <
?? ??? ?(SELECT count(*)
?? ??? ??? ?FROM REGISTRATION
?? ??? ??? ?WHERE
?? ??? ??? ?grade = 'A'
?? ??? ??? ?AND
?? ??? ??? ?student_id = STUDENT.student_id
?? ??? ?);
?? ??? ?
?? ?我們應該根據基本的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 的外部鏈接。如下例：
?? ??? ?select book_key from book
?? ??? ?where
?? ??? ?book_key NOT IN (select book_key from sales);
?? ??? ?下面我們在where子句中使用了外部連接來替代原來的not exits，得到一個更高效的執行計劃。
?? ??? ?select b.book_key from book b, sales s
?? ??? ?where
?? ??? ??? b.book_key = s.book_key(+)
?? ??? ?and
?? ??? ??? s.book_key IS NULL;
?? ? e.索引NULL值列
?? ??? ?如果你的SQL語句頻繁使用到NULL值，應當考慮基于NULL值創建索引。為使該查詢最優化，可以創建一個使用基于NULL值索引函數。
?? ??? ?(譯者按，如 create index i_tb_col on tab(nvl(col,null)); create index i_tb_col on tab(col,0);)
?? ? 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.使用別名
?? ??? ?在參照列的地方總是使用表別名。
??? --> Author : Robinson Cheng
?? ?--> Blog?? : http://blog.csdn.net/robinson_0612
四、結論：
?? ?這篇文章從整體上描述SQL tuning的一些步驟，并未涉及SQL tuning的具體細節。更多參考： "Oracle Tuning: The Definitive Reference"
??? 原文鏈接： http://www.dba-oracle.com/art_sql_tune.htm
五、更多參考：dbms_xplan之display_cursor函數的使用
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
