1.执行权限：

Grant  administer sql tuning set to

Grant  administer any sql tuning set to

Grant  advisor to

Grant create any sql profile to

Grant alter any sql profile to

Grant drop any sql profile to

2.创建优化任务

Declare

     tuning_task_name varchar2(30);

     tuning_sqltext  clob;

Begin

     tuning_sqltext :='sql_text'

     tuning_task_name := dbms_sqltune.create_tuning_task(

        sql_text => tuning_sqltext,

        bind_lisk =>sql_binds(anydata.convertvarchar2(100))，

        user_name=>'任务名',

        scope =>'COMPREHENSIVE',

        time_limit =>60,

        task_name=>'first_tuning_task',

        description=>'Tune test');

End;

/

3.查看任务

Select task_name from user_advisor_log;

4.执行sql优化任务

Begin

   dbms_sqltune.execute_tuning_task(task_name =>'任务名');

End;

/

5.查询任务状态

Select status from user_advisor_tasks where task_name='任务名';

6.显示报告

SET LONG 8000

SET LONGCHUNKSIZE 8000

SET LINESIZE 100

SET PAGESIZE 100

Select dbms_sqltune.report_tuning_task('任务名') from dual;