? 将SQL 優化指導用于：
– 确定使用資源最多的 SQL 語句
– 優化使用資源最多的 SQL 語句
? 使用SQL 通路指導優化工作量
SQL 優化
SQL 優化程序
? 确定沒有很好地優化的SQL 語句。
? 優化各條語句。
? 優化整個應用程式。
一般情況下，效果最明顯的優化工作是SQL 優化。沒有很好地優化的SQL 會不必要地使用過多資源。這種低效率會降低可伸縮性、使用更多的OS
和資料庫資源并增加響應時間。要對沒有很好地優化的SQL
語句進行優化，必須先确定這些語句，然後再進行優化。可以分别對各條SQL
語句進行優化，但優化一條語句的解決方法經常會影響多條其它語句的性能。
顧名思義，使用資源最多的SQL 語句是需要優化的語句。這些語句的用時最長，使用的CPU
資源最多，或者實體或邏輯讀取次數最多。
優化各條語句的方法是：檢查優化程式統計資訊，檢查最高效的通路路徑的解釋計劃，測試替代SQL
構造，以及測試可能的新索引、實體化視圖和分區。
使用優化後的SQL 語句測試整個應用程式的性能。整體性能是否有所提高？
方法很可靠，但有些拖沓。優化單條語句并不難。測試各條語句優化對應用程式的整體影響可能非常困難。
在Oracle Database 11 g 中，可以使用一組SQL 指導來确定和優化語句（單獨優化或集體優化）。
SQL 指導
Oracle Database 11 g 提供了一組SQL 指導：SQL 通路指導、SQL 優化指導、SQL 性能分析器和SQL
修複指導。AWR 可确定最近的高負荷SQL 語句并記錄有關的統計資訊。
SQL 優化指導可分析一個或多個SQL
語句（一次分析一個）。它會檢查統計資訊、SQL 概要檔案、索引、實體化視圖以及調整後的SQL 。SQL
優化指導可随時通過手動方式運作；但主要是在每個維護視窗期間針對最近的高負荷SQL
語句運作。單擊“Automatic SQL Tuning Results（自動SQL
優化結果）”可檢視和實施建議。可以對此自動作業進行配置，自動為高負荷語句實施建議的SQL 概要檔案。
SQL 通路指導會考察應用到一組SQL 語句的更改，确定性能是否有淨提高。這組語句可能是一組假想的SQL
語句，以往的語句或手動建立的語句。
SQL 性能分析器可用于預測和防止任何影響SQL 執行計劃結構的資料庫環境更改所帶來的潛在性能問題。
SQL 修複指導是在某個SQL 語句因嚴重錯誤而失敗時從支援工作台運作的。嚴重錯誤還會産生意外事件。修複指導會嘗試查找并推薦一個SQL
更新檔程式。如果未找到任何更新檔程式，可以接着在支援工作台中打包意外事件，并将意外事件程式包作為服務請求(SR) 送出給Oracle
技術支援部門。
自動SQL 優化結果
預設情況下，自動SQL 優化任務在每天晚上運作。自動SQL 優化結果連結會顯示結果概要頁。如果單擊“View
Report（檢視報表）”，則可檢視接受檢查的每條SQL 語句。
單擊“Configure（配置）”按鈕會顯示一個頁面；在此頁面中，可以更改自動優化任務的預設設定，并啟用SQL
概要檔案自動實施功能。
實施自動優化建議
如果單擊“Automatic Tuning Results Summary （自動優化結果概要）”頁上的“View Report
（檢視報表）”按鈕，将顯示“Automatic SQL Tuning Result Details （自動SQL
優化結果詳細資料）”頁。可以實施所有建議，或細化以檢視或實施單個建議。
在“Recommendations（建議）”頁上，可以單擊右側的眼鏡圖示檢視實施SQL
概要檔案會對解釋計劃産生的影響。
SQL 優化指導：概覽
全面SQL優化
檢測過時或缺少的統計資訊
優化SQL 計劃（SQL 概要檔案）
添加缺少的索引
調整SQL 結構
SQL 優化指導是優化過程中使用的主要驅動程式。它可以執行多種類型的分析：
?
統計資訊分析：檢查每個查詢對象是否缺少統計資訊或統計資訊是否過時，然後提出建議以收集相關的統計資訊。
? SQL 概要分析：優化程式會驗證它自身的估計值并收集輔助資訊以消除估計錯誤。它使用輔助資訊建構SQL
概要檔案，并提出建立SQL 概要檔案的建議。建立SQL 概要
檔案後，查詢優化程式便可以通過此檔案生成合理優化的計劃。
通路路徑分析：考察新索引是否會顯著改善對查詢中的每個表的通路性能。如果合适，則會建議建立此類對象。
? SQL 結構分析：确定使用了錯誤計劃的 SQL
語句，并提出對這些語句進行結構調整的相關建議。建議的更改可能涉及文法方面的更改，也可能涉及語義方面的更改。
SQL 優化指導會分别考察指導任務中包括的每個SQL 語句。建立一個新索引可能會對查詢有幫助，但也可能增加DML
的響應時間。是以，應使用SQL 通路指導檢查建議的索引或其它對象對工作量（一組SQL
語句）的影響，以确定性能是否有淨提高。
使用SQL 優化指導
? 使用SQL 優化指導可分析SQL 語句，并獲得性能建議。
? SQL 優化指導分析的來源：
– 頂級活動：分析目前處于活動狀态的頂級 SQL 語句
– SQL 優化集：分析使用者提供的一組 SQL 語句
– 以往的SQL (AWR)：分析 AWR 快照收集的 SQL 語句中的語句
SQL 優化指導會在每晚以自動SQL 優化任務的形式自動運作。有時，可能需要對某個SQL 語句立即進行優化操作。你可以随時使用SQL
優化指導分析SQL
語句，并獲得性能建議。通常，運作此指導類似于使用ADDM 查找性能問題的操作。
此外，可以運作SQL 優化指導來分析哪些SQL 語句占用的CPU 時間、I/O 和記憶體最多。
即使在一項任務中送出了多條語句進行分析，對每條語句的分析仍是分别進行的。要獲得考慮了一組SQL 語句的整體性能的優化建議，使用SQL
通路指導。
SQL 優化指導選項
在“Schedule SQL Tuning Advisor（排程SQL
優化指導）”頁上，可以選擇要包括的SQL 語句，以及更改優化任務的自動預設設定。可以設定SQL
語句的來源；如果有
ADVISOR系統權限，可以送出任務。随後，Oracle Enterprise Manager 會為SQL
優化指導建立一項優化任務。
通過SQL 語句選項，可以從以下來源中選擇一條或多條SQL 語句：最近的頂級活動、AWR 中存儲的以往的SQL 語句或者已建立的SQL
優化集。
選擇優化任務的适當範圍很重要。如果選擇“Limited（有限制）”選項，SQL 優化指導會根據統計資訊檢查結果、通路路徑分析結果和SQL
結構分析結果來生成建議。“Limited（有限制）”選項不會産生SQL
概要檔案建議。如果選擇“Comprehensive（綜合）”選項，SQL
優化指導不僅會生成“Limited（有限制）”選項生成的所有建議，在SQL 概要分析模式下還會調用優化程式來建構SQL
概要檔案。使用“Comprehensive（綜合）”選項時，還可以指定優化任務的時間限制，該時間限制的預設值是30
分鐘。選擇“Run SQL Tuning Advisor（運作 SQL 優化指導）”之後，請使用“SQL Tuning
Options（SQL 優化選項）”頁配置優化任務。
SQL 優化指導建議
任務的SQL 優化結果會在任務完成後即刻顯示，也可以在以後通過“Advisor
Central（指導中心）”頁通路這些優化結果。此時還會顯示建議的概要。可以複查和實施單個建議。選擇語句并單擊“View（檢視）”。
使用SQL 優化指導：示例
可以通過執行以下步驟來調用SQL 優化指導：
1. 在“Database（資料庫）”首頁中，單擊“Related Links（相關連結）”區域中的“Advisor
Central（指導中心）”。
2. 單擊“SQL Advisors （SQL 指導）”。此時将出現“SQL Tuning Advisor
Links（SQL 優化指導連結）”頁。
可以對以下任一來源運作該指導：
- Active SQL （活動SQL）：分析目前活動的頂級 SQL 語句
- SQL Tuning Sets（SQL 優化集）：分析使用者提供的一組 SQL 語句
- Historical SQL (AWR) （以往的SQL (AWR)）：分析AWR
快照捕獲的SQL 語句
3. 選擇“Active SQL （活動SQL
）”。選擇要分析的五分鐘間隔，方法是将灰色框拖到目标時間段上。請選擇要在標明期間内分析的一條或多條語句。
4. 單擊“Run SQL Tuning Advisor（運作 SQL 優化指導）”。此時會出現“SQL
Tuning Options（SQL 優化選項）”頁，顯示此時間間隔内的SQL 語句。提供任務的名稱和
描述，選擇“Comprehensive（綜合）”作為範圍，選擇“Immediately（立即）”作為啟動時間。單擊“OK（确定）”。
5. 重新導航到“Advisor Central（指導中心）”頁。指導任務的狀态列于“Results
（結果）”區域中此标題的下面。請一直等待，直至任務狀态變為已完成。通過單擊浏覽
器中的“Refresh（重新整理）”檢查狀态。選擇任務并單擊“View Result （檢視結果）”。此時會顯示“SQL Tuning
Result （SQL 優化結果）”頁。
6. 選擇SQL 語句并單擊“View Recommendations （檢視建議）”。
重複的SQL
語句是指隻是所用的文字值或格式有所不同的語句。每個不同的語句在庫高速緩存中都會有一個單獨的遊标。如果将文字替換為綁定變量，并統一格式，則重複的語句可以使用相同的遊标。
通過在“Additional Monitoring
Links（其他監視連結）”區域中單擊“Performance（性能）”頁籤頁上的“Duplicate SQL（重複的SQL
）”，可以确定重複的SQL 語句。已确定為重複的SQL
（不考慮格式或文字差異）會列在一起。這将有助于确定應用程式中的哪個SQL
可以合并，進而降低對庫高速緩存的需求，提高語句的執行速度。
SQL 通路指導：概覽
評估 SQL 的全部工作量, 并建議索引, 分區和實體化視圖, 它們将改善 SQL 工作量的總體性能。
SQL 通路指導能夠為給定工作量建議一組适當的實體化視圖、實體化視圖日志、分區和索引。了解和使用這些結構在優化SQL
時至關重要，因為這些結構在資料檢索時能夠極大地改善性能。
SQL 通路指導建議使用位圖索引、基于函數的索引和B
樹索引。位圖索引可減少許多類型的即席查詢的響應時間，并且相對于其它索引技術而言降低了存儲要求。B
樹索引在資料倉庫中最為常用，用于為唯一關鍵字或接近唯一的關鍵字編制索引。
SQL 通路指導的另一個元件還提供了優化實體化視圖的建議，使這些視圖可以快速重新整理并利用正常查詢重寫。
典型的SQL 通路指導會話
啟動SQL 通路指導會話時，可以選擇“Use Default
Options（使用預設選項）”，以一組建議的預定義指導選項開始。此外，還可以選擇“Inherit Options from a Task or
Template（從任務或模闆繼承選項）”來啟動任務，并使該任務繼承一組由模闆或任務定義的選項值。這些模闆包括多個針對通用環境、OLTP
和資料倉庫資料庫設計的通用模闆。你可以儲存先前任務中的定制模闆并在需要時重新使用。
單擊“Continue （繼續）”啟動SQL 通路指導向導。
工作量來源
使用SQL 通路指導向導的“Workload
Source（工作量源）”頁提供已定義的工作量，以便通路指導可以生成建議。支援的工作量源包括：
? Current and Recent SQL Activity（目前和最近的SQL 活動）：使用高速緩存中的目前SQL
作為工作量
? Use an existing SQL Tuning Set（使用現有的SQL 優化集）：可以将先前建立的
SQL 優化集指定為工作量源
? Create a Hypothetical Workload from the Following Schemas and Tables
（從下列方案和表建立假想工作量）：提供方案以便通路指導可以搜尋維表并生成工作量
通過應用“Filter Options
（過濾器選項）”部分中的可用過濾器，可以進一步縮小工作量的範圍。使用這些選項，可以縮小出現在工作量中的SQL
語句的範圍。指導将過濾器應用于工作量後，可以對重點部分進行優化。
可用的過濾器選項包括：
? 消耗資源最多的SQL 語句
? 使用者、子產品辨別符或操作
? 表
建議選項
使用“Recommendations Options （建議選項）”頁可以選擇是否限制指導基于單個通路方法提出建議。從“Access
Structures to Recommend（要推薦的通路結構）”部分選擇“Indexes（索引）”、“Materialized
Views （實體化視圖）”、“Partitioning （分區）”或者上述各項的任意組合。可以選擇“Evaluation
Only（僅評估）”，進而隻評估現有的通路結構。在此模式下，指導不生成新的建議，而是就現有結構的使用提出意見。此項在跟蹤目前索引、實體化視圖的有效性和MV
日志使用率随時間變化方面非常有用。
可以使用“Advisor Mode
（指導模式）”部分，以兩種模式之一運作指導。這些模式會影響建議的品質和處理所需的時間。在“Comprehensive（綜合）”模式中，指導将搜尋候選的大型池，以便得到最高品質的建議。在“Limited（有限制）”模式中，指導執行速度很快，但是會限制候選建議。
可以選擇“Advanced
Options（進階選項）”來顯示或隐藏選項，這些選項可用于設定空間限制、優化選項和預設存儲位置。使用“Workload
Categorization（工作量類别）”部分可以設定“Workload
Volatility（工作量不穩定性）”和“Workload
Scope（工作量範圍）”選項。生成建議時，可以選擇關注隻讀操作，或者考察被引用對象的不穩定性。你還可以選擇“Partial
Workload （部分工作量）”，其中不包括删除未使用的通路結構的建議；或者選擇“Complete
Workload（全部工作量）”，其中包括删除未使用的通路結構的建議。
使用“Space
Restrictions（空間限制）”部分可指定硬性空間限制，強制指導僅使用不超過指定限制的總空間要求生成建議。
使用“Tuning Options（優化選項）”部分可指定相關選項對指導生成的建議進行定制。
使用“Prioritize Tuning of SQL Statements by（确定優化SQL
語句優先級的依據）”下拉清單，可以按“Optimizer Cost（優化程式開銷）”、“Buffer Gets
（緩沖區擷取數）”、“CPU Time（CPU 時間）”、“Disk Reads（磁盤讀取數）”、“Elapsed
Time（用時）”和“Execution Count （執行計數）”劃分優先級。
使用“Default Storage
Locations（預設存儲位置）”部分可以覆寫為方案和表空間位置定義的預設值。預設情況下，索引放置在所引用表的方案和表空間中。實體化視圖放置在相應使用者的方案和表空間中，該使用者執行的某一個查詢為實體化視圖建議提供了資訊。
定義了這些參數以後，可以排程和複查優化任務。
複查建議
使用“Advisor Central（指導中心）”頁可以列出所有已完成的SQL 通路指導任務。選擇要檢視建議的任務，然後單擊“View
Result （檢視結果）”按鈕。使用“Results for Task（任務結果）”的“Summary
（概要）”頁，可以通路指導查找結果的概覽。該頁中給出了圖表和統計資訊，提供了建議可能對整體工作量性能和查詢執行時間産生的改進。使用該頁可以顯示語句計數和建議操作計數。
要檢視指導任務結果的其它方面，可單擊該頁上其它三個頁籤之一：“Recommendations（建議）”、“SQL
Statements（SQL 語句）”或“Details （詳細資料）”。
“Recommendations（建議）”頁中提供了一個圖表和一個表，其中按建議對整個工作量總開銷的改進百分比顯示頂級建議。頂級建議對總體性能的改進幅度最大。
單擊“Show SQL（顯示SQL ）”按鈕，可以檢視為所選建議生成的SQL
腳本。在表中單擊相應的建議辨別符，可以檢視實施建議所需執行的操作清單。在“Actions（操作）”
頁上，可以檢視實施操作需要執行的所有相應SQL 語句。對于不希望實施的建議，請使對應的複選框處于未選中狀态。然後，單擊“Schedule
Implementation（安排實施）”按鈕來實施保留的操作。此步驟以排程程式作業的形式執行。
SQL 性能分析程式：概覽
? 目标使用者：DBA、QA、應用程式開發人員
? 幫助預測系統更改對SQL 工作量響應時間的影響
? 建構不同版本的SQL 工作量性能資料（即SQL 執行計劃和執行統計資訊）
? 以串行方式執行SQL（不考慮并發）
? 分析性能差異
? 針對單個SQL 提供細粒度性能分析
? 與SQL 優化指導相內建以優化回歸
Oracle Database 11 g 包括SQL 性能分析程式，該工具可以準确地評估更改對構成工作量的SQL 語句的影響。SQL
性能分析程式有助于預測潛在的更改對SQL 查詢工作量的性能影響。此功能可為DBA 提供有關SQL
語句性能的詳細資訊，如執行前後的統計資訊，以及性能有所提高或降低的語句。這樣您便可以（例如）在測試環境中進行更改，以确定資料庫的更新是否會改進工作量性能。
SQL 性能分析程式：使用情形
SQL 性能分析程式可用于預測和防止任何影響SQL
執行計劃結構的資料庫環境更改所帶來的潛在性能問題。這些更改可包括（但不限于）以下任何更改：
? 資料庫更新
? 實施優化建議
? 方案更改
? 收集統計資訊
? 更改資料庫參數
? 更改作業系統和硬體
可以使用SQL 性能分析程式分析最複雜的環境更改所導緻的SQL
性能變化。随着應用程式在開發生命周期中的推進，資料庫應用程式開發人員可以測試對方案、資料庫對象和重寫應用程式的更改，以減輕任何潛在的性能影響。
SQL 性能分析程式還可以比較SQL 性能統計資訊。
可以通過Oracle Enterprise Manager 或使用DBMS_SQLPA程式包通路SQL 性能分析程式。
使用SQL 性能分析程式
1. 捕獲生産環境的SQL 工作量。
2. 将SQL 工作量傳輸至測試系統。
3. 建構“更改前”性能資料。
4. 進行更改。
5. 建構“更改後”性能資料。
6. 比較步驟3 和步驟5 的結果。
7. 優化回歸的SQL。
1. 收集SQL：在此階段中，将收集代表生産系統中的 SQL 工作量的SQL 語句集。
2. 傳輸：必須将得到的工作量結果傳輸到測試系統。将 STS 從生産系統中導出，然後再導入測試系統。
3. 計算“之前版本”性能：在進行任何更改之前，先執行 SQL
語句，收集評估未來更改對工作量性能可能産生的影響所需的基線資訊。
4. 進行更改：獲得了之前版本資料後，即可實施計劃的更改，然後開始檢視對性能的影響。
5. 計算“之後版本”性能：在資料庫環境中進行了更改之後，執行此步驟。SQL
工作量中的每個語句都在虛拟執行模式下運作（僅收集統計資訊），收集與步驟3 所捕獲的
相同的資訊。
6. 比較和分析SQL 性能：在獲得了兩個版本的 SQL
工作量性能資料後，可以比較之後與之前版本的資料，進行性能分析。
7. 優化回歸的SQL：在此階段中，已經準确地确認了哪些SQL
語句在進行資料庫更改時可能導緻性能問題。你可以使用任何一種資料庫工具來優化系統。在實施了任何優
化操作後，應重複該過程來建立新的之後版本資料，然後分析性能差異以確定新的性能是可接受的。
小結
來源：http://blog.csdn.net/rlhua/article/details/13021985