手工執行sql tuning advisor和sql access advisor

sql tuning advisor：
建立任務
DECLARE

 my_task_name VARCHAR2(30);
 my_sqltext   CLOB;
BEGIN
 my_sqltext := 'SELECT /*+ ORDERED */ * '                      ||
               'FROM employees e, locations l, departments d ' ||
               'WHERE e.department_id = d.department_id AND '  ||
                     'l.location_id = d.location_id AND '      ||
                     'e.employee_id < :bnd';

 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         sql_text    => my_sqltext,
         bind_list   => sql_binds(anydata.ConvertNumber(100)),
         user_name   => 'HR',
         scope       => 'COMPREHENSIVE',
         time_limit  => 60,
         task_name   => 'my_sql_tuning_task',
         description => 'Task to tune a query on a specified employee');
END;
/

執行任務

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
END;
/

檢視狀態和檢視結果

SELECT status 
FROM   USER_ADVISOR_TASKS 
WHERE  task_name = 'my_sql_tuning_task';

SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task')
FROM   DUAL;

如想針對sql_id做調優，create task時使用如下語法：

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_id           IN VARCHAR2,
  plan_hash_value  IN NUMBER    := NULL,
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;

下面舉例一個：
1、為SQL_id建立一個STA（SQL Tuning advisor）分析任務（使用SYS使用者執行）
SQL> variable task_li_test varchar2(2000);
SQL>exec :task_li_test:=dbms_sqltune.create_tuning_task(sql_id=>'xxxxxxxxxxx',time_limit=>600,task_name=>'li_sql_1');
 
2、執行上面建立的STA（需要一定的時間）
SQL> exec dbms_sqltune.execute_tuning_task(task_name=>'li_sql_1');
 
3、查詢最佳化任務建立與執行的情況
select a.owner,b.task_id,b.task_name,a.created from dba_advisor_tasks a,dba_advisor_log b where a.task_id=b.task_id and a.task_name='li_sql_1';


 
4、檢視任務最佳化報告（最佳化的詳細內容）
SQL>select dbms_sqltune.report_tuning_task('li_sql_1') from dual;
 
5、接受建議的 SQL 概要檔案,即建立SQL_Profle
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'li_sql_1',task_owner =>'SYS', replace => TRUE);
 
6、檢視第5步建立起來的SQL_Profile資訊
SQL>select a.name,a.task_id,a.created from dba_sql_profiles a,dba_advisor_log b where a.task_id=b.task_id and b.task_name='li_sql_1'
NAME
TASK_ID
 CREATED
SYS_SQLPROF_01411bdf99410002
106699
    14-9月 -13 05.49.00.000000 下午
 
7、再次執行SQLID為‘xxxxxxxxxxx’的語句
7.1、執行時間由原來的6分鐘降為3秒
7.2、檢視執行計劃，執行計劃中包含下面資訊，說明是使用了建立的SQL_Profile所起到的效果
  - SQL profile SYS_SQLPROF_01411bdf99410002used for this statement
 
8、刪除SQL_Profile
SQL>exec dbms_sqltune.drop_sql_profile(name =>'SYS_SQLPROF_01411bdf99410002');
 
9、刪除最佳化任務
SQL> exec dbms_sqltune.drop_tuning_task(task_name => 'li_sql_1');