先看看 sql repair advisor的用途，主要是用来改变sql的执行计划的：
sqlplan will change after patching. SQL Repair Advisor is not automatically launched after a SQL statement crash
* Export patches * DBMS_SQLDIAG.pack_stgtab_sqlpatch(staging_table), export, unpack_stgtab_sqlpatch(staging_table)
* Packages *
DBMS_SQLDIAG.create_diagnosis_task(sql_text, task_name)
DBMS_SQLDIAG.set_tuning_task_parameter(task_id, filter)
DBMS_SQLDIAG.execute_diagnosis_task(task_name)
DBMS_SQLDIAG.report_diagnosis_task(task_name, out_type)
DBMS_SQLDIAG.accept_sql_patch(task_name, task_owner)
* Views *
DBA_SQL_PATCHES(recommended patches), drop_sql_patch(procedure)
举个例子先：
DELETE FROM scott.emp WHERE ROWID <> (SELECT MAX(ROWID) FROM scott.emp GROUP by empno);
ERROR at line 2:ORA-01427: single-row subquery returns more than one row
建立一个诊断任务
DECLARE
report_out clob;
task_id varchar2(50);
BEGIN
task_id := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK(
sql_text => 'DELETE FROM scott.emp WHERE ROWID <> (SELECT MAX(ROWID) FROM scott.emp GROUP by empno)',
task_name=>'test_task1',
problem_type=>dbms_sqldiag.problem_type_compilation_error);
END;
建完了，查询一下：
SELECT task_id, task_name, to_char(created,'YYYY-MM-dd hh24:mi:ss'), advisor_name, status FROM dba_advisor_tasks O order by created desc
执行诊断任务：
begin
dbms_sqldiag.execute_diagnosis_task('test_task1');
end;
生成报告：
DECLARE
rep_out CLOB;
BEGIN
rep_out := dbms_sqldiag.report_diagnosis_task('test_task1', dbms_sqldiag.type_text);
dbms_output.put_line('Report : ' || rep_out);
end;
Report : GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : test_task1
Tuning Task Owner : SNOWHILL
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 06/07/2013 16:36:17
Completed at : 06/07/2013 16:38:58
-------------------------------------------------------------------------------
Schema Name: SNOWHILL
SQL ID : guz0ng0q5z3s9
SQL Text : DELETE FROM scott.emp WHERE ROWID <> (SELECT MAX(ROWID) FROM
scott.emp GROUP by empno)
-------------------------------------------------------------------------------
No SQL patch was found to resolve the problem.
-------------------------------------------------------------------------------
接受建议：
begin
DBMS_SQLDIAG.ACCEPT_SQL_PATCH (task_name => 'test_task1', task_owner => 'SNOWHILL');
end;
删除任务：
begin
DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'test_task1');
end;