DBMS_SQLTUNE

首先，让我们看看如何在命令行中使用它。我们通过调用提供的包 dbms_sqltune 来调用顾问。
declare
l_task_id     varchar2(20);
l_sql         varchar2(2000);
begin
l_sql := 'select account_no from accounts where old_account_no = 11';
dbms_sqltune.drop_tuning_task ('FOLIO_COUNT');
l_task_id := dbms_sqltune.create_tuning_task (
sql_text  => l_sql,
user_name  => 'ARUP',
scope      => 'COMPREHENSIVE',
time_limit => 120,
task_name  => 'FOLIO_COUNT'
   );
dbms_sqltune.execute_tuning_task ('FOLIO_COUNT');
end;
/
这个包创建并执行了一个名为 FOLIO_COUNT 的调整任务。接下来，您将需要查看任务执行的结果（也就是说，查看建议）。
set serveroutput on size 999999
set long 999999
select dbms_sqltune.report_tuning_task ('FOLIO_COUNT') from dual;

列表 2 显示的是输出结果。仔细查看这些建议；顾问说您可以通过在 OLD_ACCOUNT_NO 列上创建一个索引来改进性能。更佳的是，如果创建索引，顾问计算了查询成本，从而使潜在的节省量
变得更加可定义、更加具体。
当然，考虑到本示例的简单性，通过手动检查也能得到这种结论。但是，可以想象出该工具对于那些更复杂的查询十分有用，因为对这些查询执行手动检查也许是不可能的或不实际的。

第一步：创建优化任务
通过调用函数CREATE_TUNING_TASK来创建优化任务，调用存储过程EXECUTE_TUNING_TASK执行该任务：
SQL> set autot off
SQL> set timing 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 a.object_name=b.table_name';
  6    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  7            sql_text    => my_sqltext,
  8            user_name   => 'DAVE',   -- 注意是大写，不然会报错，用户无效
  9            scope       => 'COMPREHENSIVE',
10            time_limit  => 60,
11            task_name   => 'tuning_sql_test',
12            description => 'Task to tune a query on a specified table');
13
14    DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');
15  END;
16  /

PL/SQL procedure successfully completed.
在函数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 999999
SQL> set serveroutput on size 999999
SQL> SET LINESIZE 100
SQL> 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                 : DEMO
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 5/28/2010 13:16:43
Completed at                      : 5/28/2010 13:16:44
Number of Index Findings          : 1

Schema Name: DEMO
SQL ID     : 6p64dnnsqf9pm
SQL Text   : select count(*) from bigtab a, smalltab b where
             a.object_name=b.table_name

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Index Finding (see explain plans section below)

  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 100%)
  ----------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index DEMO.IDX$$_06C50001 on SYS.SMALLTAB('TABLE_NAME');

  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index DEMO.IDX$$_06C50002 on SYS.BIGTAB('OBJECT_NAME');

  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.


EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3089226980
--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    36 |  3550   (2)| 00:00:43 |
|   1 |  SORT AGGREGATE     |          |     1 |    36 |            |          |
|*  2 |   HASH JOIN         |          |   155K|  5462K|  3550   (2)| 00:00:43 |
|   3 |    TABLE ACCESS FULL| SMALLTAB |  1223 | 22014 |    11   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| BIGTAB   |  1205K|    20M|  3526   (1)| 00:00:43 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
2- Using New Indices
--------------------
Plan hash value: 494801882
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                |     1 |    36 |  1108   (3)| 00:00:14 |
|   1 |  SORT AGGREGATE        |                |     1 |    36 |            |        |
|*  2 |   HASH JOIN            |                |   155K|  5462K|  1108   (3)| 00:00:14 |
|   3 |    INDEX FAST FULL SCAN| IDX$$_06C50001 |  1223 | 22014 |     3   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| IDX$$_06C50002 |  1205K|    20M|  1093   (2)| 00:00:14 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
------------------------------------------------------------------------------

看一下这个优化建议报告：
第一部分是关于这次优化任务的基本信息：如任务名称、执行时间、范围、涉及到的语句等等。

第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。前面先给出了问题描述：可以通过建立更多的所引来提高性能；然后是建议的具体内容：在表smalltab的字段table_name
上创建索引，在表bigtab的字段object_name上创建索引；最后是相关注意事项：此次优化虽然给出了创建索引的建议，但是最好通过SQL访问建议器(SQL Access Advisor SAA)结合整个数据
库的工作量来深入分析，那样就能给出考虑了索引维护和空间消耗等因素的更加合理的建议。
最后，报告还给出了原有的查询计划，以及采用优化建议以后的查询计划的对比。可以看出COST值大大下降。

五、删除优化任务
通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务
SQL>exec dbms_sqltune.drop_tuning_task('tuning_sql_test');
PL/SQL procedure successfully completed.