一、手工生成sql tuning advisor
1、sql text format:
declare
my_task_name varchar2(30);
my_sqltext clob;
begin
my_sqltext := 'select * from dba_segments where owner=''clic'' and segment_type=''table''';
my_task_name := dbms_sqltune.create_tuning_task(sql_text => my_sqltext,
scope => 'comprehensive',
time_limit => 60,
task_name => 'test_sql_tuning_task1',
description => 'task to tune a query');
dbms_sqltune.execute_tuning_task(task_name => 'test_sql_tuning_task1');
end;
/
2、sql id format：
declare
my_task_name varchar2(30);
my_sqltext clob;
begin
my_task_name := dbms_sqltune.create_tuning_task(sql_id => 'b3uaak09jfaxc',
scope => 'comprehensive',
time_limit => 60,
task_name => 'test_sql_tuning_task1',
description => 'task to tune a query');
dbms_sqltune.execute_tuning_task(task_name => 'test_sql_tuning_task1');
end;
/
二、查看生成的stareport：
set long 999999
set longchunksize 999999
set serveroutput on size 999999
set linesize 200
select dbms_sqltune.report_tuning_task('test_sql_tuning_task1') from dual;
exec dbms_sqltune.drop_tuning_task('test_sql_tuning_task1');
删除优化任务
sql> execdbms_sqltune.drop_tuning_task(task_name => 'li_sql_1');
三、accept sql profile
接受建议的 sql 概要文件,即创建sql_profle
sql> execute dbms_sqltune.accept_sql_profile(task_name => 'test_sql_tuning_task1',task_owner =>'sys', replace => true);
查看创建起来的sql_profile信息
sql>select a.name,a.task_id,a.createdfrom dba_sql_profiles a,dba_advisor_log bwhere a.task_id=b.task_idand b.task_name='test_sql_tuning_task1';
删除sql_profile
sql>exec dbms_sqltune.drop_sql_profile(name =>'sys_sqlprof_01411bdf99410002');