課程目標：

使用SQL調優指導定位使用最多資源的SQL語句並調優
使用SQL訪問指導調優負載

1、SQL調優

一般來說，對SQL調優會獲得最大的效能提升。差的SQL語句會消耗額外的作業系統和資料庫資源，增加響應時間。我們需要先定位到差的SQL語句再進行調優。可以對單個SQL語句進行調優，但往往對一個語句的調優方案會影響到其他語句的效能。

使用最多資源的SQL語句往往需要調優，包括執行最長時間，使用最多CPU或者最多物理讀或邏輯讀。

透過檢查最佳化器統計資料，執行計劃，測試其他SQL結構，索引，物化檢視和分割槽調優SQL語句。

評估使用調優的SQL語句後，對整個應用來說，效能是否更好了。調優單個語句並不是很複雜，難的是單個語句調優後測試對整個系統的影響。

在Oracle 11g中，提供了一些SQL指導用於識別和調優語句。

2、SQL指導

Oracle 11g提供了一系列的SQL指導，包括SQL訪問指導，SQL調優指導，SQL效能分析器和SQL修復指導。AWR識別和記錄最近高負載SQL語句的統計資料。

SQL調優指導一次分析一個或多個SQL語句，檢查統計資料，SQL配置檔案，索引，物化檢視，並重組SQL。SQL調優指導可以隨時手動執行，在每一個維護視窗中都會對近期的高負載SQL語句執行SQL調優指導。單擊“自動SQL調優結果集”檢視和實施建議。這種自動工作可以配置為自動執行SQL配置檔案。

SQL訪問指導用於評估對一組SQL語句集進行改變後效能的提升。語句集可以是一組假設的SQL，歷史SQL集或者手動建立的SQL集。

對於影響SQL執行計劃的任何資料庫環境變化，SQL效能分析程式可以用來預測和防止潛在的效能問題。

當一個SQL語句由於關鍵錯誤執行失敗，會從支援工作臺執行SQL修復指導。一個關鍵的錯誤也會產生一個事件。修復指導試圖尋找和定位一個SQL補丁。如果沒有找到補丁，可以繼續在支援工作臺打包事件並作為服務請求提交給Oracle支援。

3、自動SQL調優結果集

自動SQL調優任務預設每天晚上執行。在自動SQL調優結果集頁面點選”檢視報告”，可以檢視已經檢查過的SQL語句。

單擊“配置”按鈕改變自動調整任務的預設設定並啟用SQL配置檔案自動執行。

4、實施自動調優建議

在自動調優結果彙總頁面點選”檢視報告”，會看到自動SQL調優結果的細節。可以執行所有的建議或向下鑽取檢視或執行單獨的建議。在”建議”頁面，點選眼鏡圖示檢視執行SQL概要檔案後的差異。

5、SQL調優指導：概覽

SQL調優指導是主要的調優手段，執行以下幾類分析：

Statistics Analysis：檢查查詢物件的統計資料是否缺失或者過時，並建議收集相關的統計資料。
SQL Profiling：使用輔助資訊建立SQL概要檔案，使查詢最佳化器可以產生一個調優的執行計劃。
Access Path Analysis：評估是否需要透過新的索引來提高效能。
SQL Structure Analysis：定位使用效能低下的執行計劃的SQL語句並對重構該SQL語句提出建議。

SQL調優指導在獨立的指導任務中評估SQL語句，不考慮其他因素。比如建立索引可能會提高查詢速度，但會影響DML操作的速度。所以需要使用SQL訪問指導在負載下評估是否對效能有好處。

6、使用SQL調優指導

SQL調優指導作為自動SQL調優任務每天晚上自動執行。針對需要立即進行調優的SQL語句，可以使用SQL調優指導在任何時間分析SQL語句並獲取效能建議。一般情況下，會作為ADDM效能查詢活動來執行SQL調優指導。

另外，也可以使用SQL調優指導來分析消耗最大CPU，I/O和記憶體的TOP SQL語句。

即使可以在一個任務中提交多條語句進行分析，但是還是對每一條語句單獨進行分析。要獲取一個SQL集的整體的效能建議，需要使用SQL訪問指導。

7、SQL調優指導選項

在“排程SQL調優指導”頁面，可以選擇調優任務的SQL語句。如果有ADVISOR系統許可權，可以設定SQL語句來源並提交任務。EM會為SQL調優指導建立一個調優任務。

可以從頂級活動中選擇一個或者多個SQL語句，從AWR中選擇歷史SQL語句或者從已經建立的SQL調優集選擇SQL語句進行調優。

如果選擇“Limited”選項，則不會生成SQL概要檔案。選擇“Comprehensive”模式會呼叫SQL profiling模式下的最佳化器來建立SQL概要檔案。

