8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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
- EXECUTE_DIAGNOSIS_TASK
- REPORT_DIAGNOSIS_TASK
- ACCEPT_SQL_PATCH
- DROP_SQL_PATCH
- Putting It All Together
- Manually create a new SQL Patch (Pre-12.2)
- Manually create a new SQL Patch (12.2+)
For more information see:
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.
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.
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 "*".
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
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:
- Repairing SQL Failures with the SQL Repair Advisor
- DBMS_SQLDIAG (11g)
- DBMS_SQLDIAG (12.2)
- Using SQL Patch to add hints to a packaged application
- Adding and Disabling Hints Using SQL Patch
- DBMS_SQLDIAG : Export and Import SQL Test Cases
- DBMS_ADVANCED_REWRITE in Oracle 10g
Hope this helps. Regards Tim...