SQL PROFILE使用说明

创建任务

declare

 my_task_name varchar2(30);

 my_sqltext clob;

begin

 my_sqltext :='select * from emp where ename = ''JAMES'';';

 my_task_name := dbms_sqltune.create_tuning_task(

                                    sql_text => my_sqltext,

                                    user_name=> user,

                                    scope => 'COMPREHENSIVE',

                                    time_limit =>3600,

                                    task_name =>'sql_tuning_test',

                                    description => 'Tuning Task');

end;

/

2.执行任务

exec dbms_sqltune.execute_tuning_task('sql_tuning_test');

3.查看任务状态

select * from user_advisor_tasks where task_name='sql_tuning_test';

4.得到执行任务结果

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;

5.删除任务

exec dbms_sqltune.drop_tuning_task('sql_tuning_test');

附：调用oracle自带存储过程执行sql tuning advisor

SQL>@$/ORACLE_HOME/rdbms/admin/sqltrpt.sql

结果会列出前几位的问题SQL，并给出优化建议及执行语句。