Sql Tuning Advisor是10g以后出现的一个十分有用的调优工具，大多数情况下我们可以通过dbconsole或者Grid Control的web界面调用SQL Advisor；但如果系统中没有配置dbconsole或者Grid Control的话，我们则需要通过手动调用DBMS_SQLTUNE PL/SQL程序包来使用该特性。这里我列出一个针对单个SQL语句Autotune的脚本，具体脚本:

begin  DBMS_SQLTUNE.drop_tuning_task('&task_name');end;/DECLARE  my_task_name VARCHAR2(30);  my_sqltext CLOB;  my_sqlid varchar2(30);BEGIN  my_sqlid := '&sqlid';  my_task_name := dbms_sqltune.create_tuning_task(sql_id => my_sqlid,                                                  scope => 'COMPREHENSIVE',                                                  time_limit => 300,                                                  task_name => '&task_name',                                                  description => 'comment'                                                                                 );END;/BEGIN  dbms_sqltune.execute_tuning_task(task_name => '&task_name');END;/SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name';SET LONG 10000SET LONGCHUNKSIZE 10000SET LINESIZE 100set pages 60SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL;

具体使用示例:

SQL> begin  DBMS_SQLTUNE.drop_tuning_task('&task_name');end;/Enter value for task_name: newtaskold   3:   DBMS_SQLTUNE.drop_tuning_task('&task_name');new   3:   DBMS_SQLTUNE.drop_tuning_task('newtask');begin*ERROR at line 1:ORA-13605: The specified task or object newtask does not exist for the currentuser.ORA-06512: at "SYS.PRVT_ADVISOR", line 2307ORA-06512: at "SYS.DBMS_ADVISOR", line 172ORA-06512: at "SYS.DBMS_SQLTUNE", line 751ORA-06512: at line 3SQL> DECLARE  my_task_name VARCHAR2(30);  my_sqltext CLOB;  my_sqlid varchar2(30);BEGIN  my_sqlid := '&sqlid';  my_task_name := dbms_sqltune.create_tuning_task(sql_id => my_sqlid,                                                  scope => 'COMPREHENSIVE',                                                  time_limit => 300,                                                  task_name => '&task_name',                                                  description => 'comment'                                                                                 );END;/ Enter value for sqlid: 17usubxchdf2wold  11:   my_sqlid := '&sqlid';new  11:   my_sqlid := '17usubxchdf2w';Enter value for task_name: new_task old  19:						   task_name => '&task_name',new  19:						   task_name => 'new_task',SQL> BEGIN  dbms_sqltune.execute_tuning_task(task_name => '&task_name');END;/  Enter value for task_name: new_taskold   3:   dbms_sqltune.execute_tuning_task(task_name => '&task_name');new   3:   dbms_sqltune.execute_tuning_task(task_name => 'new_task');PL/SQL procedure successfully completed.SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name';Enter value for task_name: new_taskold   1: SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name'new   1: SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'new_task'STATUS-----------COMPLETEDSQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL;Enter value for task_name: new_taskold   1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUALnew   1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('new_task') FROM DUALDBMS_SQLTUNE.REPORT_TUNING_TASK('NEW_TASK')----------------------------------------------------------------------------------------------------GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name   : new_taskTuning Task Owner  : SYSWorkload Type	   : Single SQL StatementScope		   : COMPREHENSIVETime Limit(seconds): 300Completion Status  : COMPLETEDStarted at	   : 03/25/2011 00:14:41Completed at	   : 03/25/2011 00:14:45-------------------------------------------------------------------------------Schema Name: SYSSQL ID	   : 17usubxchdf2wSQL Text   : select count(t1) from hashtab-------------------------------------------------------------------------------FINDINGS SECTION (1 finding)-------------------------------------------------------------------------------1- Statistics Finding---------------------  Table "SYS"."HASHTAB" was not analyzed.  Recommendation  --------------  - Consider collecting optimizer statistics for this table.    execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>	    'HASHTAB', 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: 485915006------------------------------------------------------------------------------| Id  | Operation	   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |	     |	   1 |	  13 |	   2   (0)| 00:00:01 ||   1 |  SORT AGGREGATE    |	     |	   1 |	  13 |		  |	     ||   2 |   TABLE ACCESS FULL| HASHTAB |	 102 |	1326 |	   2   (0)| 00:00:01 |------------------------------------------------------------------------------