Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

SQL Repair Advisor in Oracle 11g

Oracle 11g introduced the SQL Repair Advisor to help diagnose and fix valid SQL statements that fail with critical errors at runtime. The advisor performs a diagnostic operation on the statement and may provide a patch to repair the statement. The advisor is available from Enterprise Manager, as described here, but this article described the DBMS_SQLDIAG package, which provides a PL/SQL interface to interact with the SQL Repair Advisor.

CREATE_DIAGNOSTICS_TASK

There are three overloads of the CREATE_DIAGNOSTICS_TASK function to create a task using SQL text, and SQL_ID or an SQL set.

l_task := SYS.DBMS_SQLDIAG.create_diagnosis_task( 
                sql_id       => '556prbqmcw8d1',
                task_name    => 'sql_repair_task',
                problem_type => DBMS_SQLDIAG.problem_type_execution_error);

The TASK_NAME parameter is optional. If one is not specified a system generated name will be used and passed back in the return value.

The PROBLEM_TYPE parameter is defined using one of the following constants defined in the DBMS_SQLDIAG package.

EXECUTE_DIAGNOSIS_TASK

The EXECUTE_DIAGNOSIS_TASK procedure does exactly what it's name suggests.

SYS.DBMS_SQLDIAG.execute_diagnosis_task(task_name => 'sql_repair_task');

REPORT_DIAGNOSIS_TASK

The REPORT_DIAGNOSIS_TASK function returns a CLOB containing the results of diagnostics task, which will include any recommendations.

l_report := SYS.DBMS_SQLDIAG.report_diagnosis_task(taskname => l_task);

The content of the report can be tailored using the TYPE, LEVEL and SECTION parameters.

l_report := SYS.DBMS_SQLDIAG.report_diagnosis_task(task_name => l_task,
                                                   type      => DBMS_SQLDIAG.type_html,
                                                   level     => DBMS_SQLDIAG.level_all,
                                                   section   => DBMS_SQLDIAG.section_all);

Valid values for these three parameters are displayed below, with the default value indicated with a "*".

ACCEPT_SQL_PATCH

If you are happy with the recommended SQL patch suggested by the task report, you can apply the SQL patch using the ACCEPT_SQL_PATCH procedure.

BEGIN
  SYS.DBMS_SQLDIAG.accept_sql_patch(
        task_name  => 'sql_repair_task',
        task_owner => 'SYS',
        replace    => TRUE);
END;
/

DROP_SQL_PATCH

If you later want to drop an SQL patch, you can do this using the DROP_SQL_PATCH procedure.

BEGIN
  SYS.DBMS_SQLDIAG.drop_sql_patch(name => 'my_sql_patch');
END;
/

Putting It All Together

If we have a problem SQL statement with an SQL_ID of "556prbqmcw8d1", we might do the following.

DECLARE
  l_task    VARCHAR2(50);
  l_report  CLOB;
BEGIN
  l_task := SYS.DBMS_SQLDIAG.create_diagnosis_task( 
                  sql_id       => '556prbqmcw8d1',
                  task_name    => 'sql_repair_task',
                  problem_type => DBMS_SQLDIAG.problem_type_execution_error);

  SYS.DBMS_SQLDIAG.execute_diagnosis_task(task_name => l_task);

  l_report := SYS.DBMS_SQLDIAG.report_diagnosis_task(task_name => l_task);
 
  DBMS_OUTPUT.PUT_LINE ('Report : ' || l_report);
END;
/

Check the output of the report, the apply the recommendation.

BEGIN
  SYS.DBMS_SQLDIAG.accept_sql_patch(
        task_name  => 'sql_repair_task',
        task_owner => 'SYS',
        replace    => TRUE);
END;
/

The execution plan displayed using DBMS_XPLAN includes a line to indicate when an SQL patch has been used by the optimizer.

Manually create a new SQL Patch

Oracle provide an undocumented package called DBMS_SQLDIAG_INTERNAL, which gives you the ability to manually create and SQL patch for the sole purpose of adding hints to SQL from shrink-wrapped applications, where you are not able to modify the SQL directly.

BEGIN
  SYS.DBMS_SQLDIAG_INTERNAL.i_create_patch(
    sql_text  => 'SELECT * FROM big_table WHERE id >= 8000',
    hint_text => 'PARALLEL(big_table,10)',
    name      => 'big_table_sql_patch');
END;
/

These manual SQL patches can be removed in the normal manner.

BEGIN
  DBMS_SQLDIAG.drop_sql_patch(name => 'scott_emp_sql_patch');
END;
/

You can read more about this functionality in this blog post from the optimizer team.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.