How to use STA（sql tuning advisor）

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