Oracle 10G推出了強大的SQL優化工具：SQL Tuning Advisor，使用該功能必須保證優化器是CBO模式，對SQL進行優化需要執行DBMS_SQLTUNE包，因而需要advisor許可權。

    舉個例子介紹如何優化一條發現問題的語句

1.建立測試環境

HZH@hzh%11gR2>create table hzh1 as select * from dba_objects;

HZH@hzh%11gR2>create table hzh2 as select * from dba_objects;

HZH@hzh%11gR2>insert into hzh1 select * from dba_objects;

HZH@hzh%11gR2>insert into hzh1 select * from dba_objects;

HZH@hzh%11gR2>insert into hzh1 select * from dba_objects;

HZH@hzh%11gR2>insert into hzh2 select * from dba_objects;

HZH@hzh%11gR2>insert into hzh2 select * from dba_objects;

HZH@hzh%11gR2>insert into hzh2 select * from dba_objects;

HZH@hzh%11gR2>set timing on

HZH@hzh%11gR2>set autot on

HZH@hzh%11gR2>select count(*) from hzh1 a,hzh2 b where a.object_name=b.object_name;

  COUNT(*)

----------

   2138860

Elapsed: 00:00:00.36

Execution Plan

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

Plan hash value: 679460921

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

----

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

   |

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

----

|   0 | SELECT STATEMENT    |      |     1 |   132 |       |  4929   (2)| 00:01:

00 |

|   1 |  SORT AGGREGATE     |      |     1 |   132 |       |            |

   |

|*  2 |   HASH JOIN         |      |    16M|  2124M|    22M|  4929   (2)| 00:01:

00 |

|   3 |    TABLE ACCESS FULL| HZH1 |   302K|    19M|       |  1204   (1)| 00:00:

15 |

|   4 |    TABLE ACCESS FULL| HZH2 |   364K|    22M|       |  1204   (1)| 00:00:

15 |

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

----

Predicate Information (identified by operation id):

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

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

Note

-----

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

Statistics

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

         54  recursive calls

          2  db block gets

       8962  consistent gets

       2178  physical reads

        308  redo size

        425  bytes sent via SQL*Net to client

        419  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

2.建立優化任務

呼叫函式CREATE_TUNING_TASK來建立優化任務；

呼叫儲存過程EXECUTE_TUNING_TASK執行該任務；

HZH@hzh%11gR2> set autot off

HZH@hzh%11gR2> set timing off

HZH@hzh%11gR2>DECLARE

2 kingsql_me VARCHAR2(30);

3 sqltext_me CLOB;

4 BEGIN

5 sqltext_me := 'select count(*) from hzh1 a,hzh2 b where a.object_name=b.object_name';

6 kingsql_me := DBMS_SQLTUNE.CREATE_TUNING_TASK(

7 sql_text => sqltext_me,

8 user_name => 'HZH',

9 scope => 'COMPREHENSIVE',

10 time_limit => 60,

11 task_name => 'tuning_kingsql_test',

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

13 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_kingsql_test');

14 END;

/

PL/SQL procedure successfully completed.

3.執行優化任務

呼叫dbms_sqltune.execute_tuning_task過程來執行前面建立好的優化任務。

HZH@hzh%11gR2>exec dbms_sqltune.execute_tuning_task('tuning_kingsql_test');

PL/SQL procedure successfully completed.

4.檢查優化任務的狀態

通過檢視dba_advisor_task或者suser_advisor_tasks可以檢查優化任務的狀態

HZH@hzh%11gR2>SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_kingsql_test';

TASK_NAME                      STATUS

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

tuning_kingsql_test            COMPLETED

5.檢視優化結果

查詢dbms_sqltune.report_tning_task函式可以獲得優化任務的結果

HZH@hzh%11gR2>set long 888888

HZH@hzh%11gR2>set serveroutput on size 888888

HZH@hzh%11gR2>set lines 100

HZH@hzh%11gR2>select dbms_sqltune.report_tuning_task('tuning_kingsql_test') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')

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

GENERAL INFORMATION SECTION

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

Tuning Task Name   : tuning_kingsql_test

Tuning Task Owner  : HZH

Workload Type      : Single SQL Statement

Execution Count    : 2

Current Execution  : EXEC_16

Execution Type     : TUNE SQL

Scope              : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status  : COMPLETED

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')

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

Started at         : 10/16/2014 23:01:39

Completed at       : 10/16/2014 23:01:40

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

Schema Name: HZH

SQL ID     : 4t3fargyx8syu

SQL Text   : select count(*) from hzh1 a,hzh2 b where

             a.object_name=b.object_name

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

FINDINGS SECTION (2 findings)

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')

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

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

1- Statistics Finding

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

  Table "HZH"."HZH2" was not analyzed.

  Recommendation

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

  - Consider collecting optimizer statistics for this table.

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

            'HZH2', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')

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

            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.

2- Statistics Finding

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

  Table "HZH"."HZH1" was not analyzed.

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')

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

  Recommendation

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

  - Consider collecting optimizer statistics for this table.

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

            'HZH1', 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.

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')

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

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

EXPLAIN PLANS SECTION

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

1- Original

-----------

Plan hash value: 679460921

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

----

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')

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

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

   |

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

----

|   0 | SELECT STATEMENT    |      |     1 |   132 |       |  4929   (2)| 00:01:

00 |

|   1 |  SORT AGGREGATE     |      |     1 |   132 |       |            |

   |

|*  2 |   HASH JOIN         |      |    16M|  2124M|    22M|  4929   (2)| 00:01:

00 |

|   3 |    TABLE ACCESS FULL| HZH1 |   302K|    19M|       |  1204   (1)| 00:00:

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')

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

15 |

|   4 |    TABLE ACCESS FULL| HZH2 |   364K|    22M|       |  1204   (1)| 00:00:

15 |

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

----

Predicate Information (identified by operation id):

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

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')

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

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

總結一下這個報告：
開頭是一些簡單的資訊收集和介紹；
後面給出了2個建議，就是分析HZH1和HZH2這兩個表；

分析語句為

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

            'HZH1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

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

            'HZH2', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

6.刪除優化任務

呼叫dbms_sqltuen.drop_tuning_task刪除已經存在的優化任務

HZH@hzh%11gR2>exec dbms_sqltune.drop_tuning_task('tuning_kingsql_test');

PL/SQL procedure successfully completed.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28389881/viewspace-1301306/，如需轉載，請註明出處，否則將追究法律責任。