The SQL Repair Advisor can be accessed via OEM or via a command-line
API using the
DBMS_SQLDIAG PL/SQL
built-in. Two types of failures can be worked on - those related to
incidents and those which are not. The distinction is that statement
failure(s) related to an incident will be part of the incident
package. Non-incident failures can be worked on via the SQL
Worksheet and also through the Support Workbench.
The GUI approach is basically enter a query, select links related to
the repair advisor, and attempt to apply a SQL patch if available. The
SQL patch (not a patch as in a download from MOSC) is a query
transformation of the original statement. If the patch is accepted,
then the original repairable SQL can be entered and the transformation
will take care of the fix.
The difficulty in testing this is finding a statement that can be
repaired.
If the DBA
comes across one, the steps to repair via
DBMS_SQLDIAG are shown below.
A statement is run, and an error is returned. The test table
t is a copy of EMP in the SCOTT schema.
SQL> DELETE FROM scott.t1 WHERE ROWID <>
2
(SELECT MAX(ROWID) FROM
scott.t1 GROUP by empno);
(SELECT MAX(ROWID) FROM scott.t1 GROUP by empno)
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than
one row
Create a diagnosis task using the
CREATE_DIAGNOSIS_TASK subprogram. Pass in the SQL text
and assign a task name for identification later.
DECLARE
report_out clob;
task_id varchar2(50);
BEGIN
task_id :=
DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK(
sql_text => 'DELETE FROM t1 WHERE
ROWID <>
(SELECT MAX(ROWID) FROM t1 GROUP by
empno)',
task_name=>'test_task1',
problem_type=>dbms_sqldiag.problem_type_compilation_error);
END;
/
Optionally, view the task by querying from
DBA_ADVISOR_TASKS.
SELECT task_id, task_name,
to_char(created,'DD-MON HH24:MI'),
advisor_name, status
FROM dba_advisor_tasks
ORDER BY 1;
…
TASK_ID TASK_NAME
TO_CHAR(CREA ADVISOR_NAME
STATUS
------- ---------- ------------
------------------ ---------
54 test_task1 21-SEP 19:30 SQL Repair
Advisor COMPLETED
Execute the diagnosis and generate a report.
exec dbms_sqldiag.execute_diagnosis_task('test_task1');
DECLARE
rep_out
CLOB;
BEGIN
rep_out :=
dbms_sqldiag.report_diagnosis_task('test_task1',
dbms_sqldiag.type_text);
dbms_output.put_line('Report : ' ||
rep_out);
END;
/
The output from the report can be lengthy. Part of what the API tries
is using different optimizer settings based on version. The extract
below shows a path from 10.2.0.1 to 10.2.0.3 and this is just a
snippet.
- Plan for
strategy optimizer features enabled toggle with
plan directive
(hint)
OPTIMIZER_FEATURES_ENABLE('10.2.0.1') has cost 7
with plan hash
value 3950489424
- Plan for
strategy optimizer features enabled toggle with
plan directive
(hint)
OPTIMIZER_FEATURES_ENABLE('10.2.0.2') has cost 7
with plan hash
value 3950489424
- Plan for
strategy optimizer features enabled toggle with
plan directive
(hint)
OPTIMIZER_FEATURES_ENABLE('10.2.0.3') has cost 7
with plan hash
value 3950489424
If a patch
is recommended, apply it.
exec DBMS_SQLDIAG.ACCEPT_SQL_PATCH
Ã
(task_name
=> 'test_task1', task_owner => 'SYS');
If needed or desired, then delete the task.
exec DBMS_SQLTUNE.DROP_TUNING_TASK(task_name =>
'test_task1');
Overall, the SQL Repair Advisor
is pretty easy to use, especially within OEM. The command-line API
can be somewhat cryptic at times, depending on which subprogram the
DBA is trying to use.
and include the URL for the page.
