HowtouseSTA（sqltuningadvisor）

一、手工生成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 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');