Oracle SQL 優化之sql tuning advisor (STA)

前言： 經常可以碰到優化sql的需求，開發人員直接扔過來一個SQL讓DBA優化，然后怎么辦？

當然，經驗豐富的DBA可以從各種方向下手，有時通過建立正確索引即可獲得很好的優化效果，但是那些復雜SQL錯綜復雜的表關聯，卻讓DBA們滿頭大汗。

如下特別介紹一種oracle官方提供的科學優化方法STA，經過實踐，不敢說此特性絕對有效，但是可以開闊思路，并且從中學到許多知識，不再用“猜”的方式去創建索引了。

SQL優化器 SQL Tuning Advisor  （STA)，是oracle的sql優化補助工具。
其實優化sql主要有兩個方案，其一是改寫sql本身，改寫sql需要對sql語法、數據庫的執行方式都要有較好地理解。
其二就是這個STA，它屬于DBMS_SQLTUNE包，它的主要作用是對于sql使用到的表創建正確的索引。

使用STA前提：

要保證優化器是CBO模式下。
show parameter OPTIMIZER_MODE
all_rows  /*CBO，sql所有返回行都采用基于成本的方式運行*/first_rows  /*CBO，使用成本和試探法相結合的方法，查找一種可以最快返回前面少數行*/first_rows_n  /*CBO，全部采用基于成本的優化方法CBO，并以最快的速度，返回前N行記錄*/choose  /*如果有統計信息，采用CBO，否則采用RBO*/rule  /*RBO*/執行DBMS_SQLTUNE包進行sql優化需要有advisor的權限：grant advisor to scott;

如下是STA使用例子：

1.首先創建兩個練習表obj與ind，僅創建表，無需創建索引：

SQL> create table obj as select * from dba_objects;
表已創建。
SQL> create table ind as select * from dba_indexes;
表已創建。
SQL> insert into obj select * from obj;
已創建 74603 行。
SQL> insert into obj select * from obj;
已創建 149206 行。
SQL> insert into obj select * from obj;
已創建 298412 行。
SQL> insert into ind select * from ind;
已創建 5134 行。
SQL> insert into ind select * from ind;
已創建 10268 行。
SQL> insert into ind select * from ind;
已創建 20536 行。

2.然后對這兩個表，obj與ind進行聯合查詢，并通過autotrace查看其執行計劃：

SQL> set timing onSQL> set autot trace
SQL> select count(*) from obj o, ind i where o.object_name=i.index_name;
已用時間:  00: 00: 00.15執行計劃----------------------------------------------------------Plan hash value: 380737209------------------------------------------------------------------------------------| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |      |     1 |    83 |       |  5063   (1)| 00:01:01 ||   1 |  SORT AGGREGATE     |      |     1 |    83 |       |            |          ||*  2 |   HASH JOIN         |      |  5861K|   463M|  1272K|  5063   (1)| 00:01:01 ||   3 |    TABLE ACCESS FULL| IND  | 44789 |   743K|       |   379   (1)| 00:00:05 ||   4 |    TABLE ACCESS FULL| OBJ  |   577K|    36M|       |  2472   (1)| 00:00:30 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------
   2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")
Note-----
   - dynamic sampling used for this statement (level=2)
統計信息----------------------------------------------------------
          9  recursive calls          4  db block gets      10406  consistent gets          0  physical reads          0  redo size        425  bytes sent via SQL*Net to client        415  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

通過執行計劃，可以清晰的看到，在執行以上兩個表的聯合查詢的時候，兩張表走的全表掃和hash join。

正式使用STA進行優化：
第一步：創建優化任務
通過調用函數DBMS_SQLTUNE.CREATE_TUNING_TASK來創建優化任務，調用存儲過程DBMS_SQLTUNE.EXECUTE_TUNING_TASK執行該任務：

SQL> set autot offSQL> set timing offDECLAREmy_task_name VARCHAR2(30);
my_sqltext  CLOB;BEGINmy_sqltext := 'select count(*) from obj o, ind i where o.object_name=i.index_name';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text    => my_sqltext,user_name   => 'SCOTT', 
scope       => 'COMPREHENSIVE',
time_limit  => 30,
task_name   => 'tuning_sql_test',
description => 'tuning');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');END;/PL/SQL 過程已成功完成。

如下是參數解釋：

函數CREATE_TUNING_TASK，
sql_text是需要優化的語句，
user_name是該語句通過哪個用戶執行，用戶名大寫，
scope是優化范圍（limited或comprehensive），
time_limit優化過程的時間限制，
task_name優化任務名稱，
description優化任務描述。

第二步：  執行優化任務
通過調用dbms_sqltune.execute_tuning_task過程來執行前面創建好的優化任務。

SQL> exec dbms_sqltune.execute_tuning_task('tuning_sql_test');
PL/SQL 過程已成功完成。

第三步： 檢查優化任務的狀態
通過查看user_advisor_tasks/dba_advisor_tasks視圖可以查看優化任務的當前狀態。

SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql_test';
TASK_NAME         STATUS------------------------------ -----------tuning_sql_test         COMPLETED

第 四 步：查看優化結果
通過dbms_sqltune.report_tning_task函數可以獲得優化任務的結果。

SQL> set long 999999SQL> set serveroutput on size 999999SQL> set line 120SQL> select DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from dual;

 如下是顯示優化的結果：

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')--------------------------------------------------------------------------------GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name   : tuning_sql_test
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Execution Count    : 2Current Execution  : EXEC_112
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 30Completion Status  : COMPLETED
Started at         : 08/29/2013 11:10:10Completed at       : 08/29/2013 11:10:12-------------------------------------------------------------------------------Schema Name: SCOTT
SQL ID     : 6wruu2mxyu8g3
SQL Text   : select count(*) from obj o, ind i where
             o.object_name=i.index_name-------------------------------------------------------------------------------FINDINGS SECTION (3 findings)-------------------------------------------------------------------------------1- Statistics Finding---------------------  尚未分析表 "SCOTT"."IND"。
  Recommendation  --------------
  - 考慮收集此表的優化程序統計信息。    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'IND', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');
  Rationale  ---------    為了選擇好的執行計劃, 優化程序需要此表的最新統計信息。2- Statistics Finding---------------------  尚未分析表 "SCOTT"."OBJ"。
  Recommendation  --------------
  - 考慮收集此表的優化程序統計信息。    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'OBJ', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');
  Rationale  ---------    為了選擇好的執行計劃, 優化程序需要此表的最新統計信息。3- Index Finding (see explain plans section below)--------------------------------------------------  通過創建一個或多個索引可以改進此語句的執行計劃。
  Recommendation (estimated benefit: 75.74%)  ------------------------------------------
  - 考慮運行可以改進物理方案設計的訪問指導或者創建推薦的索引。    create index SCOTT.IDX$$_00790001 on SCOTT.OBJ("OBJECT_NAME");  - 考慮運行可以改進物理方案設計的訪問指導或者創建推薦的索引。    create index SCOTT.IDX$$_00790002 on SCOTT.IND("INDEX_NAME");
  Rationale  ---------    創建推薦的索引可以顯著地改進此語句的執行計劃。但是, 使用典型的 SQL 工作量運行 "訪問指導"
    可能比單個語句更可取。通過這種方法可以獲得全面的索引建議案, 包括計算索引維護的開銷和附加的空間消耗。

-------------------------------------------------------------------------------EXPLAIN PLANS SECTION-------------------------------------------------------------------------------1- Original-----------Plan hash value: 380737209------------------------------------------------------------------------------------| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time|------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |      |     1 |    83 |       |  5063   (1)| 00:01:01 ||   1 |  SORT AGGREGATE     |      |     1 |    83 |       |            |          ||*  2 |   HASH JOIN         |      |  5861K|   463M|  1272K|  5063   (1)| 00:01:01 ||   3 |    TABLE ACCESS FULL| IND  | 44789 |   743K|       |   379   (1)| 00:00:05 ||   4 |    TABLE ACCESS FULL| OBJ  |   577K|    36M|       |  2472   (1)| 00:00:30 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------
   2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")2- Using New Indices--------------------Plan hash value: 4048334321--------------------------------------------------------------------------------------------------| Id  | Operation               | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |--------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT        |                |     1 |    83 |       |  1228   (2)| 00:00:15 ||   1 |  SORT AGGREGATE         |                |     1 |    83 |       |            |||   2 |   MERGE JOIN            |                |  5861K|   463M|       |  1228   (2)| 00:00:15 ||   3 |    INDEX FULL SCAN      | IDX$$_00790001 |   577K|    36M|       |   944   (1)| 00:00:12 ||*  4 |    SORT JOIN            |                | 44789 |   743K|  2120K|   268   (1)| 00:00:04 ||   5 |     INDEX FAST FULL SCAN| IDX$$_00790002 | 44789 |   743K|       |    18   (0)| 00:00:01 |--------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------
   4 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")
       filter("O"."OBJECT_NAME"="I"."INDEX_NAME")

報告如上，

仔細閱讀此報告，主要給出了兩種建議：1.收集兩張表obj與ind的統計信息；2.在這兩張表上面創建相應索引。

并且特別貼心的把語句都給了出來，完全可以拿去直接運行，

更加貼心的是，報告中把優化前后的預計效果都展示了出來，完全一目了然，更加方便了DBA考慮是否進行建議優化方案。

五、刪除優化任務
通過調用dbms_sqltuen.drop_tuning_task可以刪除已經存在的優化任務，可以釋放資源。

SQL>exec dbms_sqltune.drop_tuning_task('tuning_sql_test');

結語： 如上就是SQL優化器 SQL Tuning Advisor  （STA）的使用方法，可能語法有些復雜，但是確實給優化的工作帶來了很大幫助，經常使用多加練習即可熟練。