sql turning advise的使用

             sql turning advise（sta）

SQL優化器SQL Tuning Advisor （STA)，是oracle的sql優化補助工具。

其實優化sql主要有兩個方案：

其一是改寫sql本身，改寫sql需要對sql語法、數據庫的執行方式都要有較好地理解。

其二就是這個STA，它屬于DBMS_SQLTUNE包，它的主要作用是對于sql使用到的表創建正確的索引。

使用STA前提：

 要保證優化器是CBO模式下。

show parameterOPTIMIZER_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 toscott;

下面通過案例詳細介紹該工具的具體使用：

1：創建案例用戶并授權

SQL> createuser lanniao identified by lanniao;

用戶已創建。

SQL> grantconnect,resource to lanniao;

授權成功。

SQL> grantadvisor to lanniao;

授權成功。

2：創建測試表

SQL> createtable bigtab as select rownum as id,a.* from sys.all_objects a;

表已創建。

SQL> createtable smalltab as select rownum as id,a.* from sys.all_tables a;

表已創建。

然后多運行幾次下面的腳本，增加表里的數據：

SQL> insertinto bigtab  select rownum as id,a.* fromsys.all_objects a;

已創建55637行。

SQL> insertinto bigtab  select rownum as id,a.* fromsys.all_objects a;

已創建55637行。

SQL> insertinto bigtab  select rownum as id,a.* fromsys.all_objects a;

已創建55637行。

SQL> commit;

提交完成。

     這里創建一張大表和一張小表，并且都沒有索引，下面執行一個查詢：

SQL> conn /as sysdba

已連接。

SQL> grantdba to lanniao;

授權成功。

SQL>conn  lanniao/lanniao

已連接。

SQL> settiming on

SQL> setautot on

SQL>  select count(*) from bigtab a, smalltab bwhere a.object_name=b.table_name;

  COUNT(*)

----------

       752

已用時間:  00: 00: 00.32

執行計劃

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

Plan hashvalue: 3089226980

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

| Id  | Operation           | Name     | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |         |     1 |    34 |  966   (1)| 00:00:12 |

|   1 | SORT AGGREGATE     |          |    1 |    34 |            |          |

|*  2 |  HASH JOIN         |          |  155K|  5152K|   966  (1)| 00:00:12 |

|   3 |   TABLE ACCESS FULL| SMALLTAB | 2542 | 43214 |    32   (0)| 00:00:01 |

|   4 |   TABLE ACCESS FULL| BIGTAB   |   227K| 3778K|   932   (1)| 00:00:12 |

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

PredicateInformation (identified by operation id):

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

   2 -access("A"."OBJECT_NAME"="B"."TABLE_NAME")

Note

-----

   - dynamic sampling used for this statement(level=2)

統計信息

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

         73 recursive calls

          1 db block gets

       3683 consistent gets

        942 physical reads

        132 redo size

        535 bytes sent via SQL*Net to client

        523 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。

3: 使用STA對sql進行分析

3.1：創建優化任務

通過調用函數CREATE_TUNING_TASK來創建優化任務，調用存儲過程EXECUTE_TUNING_TASK執行該任務：

SQL> setautot off

SQL> settiming off

SQL> declare

  2 my_task_name varchar2(30);

  3 my_sqltext clob;

  4 begin

  5 my_sqltext:='select count(*) from bigtab a,smalltab b where

  6 a.object_name=b.table_name';

  7 my_task_name:=dbms_sqltune.create_tuning_task(

  8 sql_text =>my_sqltext,

  9 user_name =>'LANNIAO',

 10 scope =>'COMPREHENSIVE',

 11 time_limit =>60,

 12 task_name =>'tuning_sql_test',

 13 description =>'Task to tune a query on specified table');

 14 dbms_sqltune.execute_tuning_task(task_name =>'tuning_sql_test');

 15  end;

 16  /

PL/SQL 過程已成功完成。

函數CREATE_TUNING_TASK，

sql_text是需要優化的語句，

user_name是該語句通過哪個用戶執行，用戶名大寫，

