前言： 經(jīng)?？梢耘龅絻?yōu)化sql的需求，開發(fā)人員直接扔過來一個(gè)SQL讓DBA優(yōu)化，然后怎么辦？

在鼓樓等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作 網(wǎng)站設(shè)計(jì)制作按需定制設(shè)計(jì),公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),品牌網(wǎng)站建設(shè),成都營銷網(wǎng)站建設(shè),成都外貿(mào)網(wǎng)站制作,鼓樓網(wǎng)站建設(shè)費(fèi)用合理。

當(dāng)然，經(jīng)驗(yàn)豐富的DBA可以從各種方向下手，有時(shí)通過建立正確索引即可獲得很好的優(yōu)化效果，但是那些復(fù)雜SQL錯(cuò)綜復(fù)雜的表關(guān)聯(lián)，卻讓DBA們滿頭大汗。

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

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

使用STA前提：

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

如下是STA使用例子：

1.首先創(chuàng)建兩個(gè)練習(xí)表obj與ind，僅創(chuàng)建表，無需創(chuàng)建索引：

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

2.然后對這兩個(gè)表，obj與ind進(jìn)行聯(lián)合查詢，并通過autotrace查看其執(zhí)行計(jì)劃：

SQL> set timing onSQL> set autot trace
SQL> select count(*) from obj o, ind i where o.object_name=i.index_name;
已用時(shí)間:  00: 00: 00.15執(zhí)行計(jì)劃----------------------------------------------------------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)
統(tǒng)計(jì)信息----------------------------------------------------------
          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

通過執(zhí)行計(jì)劃，可以清晰的看到，在執(zhí)行以上兩個(gè)表的聯(lián)合查詢的時(shí)候，兩張表走的全表掃和hash join。

正式使用STA進(jìn)行優(yōu)化：
第一步：創(chuàng)建優(yōu)化任務(wù)
通過調(diào)用函數(shù)DBMS_SQLTUNE.CREATE_TUNING_TASK來創(chuàng)建優(yōu)化任務(wù)，調(diào)用存儲過程DBMS_SQLTUNE.EXECUTE_TUNING_TASK執(zhí)行該任務(wù)：

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 過程已成功完成。

如下是參數(shù)解釋：

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

第二步：  執(zhí)行優(yōu)化任務(wù)
通過調(diào)用dbms_sqltune.execute_tuning_task過程來執(zhí)行前面創(chuàng)建好的優(yōu)化任務(wù)。

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

第三步： 檢查優(yōu)化任務(wù)的狀態(tài)
通過查看user_advisor_tasks/dba_advisor_tasks視圖可以查看優(yōu)化任務(wù)的當(dāng)前狀態(tài)。

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

第 四 步：查看優(yōu)化結(jié)果
通過dbms_sqltune.report_tning_task函數(shù)可以獲得優(yōu)化任務(wù)的結(jié)果。

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

 如下是顯示優(yōu)化的結(jié)果：

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  --------------
  - 考慮收集此表的優(yōu)化程序統(tǒng)計(jì)信息。    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  ---------    為了選擇好的執(zhí)行計(jì)劃, 優(yōu)化程序需要此表的最新統(tǒng)計(jì)信息。2- Statistics Finding---------------------  尚未分析表 "SCOTT"."OBJ"。
  Recommendation  --------------
  - 考慮收集此表的優(yōu)化程序統(tǒng)計(jì)信息。    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  ---------    為了選擇好的執(zhí)行計(jì)劃, 優(yōu)化程序需要此表的最新統(tǒng)計(jì)信息。3- Index Finding (see explain plans section below)--------------------------------------------------  通過創(chuàng)建一個(gè)或多個(gè)索引可以改進(jìn)此語句的執(zhí)行計(jì)劃。
  Recommendation (estimated benefit: 75.74%)  ------------------------------------------
  - 考慮運(yùn)行可以改進(jìn)物理方案設(shè)計(jì)的訪問指導(dǎo)或者創(chuàng)建推薦的索引。    create index SCOTT.IDX$$_00790001 on SCOTT.OBJ("OBJECT_NAME");  - 考慮運(yùn)行可以改進(jìn)物理方案設(shè)計(jì)的訪問指導(dǎo)或者創(chuàng)建推薦的索引。    create index SCOTT.IDX$$_00790002 on SCOTT.IND("INDEX_NAME");
  Rationale  ---------    創(chuàng)建推薦的索引可以顯著地改進(jìn)此語句的執(zhí)行計(jì)劃。但是, 使用典型的 SQL 工作量運(yùn)行 "訪問指導(dǎo)"
    可能比單個(gè)語句更可取。通過這種方法可以獲得全面的索引建議案, 包括計(jì)算索引維護(hù)的開銷和附加的空間消耗。

-------------------------------------------------------------------------------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")

報(bào)告如上，

仔細(xì)閱讀此報(bào)告，主要給出了兩種建議：1.收集兩張表obj與ind的統(tǒng)計(jì)信息；2.在這兩張表上面創(chuàng)建相應(yīng)索引。

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

更加貼心的是，報(bào)告中把優(yōu)化前后的預(yù)計(jì)效果都展示了出來，完全一目了然，更加方便了DBA考慮是否進(jìn)行建議優(yōu)化方案。

五、刪除優(yōu)化任務(wù)
通過調(diào)用dbms_sqltuen.drop_tuning_task可以刪除已經(jīng)存在的優(yōu)化任務(wù)，可以釋放資源。

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

結(jié)語： 如上就是SQL優(yōu)化器 SQL Tuning Advisor  （STA）的使用方法，可能語法有些復(fù)雜，但是確實(shí)給優(yōu)化的工作帶來了很大幫助，經(jīng)常使用多加練習(xí)即可熟練。