Repairing SQL Failures with the SQL Repair Advisor

About the SQL Repair Advisor

You run the SQL Repair Advisor after a SQL statement fails with a critical error. The advisor analyzes the statement and in many cases recommends a patch to repair the statement. If you implement the recommendation, the applied SQL patch circumvents the failure by causing the query optimizer to choose an alternate execution plan for future executions.

Running the SQL Repair Advisor

You run the SQL Repair Advisor from the Problem Details page of the Support Workbench. The instructions in this section assume that you were already notified of a critical error caused by your SQL statement and that you followed the workflow described in "Investigating, Reporting, and Resolving a Problem".

To run the SQL Repair Advisor:

1. Access the Problem Details page for the problem that pertains to the failed SQL statement.

   See "Viewing Problems with the Enterprise Manager Support Workbench" for instructions.

2. In the Investigate and Resolve section, under the Self Service tab, under the Resolve heading, click SQL Repair Advisor.

   
   Description of the illustration advisor_access1.gif
   
   

   The SQL Repair Advisor page appears.

3. Enter an optional task name, set an optional time limit for the advisor task, and adjust settings to schedule the advisor to run either immediately or at a future date and time.

4. Click Submit.

   A "processing" page appears. After a short delay, the SQL Repair Results page appears.

   
   Description of the illustration sql_repair_advisor_results.gif
   
   

   A check mark in the SQL Patch column indicates that a recommendation is present. The absence of a check mark in this column means that the SQL Repair Advisor was unable to devise a patch for the SQL statement.

5. If a recommendation is present, click View to view the recommendation.

   The Repair Recommendations page appears, showing the recommended patch for the statement.

6. Click Implement.

   The SQL Repair Results page returns, showing a confirmation message.

7. (Optional) Click Verify using SQL Worksheet to run the statement in the SQL worksheet and verify that the patch successfully repaired the statement.

Viewing, Disabling, or Removing a SQL Patch

After you apply a SQL patch with the SQL Repair Advisor, you may want to view it to confirm its presence, disable it, or remove it. One reason to remove a patch is if you install a later release of Oracle Database that fixes the bug that caused the failure in the patched SQL statement.

To view, disable, or remove a SQL patch:

1. Access the Database Home page in Enterprise Manager.

   For Oracle Enterprise Manager Database Control, see Oracle Database 2 Day DBA for instructions. For Oracle Enterprise Manager Grid Control, go to the desired database target.

2. At the top of the page, click Server to display the Server page.

3. In the Query Optimizer section, click SQL Plan Control.

   The SQL Plan Control page appears. See the online help for information about this page.

4. At the top of the page, click SQL Patch to display the SQL Patch subpage.

   The SQL Patch subpage displays all SQL patches in the database.

5. Locate the desired patch by examining the associated SQL text.

   Click the SQL text to view the complete text of the statement.

6. To disable the patch, select it, and then click Disable.

   A confirmation message appears, and the patch status changes to DISABLED. You can later reenable the patch by selecting it and clicking Enable.

7. To remove the patch, select it, and then click Drop.

   A confirmation message appears.