使用SQL tuning advisor(STA)自動最佳化SQL

      Oracle 10g之後的最佳化器支援兩種模式，一個是normal模式，一個是tuning模式。在大多數情況下，最佳化器處於normal模式。基於CBO的normal模式只考慮很小部分的執行計畫集合用於選擇哪個執行計畫，因為它需要在儘可能短的時間，通常是幾秒或毫秒級來對當前的SQL語句進行解析並產生執行計畫。因此並不能保證SQL語句每次都是使用最佳的執行計畫。而tuning模式則將高負載的SQL語句直接扔給最佳化器，最佳化器來自動對其進行詳細的分析，調試並給出建議，這就是Oracle 提供的Automatic Tuning Optimizer，即自動調整最佳化器。Oracle 自動調整最佳化器通過SQL調優建議器(SQL tuning advisor)來體現。

 

1、SQL tuning的基本步驟
     a、鑒別需要調整的高負載SQL或者Top SQL
     b、尋找可改進的執行計畫
     c、實施能夠改進的執行計畫以提高SQL效率
  
2、如何tuning SQL
     a、檢查是否為最佳化器設定了合理的參數(optimizer_mode,optimizer_index_caching,optimizer_index_cost_adj,以及相關cache size)
     b、檢查SQL語句所涉及的對象是否存在過時的統計資訊或者傾斜列是否缺少長條圖等
     c、通過添加提示來引導SQL語句使用正確的訪問路徑，以及串連方式等
     d、重構等價的SQL語句以使得SQL更高效(如最小化基表及中間結果集，避免列運算，列上的函數，null值，不等運算使得索引失效)
     e、添加合理的索引或物化視圖以及移除冗餘索引,分散I/O等
  
3、Automatic Tuning Optimizer 做什麼?
     a、分析統計資訊
         最佳化器執行計畫產生期間記錄當前SQL語句涉及對象的統計資訊的類型以及哪些被使用或哪些是需要的
         當統計資訊記錄完成後自動調整最佳化器會比對與查詢相關的這些對象的統計資訊是否可用或過時或非均衡列缺少長條圖等
         針對上述的操作之後得到哪些對象沒有統計資訊以及哪些對象缺少統計資訊以及額外的統計資訊用於產生report     
     b、分析訪問路徑
         最佳化器會分析當前SQL所使用的訪問路徑是否合理，也就是分析基於表的訪問方式，如全表掃描，索引掃描等
         自動調整最佳化器會基於謂詞嘗試假設性的推斷來建立合理的索引，也就是建議通過添加或修改相應的索引來提高效能
     c、SQL結構分析
         最佳化器會建議對於一些具有較大影響的SQL語句作結構性調整及轉換(基於內部規則)，如未嵌套的子查詢，重寫物化視圖，視圖合并等
         基於文法以及語義結構的分析與調整，如謂詞列上的運算，UNION與UNION ALL的使用，NOT IN, NOT EXIST之間替換等
         對中間結果集以及串連方式等實現一些預估的分析
     d、SQL profiling
         SQL profiling 內建於最佳化器，就是一個剖析工具，基於上述得到的資訊對當前的SQL進行剖析，以檢查出導致效能糟糕的故障點
         所有上述分析得到的結果以及輔助資訊最後以sql profile的形式表現出來，供使用者來判斷是否接受
         當使用者接受這些profile，下次處於normal模式時，相同的sql語句會使用這個profile
         可以對profile進行啟用，停用，以及修改，因此即使表發生較大的變化，profile依舊能使得SQL受益

 

4、Automatic Tuning Optimizer與SQL tuning advisor結構圖 

 

5、STA可tuning的方式
     STA提供OEM圖形介面以及API方式進行tuning，本文主要描述API即dbms_sqltune.create_tuning_task方式
     下面是可被create_tuning_task接受的API方式
       a、直接提供SQL語句文本
       b、引用共用池中的SQL語句(sql_id)
       c、引用awr自動工作負載中的SQL語句(sql_id)
       d、建議SQL調優集(批量tuning)
  
6、示範SQL tuning 

