Oracle Sql Repair Advisor介绍以及使用

         sql repair advisor 分析单条sql语句，主要用于sql 执行错误、性能问题、sql 产生错误的结果问题并应用sql patch 进行问题修复，oracle 自11g 开始提供，主要通过以下几种方式去使用：

方式一：通过plsql dbms_sqldiag 诊断包

declare

l_sql_diag_task_id varchar2(100);

begin

--

-- Create diagnostic task

--wrong results

l_sql_diag_task_id :=  dbms_sqldiag.create_diagnosis_task (

sql_id => '&sql_id',

problem_type => dbms_sqldiag.PROBLEM_TYPE_WRONG_RESULTS,

task_name => '&sql_id_diagnostic_task' );

--performance issue

l_sql_diag_task_id :=  dbms_sqldiag.create_diagnosis_task (

sql_id => '&sql_id',

problem_type => dbms_sqldiag.PROBLEM_TYPE_PERFORMANCE,

task_name => '&sql_id_diagnostic_task' );

--execution error

l_sql_diag_task_id :=  dbms_sqldiag.create_diagnosis_task (

sql_id => '&sql_id',

problem_type => dbms_sqldiag.PROBLEM_TYPE_EXECUTION_ERROR,

task_name => '&sql_id_diagnostic_task' );

--

-- Setup parameters for the task to give verbose output

--

dbms_sqltune.set_tuning_task_parameter(

l_sql_diag_task_id,

'_SQLDIAG_FINDING_MODE',

DBMS_SQLDIAG.SQLDIAG_FINDINGS_FILTER_PLANS);

end;

/

--

-- Execute the task

-- 

exec dbms_sqldiag.execute_diagnosis_task ( task_name  => '&sql_id_diagnostic_task' );

--

-- Generate and output the report for the task

--

select dbms_sqldiag.report_diagnosis_task ('&sql_id_diagnostic_task')   as recommendations from dual;

方式二：通过OEM 图形化界面