Oracle 11g SQL Repair Advisor(原创)

SQL Repair Advisor

When a SQL statement failure results in a critical error, the new SQL Repair Advisor analyzes the statement and in many cases recommends a patch to fix the failed statement. The fix is usually in the form of a recommendation to apply a SQL patch to bypass the failure, without changing the SQL statement itself. Applying the recommended patch leads the query optimizer to select an alternate execution plan for the problem statement.

The SQL Repair Advisor tries to recommend a SQL patch when it’s unable to find a fix for the problem SQL statement(s). A SQL patch is very similar to a SQL profile, but it’s used mostly as a workaround to fix a failing SQL statement.

There are two ways you can invoke the SQL Repair Advisor. The first way is to use the Support Workbench to launch the SQL Repair Advisor. You can also use the new DBMS_SQLDIAG package to invoke the SQL Repair Advisor.

Using the DBMS_SQLDIAG Package

Although using the Enterprise Manager is the most straightforward way to invoke the SQL Repair Advisor, you can also use the new DBMS_SQLDIAG package to invoke the advisor. In the following example, I first create a SQL Repair Advisor task and then apply and test the SQL patch offered by it:

1. Identify the problem SQL statement, as shown in the following example,

which results in an error:

SQL> delete from t t1 where t1.a = 'a'

and rowid <> (select max(rowid)

from t t2 where t1.a= t2.a and t1.b = t2.b and t1.d=t2.d);

You can fix the error resulting from executing the SQL statement by using the SQL Repair Advisor.

2. Create a SQL Repair Advisor diagnostic task by passing the offending SQL query as the value for the sql_text attribute:

SQL> declare

2 report_out clob;

3 task_id varchar2(50);

4 begin

5 task_id := dbms_sqldiag.create_diagnosis_task(

6 sql_text=>' delete from t t1 where t1.a = 'a'

and rowid <> (select max(rowid) from t t2

where t1.a= t2.a and t1.b = t2.b

and t1.d=t2.d)',

8 task_name =>'test_task1',

9 problem_type=>dbms_sqldiag.problem_type_compilation

_error);

10* end;

PL/SQL procedure successfully completed.

The previous code specifies the SQL statement you want the SQL Repair Advisor to analyze. In addition, it specifies the task name and the problem type. I chose problem_type_compilation_error as the value for the problem_type parameter in this example. The other possible value for the problem_type parameter is problem_type_execution_type.

You are now ready to execute the task, as shown in the next step.

3. Execute the diagnostic task you created earlier, by passing the task name as a parameter to the EXECUTE_DIAGNOSTIC_TASK procedure:

SQL> exec dbms_sqldiag.execute_diagnosis_task('test_task1');

PL/SQL procedure successfully completed.

The EXECUTE_DIAGNOSTIC_TASK procedure has only a single param-eter, task_name.

4. Use the REPORT_DIAGNOSTIC_TASK procedure to get an analysis of the diagnostic task you executed:

SQL> declare rep_out clob;

2 begin

3 rep_out := dbms_sqldiag.report_diagnosis_task

4 ('test_task1',dbms_sqldiag.type_text);

5 dbms_output.put_line ('Report : ' || rep_out);

6*end;

SQL> /

 

Report : GENERAL INFORMATION

SECTION

-------------------------------------------------

Tuning Task Name : test_task1

Tuning Task Owner : SYS

Tuning Task ID : 3219

Workload Type : Single SQL Statement

Execution Count : 1

Current Execution : EXEC_3219

Execution Type : SQL DIAGNOSIS

Scope : COMPREHENSIVE

Time Limit(seconds) : 1800

Completion Status : COMPLETED

Started at : 10/20/2007 06:33:42

Completed at : 10/20/2007 06:36:45

Schema Name : SYS

SQL ID : 44wx3x03jx01v

SQL Text : delete from t t1 where t1.a = 'a'

and rowid <> (select max(rowid)

from t t2 where t1.a= t2.a

and t1.b = t2.b and t1.d=t2.d)

...

PL/SQL procedure successfully completed.

5. If the SQL Repair Advisor recommends a patch, you can accept the patch by executing the ACCEPT_SQL_PATCH procedure, as shown here:

SQL> exec dbms_sqldiag.accept_sql_patch (task_name=> 'test_task1',task_owner=> 'SYS');

You can now execute the problem SQL statement to ensure that the workaround patch did fix the problem. Check the explain plan output for the SQL statement to make sure it shows use of the SQL patch. The DBA_SQL_PATCHES view contains the names of all the patches recommended by the SQL Repair Advisor.

If you want to drop the SQL patch for any reason, you can do so by using the DROP_SQL_PATCH procedure. You can remove the SQL patch, for example, if you receive an official patch from Oracle to fix the problem. You can also drop the SQL patches when you upgrade your database to the next patch set or Oracle release.

You can export a SQL patch into another database by using a staging table. Inserting a patch is called packing the staging table and creating patches using the staging table is called unpacking. The following exercise shows how to export a SQL Patch.

Exporting a SQL Patch to Another Database
1. Create a staging table by executing the CREATE_STGTB_SQLPATCH procedure:
SQL> exec dbms_sqldiag.create_stgtab_sqlpatch (
table_name => 'mystagetab1',
schema_name => 'hr');
In the next step, you’ll use this table to store the SQL patch information.
2. Execute the PACK_STGTAB_SQLPATCH procedure to write SQL patch information to the staged table you created in Step 1.
SQL> exec dbms_sqldiag.pack_stgtab_sqlpatch (
staging_table_name => 'mystagetab1');
This will copy all SQL patches in the DEFAULT category to the staging table mystgtab1. You can now move the staging table to the new database using the Data Pump Export and Import utilities.
3. Use the UNPACK_STGTAB_SQLPATCH procedure to create SQL patches on the new system using the patches in the staging table.
SQL> exec dbms_sqldiag.unpack_stgtab_sqlpatch
(staging_table_name => 'mystgtab1');
By default, Oracle will unpack all patches in the staging table and apply those patches to the target database.

 

参考至:《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》

本文原创，转载请注明出处、作者

如有错误，欢迎指正

邮箱:[e-mail]