--環境scott@ORA11G> select * from v$version where rownum<2;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production--建立示範表 scott@ORA11G> CREATE TABLE t  2  NOLOGGING  3  AS  4     SELECT *  5       FROM dba_source,  6            (    SELECT *  7                   FROM DUAL  8             CONNECT BY ROWNUM < 5);Table created.--執行SQL 陳述式scott@ORA11G> SELECT COUNT (*)  2    FROM t a  3   WHERE a.ROWID > (SELECT MIN (b.ROWID)  4                      FROM t b  5                     WHERE a.owner = b.owner AND a.name = b.name AND a.TYPE = b.TYPE AND a.line = b.line);  COUNT(*)----------   18727561 row selected.--開始SQL自動調整並報告結果--指令碼tune_last_sql.sql中包含了建立調優任務、開始執行調優、以及報告調優成果。指令碼內容見文章尾部scott@ORA11G> @tune_last_sqlRECS-----------------------------------------------------------------------------------------GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name   : TASK_833Tuning Task Owner  : SCOTTWorkload Type      : Single SQL StatementScope              : COMPREHENSIVETime Limit(seconds): 1800Completion Status  : COMPLETEDStarted at         : 05/22/2013 15:06:06Completed at       : 05/22/2013 15:07:17-------------------------------------------------------------------------------Schema Name: SCOTTSQL ID     : 44tg722u0ypqhSQL Text   : SELECT COUNT (*)               FROM t a              WHERE a.ROWID > (SELECT MIN (b.ROWID)                                 FROM t b                                WHERE a.owner = b.owner AND a.name = b.name             AND a.TYPE = b.TYPE AND a.line = b.line)-------------------------------------------------------------------------------FINDINGS SECTION (1 finding)-------------------------------------------------------------------------------1- Statistics Finding---------------------  Table "SCOTT"."T" was not analyzed.  Recommendation  --------------  - Consider collecting optimizer statistics for this table.    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'T',            estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>            'FOR ALL COLUMNS SIZE AUTO');  Rationale  ---------    The optimizer requires up-to-date statistics for the table in order to    select a good execution plan.-------------------------------------------------------------------------------EXPLAIN PLANS SECTION-------------------------------------------------------------------------------1- Original-----------Plan hash value: 1985065416-----------------------------------------------------------------------------------------| Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |         |     1 |   134 |       | 42648   (1)| 00:08:32 ||   1 |  SORT AGGREGATE       |         |     1 |   134 |       |            |          ||*  2 |   HASH JOIN           |         |   129K|    16M|   195M| 42648   (1)| 00:08:32 ||   3 |    TABLE ACCESS FULL  | T       |  2590K|   165M|       | 11596   (1)| 00:02:20 ||   4 |    VIEW               | VW_SQ_1 |  2590K|   165M|       | 11674   (1)| 00:02:21 ||   5 |     HASH GROUP BY     |         |  2590K|   165M|       | 11674   (1)| 00:02:21 ||   6 |      TABLE ACCESS FULL| T       |  2590K|   165M|       | 11596   (1)| 00:02:20 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("A"."OWNER"="ITEM_1" AND "A"."NAME"="ITEM_2" AND              "A"."TYPE"="ITEM_3" AND "A"."LINE"="ITEM_4")       filter("A".ROWID>"MIN(B.ROWID)")--上面的report總共分為3個部分，分別是SQL調優的基本資料、SQL調優的建議findings、以及SQL對應的執行計畫部分--在基本資料部分包含了SQL調優的任務名稱，狀態，執行，完成時間，對應的SQL完整語句等--在finding部分則給出本次調優所得到的成果，如本次是提示缺少統計資訊--在執行計畫部分則給出了當前SQL語句的執行計畫以及謂詞資訊-->接下來根據建議來收集統計資訊scott@ORA11G> BEGIN  2     DBMS_STATS.gather_table_stats (ownname            => 'SCOTT',  3                                    tabname            => 'T',  4                                    estimate_percent   => DBMS_STATS.auto_sample_size,  5                                    method_opt         => 'FOR ALL COLUMNS SIZE AUTO');  6  END;  7  /PL/SQL procedure successfully completed.-->對原SQL語句增加order提示並執行scott@ORA11G> SELECT /*+ ordered */COUNT (*)  2    FROM t a  3   WHERE a.ROWID > (SELECT MIN (b.ROWID)  4                      FROM t b  5                     WHERE a.owner = b.owner AND a.name = b.name AND a.TYPE = b.TYPE AND a.line = b.line);  COUNT(*)----------   18727561 row selected.--再次調優SQL語句scott@ORA11G> @tune_last_sqlRECS-----------------------------------------------------------------------------------------------GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name   : TASK_849Tuning Task Owner  : SCOTTWorkload Type      : Single SQL StatementScope              : COMPREHENSIVETime Limit(seconds): 1800Completion Status  : COMPLETEDStarted at         : 05/22/2013 21:26:07Completed at       : 05/22/2013 21:26:42-------------------------------------------------------------------------------Schema Name: SCOTTSQL ID     : fsp3852n56gf8SQL Text   : SELECT /*+ ordered */COUNT (*)             FROM t a             WHERE a.ROWID > (SELECT MIN (b.ROWID) from t b             WHERE a.owner = b.owner AND a.name = b.name AND a.TYPE = b.TYPE             AND a.line = b.line)-------------------------------------------------------------------------------FINDINGS SECTION (1 finding)-------------------------------------------------------------------------------1- SQL Profile Finding (see explain plans section below)--------------------------------------------------------  A potentially better execution plan was found for this statement.  Recommendation (estimated benefit: 67.95%)  ------------------------------------------  - Consider accepting the recommended SQL profile.    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_849',            task_owner => 'SCOTT', replace => TRUE);-------------------------------------------------------------------------------EXPLAIN PLANS SECTION-------------------------------------------------------------------------------1- Original With Adjusted Cost------------------------------Plan hash value: 2929971977--------------------------------------------------------------------------------------------| Id  | Operation              | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |--------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT       |           |     1 |       |       |   218K  (1)| 00:43:47 ||   1 |  SORT AGGREGATE        |           |     1 |       |       |            |          ||   2 |   VIEW                 | VM_NWVW_2 |   551K|       |       |   218K  (1)| 00:43:47 ||*  3 |    FILTER              |           |       |       |       |            |          ||   4 |     HASH GROUP BY      |           |   551K|    51M|  1197M|   218K  (1)| 00:43:47 ||*  5 |      HASH JOIN         |           |    11M|  1031M|   145M| 37646   (1)| 00:07:32 ||   6 |       TABLE ACCESS FULL| T         |  2497K|   116M|       | 11596   (1)| 00:02:20 ||   7 |       TABLE ACCESS FULL| T         |  2497K|   116M|       | 11596   (1)| 00:02:20 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - filter("A".ROWID>MIN("B".ROWID))   5 - access("A"."OWNER"="B"."OWNER" AND "A"."NAME"="B"."NAME" AND              "A"."TYPE"="B"."TYPE" AND "A"."LINE"="B"."LINE")2- Using SQL Profile--------------------Plan hash value: 1985065416-----------------------------------------------------------------------------------------| Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |         |     1 |   116 |       | 70117   (1)| 00:14:02 ||   1 |  SORT AGGREGATE       |         |     1 |   116 |       |            |          ||*  2 |   HASH JOIN           |         |  2025K|   224M|   145M| 70117   (1)| 00:14:02 ||   3 |    TABLE ACCESS FULL  | T       |  2497K|   116M|       | 11596   (1)| 00:02:20 ||   4 |    VIEW               | VW_SQ_1 |  2497K|   159M|       | 41851   (1)| 00:08:23 ||   5 |     HASH GROUP BY     |         |  2497K|   116M|   153M| 41851   (1)| 00:08:23 ||   6 |      TABLE ACCESS FULL| T       |  2497K|   116M|       | 11596   (1)| 00:02:20 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("A"."OWNER"="ITEM_1" AND "A"."NAME"="ITEM_2" AND              "A"."TYPE"="ITEM_3" AND "A"."LINE"="ITEM_4")       filter("A".ROWID>"MIN(B.ROWID)")---------------------------------------------------------------------------------針對上述的SQL語句，SQL調優器找到了一個更為高效的執行計畫，並提示我們接受該執行計畫，如下--A potentially better execution plan was found for this statement.--Recommendation (estimated benefit: 67.95%)--Consider accepting the recommended SQL profile--Author : Robinson--Blog   : http://blog.csdn.net/robinson_0612--接受SQL profilescott@ORA11G> exec DBMS_SQLTUNE.accept_sql_profile (task_name => 'TASK_849', task_owner => 'SCOTT', REPLACE => TRUE);PL/SQL procedure successfully completed.--當接受SQL profile後，我們再次來執行原來帶order提示的SQL語句scott@ORA11G> set autot trace exp;scott@ORA11G> SELECT /*+ ordered */COUNT (*)  2               FROM t a  3               WHERE a.ROWID > (SELECT MIN (b.ROWID) from t b  4               WHERE a.owner = b.owner AND a.name = b.name AND a.TYPE = b.TYPE  5               AND a.line = b.line);Execution Plan----------------------------------------------------------Plan hash value: 1985065416-----------------------------------------------------------------------------------------| Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |         |     1 |   116 |       | 70117   (1)| 00:14:02 ||   1 |  SORT AGGREGATE       |         |     1 |   116 |       |            |          ||*  2 |   HASH JOIN           |         |  2025K|   224M|   145M| 70117   (1)| 00:14:02 ||   3 |    TABLE ACCESS FULL  | T       |  2497K|   116M|       | 11596   (1)| 00:02:20 ||   4 |    VIEW               | VW_SQ_1 |  2497K|   159M|       | 41851   (1)| 00:08:23 ||   5 |     HASH GROUP BY     |         |  2497K|   116M|   153M| 41851   (1)| 00:08:23 ||   6 |      TABLE ACCESS FULL| T       |  2497K|   116M|       | 11596   (1)| 00:02:20 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("A"."OWNER"="ITEM_1" AND "A"."NAME"="ITEM_2" AND              "A"."TYPE"="ITEM_3" AND "A"."LINE"="ITEM_4")       filter("A".ROWID>"MIN(B.ROWID)")Note-----   - SQL profile "SYS_SQLPROF_013ecc70b5f70000" used for this statementscott@ORA11G> set autot off;--上面的autotrace中，最後一部分表明當前的SQL語句使用了儲存的SQL profile的執行計畫