scope是優化范圍（limited或comprehensive），

time_limit優化過程的時間限制，

task_name優化任務名稱，

description優化任務描述。

3.2: 執行優化任務

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

SQL> execdbms_sqltune.execute_tuning_task('tuning_sql_test');

PL/SQL 過程已成功完成。

3.3：檢查優化任務的狀態

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

SQL> setlinesize 1000

SQL> SELECTtask_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql_test';

TASK_NAME                  STATUS

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

tuning_sql_test                  COMPLETED

3.4: 查看優化結果

通過dbms_sqltune.report_tning_task函數可以獲得優化任務的結果。

SQL> set long999999

SQL> setserveroutput on size 999999

SQL> set line120

SQL> selectDBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

GENERALINFORMATION SECTION

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

Tuning TaskName   : tuning_sql_test

Tuning TaskOwner  : LANNIAO

WorkloadType      : Single SQL Statement

ExecutionCount    : 2

CurrentExecution  : EXEC_1056

Execution Type     : TUNE SQL

Scope              : COMPREHENSIVE

TimeLimit(seconds): 60

CompletionStatus  : COMPLETED

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

Started at         : 04/14/2014 16:42:18

Completedat       : 04/14/2014 16:42:19

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

Schema Name:LANNIAO

SQL ID     : 9n5grk4kh8ndq

SQL Text   : select count(*) from bigtab a,smalltab bwhere

             a.object_name=b.table_name

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

FINDINGS SECTION(3 findings)

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

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

1- StatisticsFinding

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

  尚未分析表"LANNIAO"."SMALLTAB"。

  Recommendation

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

  - 考慮收集此表的優化程序統計信息。

    execute dbms_stats.gather_table_stats(ownname=> 'LANNIAO', tabname =>

            'SMALLTAB', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

            method_opt => 'FOR ALL COLUMNSSIZE AUTO');

  Rationale

  ---------

    為了選擇好的執行計劃, 優化程序需要此表的最新統計信息。

2- StatisticsFinding

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

  尚未分析表"LANNIAO"."BIGTAB"。

  Recommendation

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

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

  - 考慮收集此表的優化程序統計信息。

    executedbms_stats.gather_table_stats(ownname => 'LANNIAO', tabname =>

            'BIGTAB', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,

            method_opt => 'FOR ALL COLUMNSSIZE AUTO');

  Rationale

  ---------

    為了選擇好的執行計劃, 優化程序需要此表的最新統計信息。

3- Index Finding(see explain plans section below)

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

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

  通過創建一個或多個索引可以改進此語句的執行計劃。

  Recommendation (estimated benefit: 90.48%)

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

  - 考慮運行可以改進物理方案設計的訪問指導或者創建推薦的索引。

    create index LANNIAO.IDX$$_04050001 onLANNIAO.SMALLTAB("TABLE_NAME");

  - 考慮運行可以改進物理方案設計的訪問指導或者創建推薦的索引。

    create index LANNIAO.IDX$$_04050002 onLANNIAO.BIGTAB("OBJECT_NAME");

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

  Rationale

  ---------

    創建推薦的索引可以顯著地改進此語句的執行計劃。但是, 使用典型的 SQL 工作量運

行 "訪問指導"

    可能比單個語句更可取。通過這種方法可以獲得全面的索引建議案, 包括計算索引維護

的開銷和附加的空間消耗。

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

EXPLAIN PLANSSECTION

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

1- Original

-----------

Plan hash value:3089226980

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

| Id  | Operation           | Name     | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |         |     1 |    34 |  966   (1)| 00:00:12 |

|   1 | SORT AGGREGATE     |          |    1 |    34 |            |          |

|*  2 |  HASH JOIN         |          |  155K|  5152K|   966  (1)| 00:00:12 |

|   3 |   TABLE ACCESS FULL| SMALLTAB | 2542 | 43214 |    32   (0)| 00:00:01 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

|   4 |   TABLE ACCESS FULL| BIGTAB   |   227K| 3778K|   932   (1)| 00:00:12 |

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

