oracle 10g sql tuning advisor 手工执行

1 单个sql 语句：

 (1)创建 sql tuning task

DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext   CLOB;
  v_bind_lst       sql_binds;
BEGIN
  v_bind_lst := sql_binds(anydata.convertnumber(1234), 
                         anydata.convertvarchar2('03') );
  my_sqltext   := 'select * from sajet.sys_emp where emp_id=:b1 and emp_no=:b2';
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => my_sqltext,
                                                  bind_list   => v_bind_lst,
                                                  user_name   => 'SAJET',
                                                  scope       => 'COMPREHENSIVE',
                                                  time_limit  => 60,
                                                  task_name   => 'my_sql_tuning_task',
                                                  description => 'Task to tune a query on a specified employee');
END;

(2)执行task

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

(3) 查看Task状态

SELECT task_name FROM DBA_ADVISOR_LOG WHERE owner = 'SAJET';

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE user_name = 'HR' AND task_
name = ’my_sql_tuning_task’;

(4) 查看结果

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