8、SQL調優指導建議

調優任務完成後會生成調優結果集，可以在“指導中心”頁面看到。

9、使用SQL調優指導：示例

按照以下步驟使用SQL調優指導：

（1）在主頁點選“相關連結”區域的“指導中心”。

（2）點選“SQL Advisors（SQL 指導）”，可以看到“SQL Tuning Advisor（SQL 調優指導）”連結。其資料來源有三類：

Active SQL（頂級活動）：分析當前活動的Top SQL語句
SQL Tuning Sets（SQL最佳化集）：分析提供的SQL語句集
Historical SQL（AWR）：分析AWR中的SQL語句

（3）選擇Active SQL（頂級活動）。透過拖動陰影框選項分析的時間間隔，然後選擇一個或者多個語句來進行分析。

（4）選擇“Run SQL Tuning Advisor”，在“SQL Tuning Options（SQL調優選項）”頁面可以看到選擇的SQL語句。指定任務名稱和描述，選擇“Comprehensive（綜合）”以及“Immediately（立即）”，然後點選OK。

（5）回到“Advisor Central（指導中心）”頁面，在“結果”區域列出了各個指導任務的狀態。等到剛剛的任務完成，點選任務名稱檢視結果。

（6）選擇SQL語句檢視生成的建議。

例子：使用SQL調優指導

SQL> create table hr.objtest as select * from dba_objects;

Table created.

SQL> conn hr/hr

Connected.

SQL> insert into objtest select * from objtest;

86987 rows created.

SQL> r

1* insert into objtest select * from objtest

173974 rows created.

SQL> r

1* insert into objtest select * from objtest

347948 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from objtest;

COUNT(*)

----------

695896

SQL> exec dbms_stats.gather_table_stats('HR','OBJTEST');

PL/SQL procedure successfully completed.

SQL> select owner,object_name from objtest where object_name='EMP';

SQL> set autot traceonly

SQL> r

1* select owner,object_name from objtest where object_name='EMP'

24 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 927592698

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 13 | 403 | 2750 (1)| 00:00:34 |

|* 1 | TABLE ACCESS FULL| OBJTEST | 13 | 403 | 2750 (1)| 00:00:34 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_NAME"='EMP')

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

10109 consistent gets

0 physical reads

0 redo size

1043 bytes sent via SQL*Net to client

530 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

24 rows processed

10、重複的SQL

重複的SQL語句是指那些只有字變數的值不一樣的語句。在庫快取中不同的語句有不同的遊標，重複的SQL語句如果使用繫結變數替換字變數就可以使用相同的遊標。

可以透過“效能”頁面的“其他監視連結”區域的“重複的SQL”連結檢視重複的SQL語句。這個可以幫助你確定應用裡面哪些SQL語句可以合併以降低對庫快取的需求及提高執行速度。

例子：檢視重複的SQL

SQL> select * from employees where employee_id=100;

SQL> select * from employees where employee_id=101;

SQL> select * from employees where employee_id=102;

SQL> select * from employees where employee_id=103;

SQL> select * from employees where employee_id=104;

建立繫結變數的情況：

SQL> var deptid number

SQL> exec :deptid:=20;

PL/SQL procedure successfully completed.

SQL> select * from departments where department_id=:deptid;

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

20 Marketing 201 1800

SQL> exec :deptid:=30;

PL/SQL procedure successfully completed.

SQL> select * from departments where department_id=:deptid;

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

30 Purchasing 114 1700

SQL> exec :deptid:=40;

PL/SQL procedure successfully completed.

SQL> select * from departments where department_id=:deptid;

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

40 Human Resources 203 2400

SQL> exec :deptid:=50;

PL/SQL procedure successfully completed.

SQL> select * from departments where department_id=:deptid;

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

50 Shipping 121 1500

11、SQL訪問指導：概覽

SQL 訪問指導將評估工作量源中的 SQL 語句, 並可以建議索引, 分割槽, 物化檢視和物化檢視日誌, 它們可以改善工作量的整體效能。

SQL訪問指導可以建議點陣圖索引，函式索引和B樹索引。還可以對如何最佳化物化檢視提供建議。

12、典型的SQL訪問指導會話

可以選擇使用推薦的預定義指導選項啟動SQL訪問指導會話，還可以透過選擇“從以前儲存的任務或模板繼承選項”核取方塊，使用包括通用環境、OLTP和資料倉儲模版或者之前執行過的任務使用的選項，來啟動任務。

13、工作量源

在工作量源頁面指定進行分析的工作量，支援的工作量源如下：

當前和最近的 SQL 活動
使用現有的 SQL 最佳化集
從下列方案和表建立假想工作量

