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.
For more information see:
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.
PROBLEM_TYPE_PERFORMANCE
- The SQL is performing badly.PROBLEM_TYPE_WRONG_RESULTS
- The query appears to be giving inconsistent results.PROBLEM_TYPE_COMPILATION_ERROR
- The query fails to compile, even though it should be valid.PROBLEM_TYPE_EXECUTION_ERROR
- The query compiles, but results in a runtime error, probably due to a bad execution plan, or an execution plan that encounters a bug.PROBLEM_TYPE_ALT_PLAN_GEN
- The user wants the advisor to look for alternative plans.The EXECUTE_DIAGNOSIS_TASK
procedure does exactly what it's name suggests.
SYS.DBMS_SQLDIAG.execute_diagnosis_task(task_name => 'sql_repair_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 "*".
TYPE
: TYPE_HTML
, TYPE_TEXT
*, TYPE_XML
LEVEL
: LEVEL_ALL
, LEVEL_BASIC
, LEVEL_TYPICAL
*SECTION
: SECTION_ALL
*, SECTION_ERRORS
, SECTION_FINDINGS
, SECTION_INFORMATION
, SECTION_PLANS
, SECTION_SUMMARY
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; /
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; /
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.
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.
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 normal view: https://oracle-base.com/articles/11g/sql-repair-advisor-11g