【oracle】oracle STA(SQL Tuning Advisor)

【oracle】oracle STA(SQL Tuning Advisor)

STA(SQL Tuning Advisor):STA能快速定位性能瓶颈，从而为性能优化提供准确的依据。

--查看待优化sql的执行时间
set serveroutput on;
set timing on;
set linesize 1000;
--待执行的sql

创建 并 执行 STA
set serveroutput on;

declare
my_task_name varchar2(30);
my_sqltext CLOB;
begin
my_sqltext := 'select count(*) from smalltab a,bigtab b where a.table_name = b.table_name';
--删除 STA
--dbms_sqltune.drop_tuning_task(task_name => 'smalltab_inner_bigtab_sql');
--创建 STA
my_task_name := dbms_sqltune.create_tuning_task(
sql_text => my_sqltext,
user_name => upper('ssa_masadic'),
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'smalltab_inner_bigab_sql',
description => '优化smalltab和bigtab连接到的例子'
);
--执行 STA
dbms_sqltune.execute_tuning_task(task_name => 'smalltab_inner_bigtab_sql');

dbms_output.put_line('my_task_name='||my_task_name);
end;
/

注意:
●在 ''内的'用2个单引号'表示。
●参数 sql_text 中的语句 不带 ;
●带 with as 的sql语句 STA 优化不了。（The optimzer cannot merge a view that contains a "WITH" clause）

 

系统试图：
user_advisor_tasks, dba_advisor_tasks
user_advisor_log,dba_advisor_log

 

posted on 2018-04-05 16:38  绿Z 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/greenZ/p/8723191.html