7、相關視圖
     DBA_ADVISOR_LOG
     DBA_ADVISOR_TASKS
     DBA_ADVISOR_FINDINGS
     DBA_ADVISOR_RECOMMENDATIONS
     DBA_ADVISOR_RATIONALE
     DBA_SQLTUNE_STATISTICS
     DBA_SQLTUNE_BINDS
     DBA_SQLTUNE_PLANS

8、示範用到的指令碼

SET ECHO OFF TERMOUT ON FEEDBACK OFF VERIFY OFF  SET SCAN ONSET LONG 1000000 LINESIZE 180COL recs FORMAT a135VARIABLE tuning_task VARCHAR2(30)DECLARE  l_sql_id v$session.prev_sql_id%TYPE;BEGIN  SELECT prev_sql_id INTO l_sql_id  FROM v$session  WHERE audsid = userenv('SESSIONID');    :tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);  dbms_sqltune.execute_tuning_task(:tuning_task);END;/SELECT dbms_sqltune.report_tuning_task(:tuning_task) as recs FROM dual;SET VERIFY ON FEEDBACK ON 

 

更多參考

DML Error Logging 特性 

PL/SQL --> 遊標

PL/SQL --> 隱式遊標(SQL%FOUND)

批量SQL之 FORALL 語句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化與賦值

PL/SQL 聯合數組與巢狀表格
PL/SQL 變長數組
PL/SQL --> PL/SQL記錄

SQL tuning 步驟

高效SQL語句必殺技

父遊標、子遊標及共用遊標

綁定變數及其優缺點

dbms_xplan之display_cursor函數的使用

dbms_xplan之display函數的使用

執行計畫中各欄位各模組描述

使用 EXPLAIN PLAN 擷取SQL語句執行計畫