1、授权(scott是访问的用户名)
grant administer any sql tuning set to scott;
grant advisor to scott;
grant create any sql profile to scott;
grant alter any sql profile to scott;
grant drop any sql profile to scott;
2、创建任务
declare
tuning_task_name VARCHAR2(30);
tuning_sqltext CLOB;
begin
tuning_sqltext := 'select job from emp';--注意,这个是你要优化的SQL,这里不支持*,要写上具体的字段名
tuning_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => tuning_sqltext,
user_name => 'SCOTT',--注意用户名
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_trace_20131124',--名字自定义
description => 'EMP SELECT TUNE');
dbms_sqltune.execute_tuning_task(task_name => 'sql_trace_20131124');--与上面的自定义任务名一致
end;
3、查看最终报告(优化建议)
select dbms_sqltune.report_tuning_task('sql_trace_20131124') from dual; --与上面的自定义任务名一致