應用過濾器可以縮小工作量中找到的語句的範圍。使用過濾器有兩個好處。首先, 它可以指示“指導”根據工作量中特定語句子集提出建議案, 從而產生更高質量的建議案。其次, 移去工作量中無關的語句可以顯著減少處理時間。

過濾選項有：

消耗資源最多的 SQL 語句
使用者、模組及操作
表

14、建議案選項

從“建議的訪問結構”區域選擇訪問方式，可以選擇索引、物化檢視以及分割槽，預設是選擇索引。也可以都不選擇，僅對現有結構進行分析。

可在限制模式或綜合模式下執行。限制模式是指處理最高成本語句後迅速返回, 有可能忽略成本低於特定閾值的語句。而在綜合模式下則執行詳盡分析。

15、建議案選項

高階選項包括工作量類別、空間限制、最佳化優先順序以及預設儲存位置。

工作量類別中，如果是資料倉儲工作量, 選擇工作量不穩定性下的“只考慮查詢”核取方塊，如果工作量代表所有訪問結構用例時，選擇工作量範圍下的“建議刪除未使用的訪問結構”核取方塊。

空間限制中，可以選擇不限制建議的索引和物化檢視所使用的附加空間，也可以指定將附加空間限制為多少M，設定為零或負數表示建議刪除現有的訪問結構, 以便為更好的訪問結構留出空間。

最佳化優先順序中，選擇確定最佳化 SQL 語句優先順序的依據，如果選擇“考慮訪問結構建立成本建議案”核取方塊，SQL 訪問指導將針對 SQL 語句執行時間的頻率和改善可能性來衡量建立訪問結構的成本。如果未選中, 將忽略建立成本。如果您不需要為並非頻繁執行的語句生成的特定建議案, 則選中此核取方塊。

預設儲存位置中，預設情況下, 索引將被放置在它們所引用的表的方案和表空間中, 而實體化檢視將被放置在查詢中引用的第一個表的方案和表空間中, 實體化檢視日誌將被放置在它們所引用的表方案的預設表空間中。使用相應的欄位可以更改這些預設位置。

16、檢視建議

使用指導中心頁，可以列出所有SQL訪問指導完成的任務。選擇一個建議，然後點選檢視結果按鈕，以獲得指導結果的概述。該網頁的圖表和統計資料提供整體的工作負載效能和查詢執行時間潛在的改進建議。可以使用該頁面顯示語句計數和推薦運算元。

點選頁面上的其他三個選項卡：建議案，SQL語句或者詳細資料檢視指定任務的其他方面。

建議案頁面列出了一些建議案, 這些建議案最初是按最大成本改善進行排序的。實施排在最前面的建議案將最大程度地改善整體效能。

17、SQL效能分析程式：概覽

Oracle資料庫11g包括SQL效能分析程式，可以測試和分析變更對 SQL 最佳化集中包含的 SQL 執行效能的影響。SQL效能分析程式可以幫助你預測對SQL查詢的工作量潛在變化的影響。此功能為DBA提供了SQL語句效能的詳細資訊，如之前和之後執行統計資料，效能提升或者下降的語句。

18、SQL效能分析器：使用案例

對於任何資料庫環境變化影響的SQL執行計劃，SQL效能分析程式可以用來預測和防止潛在的效能問題。這些環境變化包括（但不限於）以下：

資料庫升級
調優建議的實施
模式變化
統計資料收集
資料庫引數變化
作業系統和硬體更改

可以在複雜的環境中使用SQL效能分析程式預測SQL效能的變化。隨著應用程式進入開發生命週期，資料庫應用程式開發人員可以測試模式，資料庫物件的變化，並重寫應用程式，以減輕任何潛在的效能影響。

可以透過EM或使用dbms_sqlpa包訪問SQL效能分析程式。

19、使用SQL效能分析程式

使用SQL效能分析程式的步驟：

（1）收集SQL：在生成系統上搜集有代表性的SQL語句。

（2）傳輸：必須將所得的工作量傳輸給測試系統。從生產系統匯出STS然後匯入到測試系統。

（3）計算改變前的效能：在任何變化發生前，執行SQL語句，收集對後續有效能影響的改變的基準資訊。

（4）做出改變：執行改變，檢視對效能的影響。

（5）計算改變後的效能：在資料庫環境更改後，模擬執行SQL語句，收集與步驟3相同的資訊。

（6）比較和分析SQL效能：在有了改變前後的效能資料之後，可以進行效能比較分析。

（7）調優SQL：在這個階段，你已經確定了哪些SQL語句可能會導致效能問題時。可以使用任何資料庫工具來調優系統。實施任何調優動作後，應該重複該過程以建立新的版本，並分析效能差異，以確保新的效能是可以接受的。

例子：使用SQL效能分析程式評估引數變化的影響

建立SQL最佳化集

OCP課程56：管理II之SQL調優

相關文章