Problem Diagnosis - Expert Oracle RAC Performance Diagnostics and Tuning - page 625

where ( T32069.ROW_WID = T537323.PRODUCT_WID and T501320.ROW_WID = T501866.CUSTOMER_WID and T501866.
ROW_WID = T537323.INSTALL_LOC_WID and T512643.ROW_WID = T537323.INSTALL_BASE_WID and T534609.ROW_WID
= T537323.INCIDENT_DT_WID and T537323.EFFECTIVE_TO_DT_WID = 47121231 and T537323.SRV_REQ_WID =
T537387.ROW_WID and T537387.INCIDENT_TYPE_CD = '8-Telephone Support' and T595543.NOTE_TYPE_NAME is
not null and T595543.CREATED_ON_DT between TO_DATE('2010-07-26 00:01:45' , 'YYYY-MM-DD HH24:MI:SS')
and TO_DATE('2010-07-27 00:01:45' , 'YYYY-MM-DD HH24:MI:SS') )
group by T32069.X_PROD_HIER8_NAME, T501320.NAME, T512643.INSTANCE_NUM, T512643.SERIAL_NUM, T534609.
ROW_WID, T534609.DAY_DT, T537387.SRV_REQ_NUM, T537387.INCIDENT_TYPE_CD, T595543.CREATED_ON_DT,
T595543.NOTES, T595543.NOTES_DETAIL, T595543.NOTE_TYPE_NAME
order by c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11 ',
task_name => 'ERR_INC_52777_TASK',
problem_type => DBMS_SQLDIAG.PROBLEM_TYPE_EXECUTION_ERROR);

The problem-type parameter can have the following values:

•

PROBLEM_TYPE_COMPILATION_ERROR

•

PROBLEM_TYPE_WRONG_RESULTS

•

PROBLEM_TYPE_EXECUTION_ERRORS

Step 5
Once the procedure has been created, the next step is to execute the diagnosis task by calling DBMS_SQLDIAG.EXECUTE_
DIAGNOSIS_TASK with the task ID returned by the CREATE_DIAGNOSIS_TASK from Step 4 . After a short delay, the SQL
Repair Advisor returns. As part of its execution, the SQL Repair Advisor keeps a record of its findings, which can be
accessed through the reporting facilities of SQL Repair Advisor.
DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK (MV_id);

Step 6
Once the task is executed, the next step is to report the task using DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK . If the
SQL Repair Advisor was able to find a work-around, it recommends an SQL patch. An SQL patch is similar to an SQL
profile; but unlike the SQL profile, it is used for work-around compilation or execution errors:

BEGIN
REP_OUT:= DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK (MV_id, DBMS_SQLDIAG.TYPE_TEXT);
DBMS_OUTPUT.PUT_LINE ('Report: ' || REP_OUT);
END;
/

Step 7
From Step 6, if the patch was found, the next step is to apply the patch using the DBMS_SQLDIAG.ACCEPT_SQL_PATCH
procedure, which takes the task_name as an argument:

EXECUTE DBMS_SQLDIAG.ACCEPT_SQL_PATCH (task_name => ' ERR_INC_52777_TASK ', task_owner => 'SYS',
REPLACE => TRUE);