8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 11g » Here

SQL Repair Advisor (DBMS_SQLDIAG and DBMS_SQLDIAG_INTERNAL) 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 a 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 a 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 a 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 a 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 a 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.

Manually create a new SQL Patch (12.2+)

Oracle 12c Release 2 (12.2) has a documented method for manually creating SQL patches to add hints, available for both Oracle SE and Oracle EE. Rather than using the SYS.DBMS_SQLDIAG_INTERNAL package you can now use the SYS.DBMS_SQLDIAG package.

DECLARE
  l_patch_name  VARCHAR2(32767);
BEGIN
  -- SQL Text
  l_patch_name := SYS.DBMS_SQLDIAG.create_sql_patch(
    sql_text  => 'SELECT * FROM big_table WHERE id >= 8000',
    hint_text => 'PARALLEL(big_table,10)',
    name      => 'big_table_sql_patch_1');

  -- SQL ID
  l_patch_name := SYS.DBMS_SQLDIAG.create_sql_patch(
    sql_id    => '19v5guvsgcd1v',
    hint_text => 'PARALLEL(big_table,10)',
    name      => 'big_table_sql_patch_2');
END;
/

If the NAME parameter is omitted a system generated patch name is used and returned by the functions.

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

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

This interface can be added to 12.1.0.2 EE using Patch #17203284.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.