PredicateInformation (identified by operation id):

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

   2 -access("A"."OBJECT_NAME"="B"."TABLE_NAME")

2- Using NewIndices

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

Plan hash value:2901183249

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

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

---------

| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| T

ime     |

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

---------

|   0 | SELECT STATEMENT       |                |     1 |   34 |    92   (4)| 0

0:00:0

2 |

|   1 | SORT AGGREGATE        |                |     1 |   34 |            |

        |

|*  2 |  HASH JOIN            |                |   155K| 5152K|    92   (4)| 0

0:00:02 |

|   3 |   INDEX FAST FULL SCAN| IDX$$_04050001 | 2542 | 43214 |    12   (0)| 0

0:00:01 |

|   4 |   INDEX FAST FULL SCAN| IDX$$_04050002 |  227K|  3778K|    78  (2)| 0

0:00:0

1 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

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

---------

PredicateInformation (identified by operation id):

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

   2 -access("A"."OBJECT_NAME"="B"."TABLE_NAME")

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

看一下這個優化建議報告：

第一部分是關于這次優化任務的基本信息：如任務名稱、執行時間、范圍、涉及到的語句等等。

第二部分是關于這次優化任務的所找到的問題以及給出的優化建議。前面先給出了問題描述：收集表的統計信息及可以通過建立更多的索引來提高性能；然后是建議的具體內容：在表smalltab的字段table_name上創建索引，在表bigtab的字段object_name上創建索引；最后是相關注意事項：此次優化雖然給出了創建索引的建議，但是最好通過SQL訪問建議器(SQL Access Advisor SAA)結合整個數據庫的工作量來深入分析，那樣就能給出考慮了索引維護和空間消耗等因素的更加合理的建議。

最后，報告還給出了原有的查詢計劃，以及采用優化建議以后的查詢計劃的對比。可以看出COST值大大下降。

3.5：刪除優化任務

通過調用dbms_sqltuen.drop_tuning_task可以刪除已經存在的優化任務

SQL>execdbms_sqltune.drop_tuning_task('tuning_sql_test');

3.6：按照優化建議進行優化

首先要說明一點的是，最好不要直接按照優化器給出的建議直接優化。因為像建索引這種操作影響可不是這一條語句，我們這里只是驗證一下優化建議的效果。

按照建議，創建兩個索引：

SQL> createindex smalltab_idx1 on smalltab(table_name);

索引已創建。

SQL> createindex bigtab_idx1 on bigtab(object_name);

索引已創建。

SQL> analyzetable smalltab compute statistics;

表已分析。

SQL> analyzetable bigtab compute statistics;

表已分析。

SQL> settiming on

SQL> setautot on

SQL> selectcount(*) from bigtab a, smalltab b where a.object_name=b.table_name;

  COUNT(*)

----------

       752

已用時間:  00: 00: 00.05

執行計劃

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

Plan hash value:2594317117

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

--------

| Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Ti

me     |

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

--------

|   0 | SELECT STATEMENT       |               |     1 |   44 |   321   (2)| 00

:00:04 |

|   1 | SORT AGGREGATE        |               |     1 |   44 |            |

       |

|*  2 |  HASH JOIN            |               | 20689 |   888K|  321   (2)| 00

:00:04 |

|   3 |   INDEX FAST FULL SCAN| SMALLTAB_IDX1 | 2869 | 54511 |     5   (0)| 00

:00:01 |

|   4|    INDEX FAST FULL SCAN|BIGTAB_IDX1   |   222K| 5433K|   313   (1)| 00

:00:04 |

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

--------

PredicateInformation (identified by operation id):

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

   2 -access("A"."OBJECT_NAME"="B"."TABLE_NAME")

統計信息

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

          1 recursive calls

          0 db block gets

       1176 consistent gets

          3 physical reads

          0 redo size

        535 bytes sent via SQL*Net to client

        523 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          1 rows processed

可以看出，consistentgets比優化前大大下降了，優化建議確實提高了性能。Oracle10g讓優化變得如此簡單。