http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php

自动SQL调优建议：统计信息，SQL Profile，访问路径，SQL结构分析

1 创建一个查询的sql tuning task
declare
l_task_id     varchar2(20);
l_sql         varchar2(2000);
begin
l_sql := 'select object_type from test where object_type = ''TYPE'' ';
dbms_sqltune.drop_tuning_task ('TEST');
l_task_id := dbms_sqltune.create_tuning_task (
sql_text  => l_sql,
user_name  => 'RMAN',
scope      => 'COMPREHENSIVE',
time_limit => 120,
task_name  => 'TEST'
   );
dbms_sqltune.execute_tuning_task ('TEST');
end;
/

SET LONG 1000000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('TEST') AS recommendations FROM dual;
SET PAGESIZE 24

2 创建一个内存的statement的sql tuning task
declare
l_task_id     varchar2(20);
l_sql         varchar2(2000);
begin
--dbms_sqltune.drop_tuning_task ('TEST');
l_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_id           => '7wb923pm2b5vm',
  task_name        => 'TEST',
  description      => 'TEST'
   );
dbms_sqltune.execute_tuning_task ('TEST');
end;
/

3 基于awr创建
begin
dbms_sqltune.drop_tuning_task ('TEST');
end;
/

declare
l_task_id     varchar2(20);
l_sql         varchar2(2000);
begin
l_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  begin_snap      => 139,
  end_snap        => 140,
  sql_id          => '7wb923pm2b5vm',
  task_name       => 'TEST',
  description     => 'TEST')
;
dbms_sqltune.execute_tuning_task ('TEST');
end;
/

4 基于sqlset创建
begin
dbms_sqltune.drop_tuning_task ('TEST');
end;
/

declare
l_task_id     varchar2(20);
l_sql         varchar2(2000);
begin
l_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sqlset_name     => 'my_sql_tuning_set',
  task_name       => 'TEST')
;
dbms_sqltune.execute_tuning_task ('TEST');
end;
/

5 创建sql tuning set
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'my_sql_tuning_set',
    description  => 'I/O intensive workload');
END;
/

DECLARE
 baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN baseline_cursor FOR
    SELECT VALUE(p)
    FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
                  'peak baseline',
                   NULL, NULL,
                   'elapsed_time',
                   NULL, NULL, NULL,
                   30)) p;

    DBMS_SQLTUNE.LOAD_SQLSET(
             sqlset_name     => 'my_sql_tuning_set',
             populate_cursor => baseline_cursor);
END;
/

查看sql tuning set
SELECT sql_id, sql_text FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(
   'my_sql_tuning_set',
   null));

查看执行计划
SET LONG 1000000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('TEST') AS recommendations FROM dual;
SET PAGESIZE 24

接受sql profile
DECLARE
 my_sqlprofile_name VARCHAR2(30);
BEGIN
 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
    task_name    => 'TEST',
    name         => 'test_profile',
    force_match  => TRUE);
END;
/

NOTE: 不能接受set中所有的query