STA优化-CSDN博客

本文链接：https://blog.csdn.net/oradbm/article/details/53389133

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(1000);
  my_sqltext clob;
  begin

-----SELECT q'(sql_text)' into my_sqltext from dual;
  SELECT sql_fulltext INTO my_sqltext FROM v$sql where sql_id='f8za0xgm6hrpd' AND ROWNUM<2;
  my_task_name:=dbms_sqltune.create_tuning_task(sql_text => my_sqltext,user_name => 'NPMDB',scope => 'COMPREHENSIVE',time_limit => 200,task_name => 'sql_tuning_task0C',description => 'sql');
  dbms_sqltune.execute_tuning_task(task_name => 'sql_tuning_task0C');
   end;
  /

SET LONG 1000000
select dbms_sqltune.report_tuning_task('sql_tuning_task01') from dual;

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();