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》
本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:czmcj@163.com
相关推荐
使用ORACLE SQL Tuning advisor快速优化低效的SQL语句, 这里是一个使用范例,有9个步骤, 后面 其他语句,都按照这个步骤来做就可以了。
关于oracle的SQL Tuning Advisor全面的使用介绍,包括分析awr、cursor、sql_set、sql文本中获取sql并且建立、执行、获取结果的方法。并且介绍了sql_set,sql_profile的操作。里面涉及到的内容都有可执行的代码。
oracle 10gADDM 和 SQL Tuning Advisor
SQL调整工具集(SQL Tuning Set,STS)是Oracle 10g的SQL Tuning Advisor特性的一个组成部分。每个调整工具集都包含一个或几个SQL语句,以及正确解释它们所需的上下文信息。SQL Tuning Advisor用一个调整工具集作为...
基本信息 出版社: 清华大学出版社; 第1版 (2009年1月1日) 平装: 594页 语种: 简体中文 开本: 16 ... 使用最新的Oracle Database 11g工具——Oracle Total Recall和Oracle Flashback Data Archive等。
Oracle+10G+最佳20位新特性:SQL+Advisor+和+ADDM
SQLAdvisor是由美团点评公司技术工程部DBA团队(北京)开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析,结合分析SQL中的where条件、聚合条件、多表Join关系 给出索引优化建议。目前SQL...
基本信息 出版社: 清华大学出版社; 第1版 (2009年1月1日) 平装: 594页 语种: 简体中文 开本: 16 ... 使用最新的Oracle Database 11g工具——Oracle Total Recall和Oracle Flashback Data Archive等。
OCPOCA认证考试指南全册:Oracle Database 11g(1Z0-051,1Z0-052,1Z0-053) 共2部分:此为第002部分 基本信息 原书名: OCA/OCP Oracle Database 11g All-in-One Exam Guide with CD-ROM: Exams 1Z0-051, 1Z0-052...
SQL Access Advisor 获得关于基于表(而不仅仅是数据)的实际使用的最佳表设计的建议。 PL/SQL:高效的编码 在不同的事件处触发几次的触发器以及迫使相同类型的触发器排队的能力是一些新增亮点。 RMAN 探究 Data ...
解压密码:123 ||SQL Sentry Performance Advisor是一款出色的数据库监控工具,它允许您快速、轻松地跟踪和识别SQL服务器之间的性能问题。它是一个可靠和强大的应用程序,提供了各种智能工具,以帮助您立即识别和...
Oracle10g性能优化,内容: • 使用适合于可用工具的Oracle 数据库优化方法 • 利用数据库Advisor 主动优化Oracle 数据库 • 使用基于自动工作量资料档案库的工具优化数据库 • 使用Statspack 报表优化数据库 • ...
官方资料:Oracle白皮书_Oracle数据库11g中的分区 分区的优势:分区的基本知识;使用分区提高可管理性;使用分区提岛性能;使用分区提高可用性;分区一为业务建模:基本分区策略;分区扩展;PARTITION ADVISOR;分区策略和...
美团技术团队出品的用来优化sql的工具,可以对SQL语句进行优化,分析瓶颈
IYSQL - 基于SQLAdvisor与Soar的开源Web分析工具,用于改进你的SQL.
SQLAdvisor是美团开源的一款SQL索引优化建议工具, 是由美团点评公司技术工程部DBA团队(北京)开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析,结合分析SQL中的where条件、聚合条件、...
Oracle 10g之后的优化器支持两种模式,一个是normal模式,一个是tuning模式。在大多数情况下,优化器处于normal模式。基于CBO的normal模式只考虑很小部分的执行计划集合用于选择哪个执行计划,因为它需要在尽可能短...
分区的优势 分区的基本知识 使用分区提高可管理性 使用分区提高性能 使用分区提高可用性 分区 — 为业务建模 基本分区策略 ...PARTITION ADVISOR 分区策略和扩展概述 利用分区进行信息生命周期管理 总结