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