Oracle之SQL优化顾问

Oracle之SQL优化顾问

--授权
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;

--创建任务
declare
tuning_task_name VARCHAR2(30);
tuning_sqltext CLOB;
begin
 tuning_sqltext := 'select job from emp';--注意，这里不支持*,要写上具体的字段名
 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');
end;

--所有已经创建任务的查看
select * from user_advisor_log;

-- 任务的执行
exec dbms_sqltune.execute_tuning_task(task_name => 'sql_trace_20131124');

--任务执行后状态的检查
 select * from user_advisor_tasks t where t.task_name = 'sql_trace_20131124'

--最终报告的生成
select dbms_sqltune.report_tuning_task('sql_trace_20131124') from dual;

--任务的删除
exec dbms_sqltune.drop_tuning_task('sql_trace_20131124');

相关阅读：