9.8 Repairing SQL Failures with the SQL Repair Advisor

«« Previous
Next »»

In the rare case that a SQL statement fails with a critical error, you can run the SQL Repair Advisor to try to repair the failed statement.

1. 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.

2. Running the SQL Repair Advisor


You run the SQL Repair Advisor from the Problem Details page of the Support Workbench.

Typically, you do so when 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.

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

Repairing SQL Failures with the SQL Repair Advisor
This image is a partial screen snapshot of the Problem Details page. On the left side of the page, arranged vertically, are several read-only fields that provide problem details. The right side of the page is occupied by the Investigate and Resolve section, which contains several subsections with links. The link entitled SQL Repair Advisor appears under the Resolve heading.

3. On the SQL Repair Advisor page, complete these steps:

A. Modify the preset task name if desired, optionally enter a task description, modify or clear the 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.

B. Click Submit.

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

Repairing SQL Failures with the SQL Repair Advisor
This is a partial screenshot of the SQL Repair Results page. The main feature on the page is a table entitled Recommendations. The table has the following columns: Select, SQL Text, Parsing Schema, SQL ID, and SQL Patch. There is one row in the table, and it is selected. The SQL Patch column has a check mark in it for that row. Above the table is a View button.

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.

Note:

If the SQL Repair Results page fails to appear, then complete these steps to display it:
1. Go to the Database Home page.
2. From the Performance menu, select Advisors Home.
3. On the Advisor Central page, in the Results list, locate the most recent entry for the SQL Repair Advisor.
4. Select the entry and click View Result.

4. If a recommendation is present (there is a check mark in the SQL Patch column), then click View to view the recommendation.

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

5. Click Implement.

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

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

3. 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 Cloud Control.

2. From the Performance menu, select SQL, then SQL Plan Control.
The SQL Plan Control page appears.

3. Click SQL Patch to display the SQL Patch subpage.
The SQL Patch subpage displays all SQL patches in the database.

4. Locate the desired patch by examining the associated SQL text.
Click the SQL text to view the complete text of the statement. After viewing the SQL text, click Return.

5. To disable the patch on the SQL Patch subpage, 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.

6. To remove the patch, select it, and then click Drop.
A confirmation message appears.

«« Previous
Next »»