STA优化

 oracle数据10g开始出现sta,用来帮助dba和开发人员提高sql语句性能。实施优化步骤：

1 保证用户权限

define USER=oracle
GRANT ADMINISTER SQL TUNING SET TO &USER;
GRANT ADMINISTER ANY SQL TUNING SET TO &USER;
GRANT ADVISOR TO &USER;
GRANT CREATE ANY SQL PROFILE TO &USER;
GRANT DROP   ANY SQL PROFILE TO &USER;
GRANT DROP   ANY SQL PROFILE TO &USER;

2 执行生成优化集

declare
my_task_name varchar2(100);
my_sqlid varchar2(100);
my_sqltext clob;
begin
my_sqlid:='&sqlid';
my_task_name:=dbms_sqltune.create_tuning_task(sql_id=>my_sqlid,scope=>'COMPREHENSIVE',time_limit=>300,task_name=>'&task_name');
end;
 /

 begin
dbms_sqltune.execute_tuning_task(task_name=>'&task_name');
end;
/

3 查询优化集

et long 10000
set lines 120
Cannot SET LINES
set pages 60
select dbms_sqltune.report_tuning_task('&task_name') from dual;

3 查询log

select task_name from user_advisor_log;
select status    from user_advisor_tasks where task_name='';

4 维护优化集和task

-删除profile
call dbms_sqltune.drop_SQL_PROFILE();
SELECT * FROM DBA_SQL_PROFILES;

--删除task

exec dbms_sqltune.drop_tuning_task();