Oracle的自动数据库诊断监控程序剖析

    来源：赛迪网技术社区    作者：sanandi

从最终权威那儿获得关于 SQL 调整的帮助：该权威就是 Oracle 数据库本身！使用 SQL 配置文件进行查询，并了解如何使用 ADDM 来快速、轻松解决常见的性能问题。

迄今为止这是平静的一天：在数据库中没有重大问题发生，不需要去灭“火”。您几乎可以放松一下了；接下来正好可以抓紧处理那些重要任务了，如对 RMAN 调整参数或多个块大小进行调整等。

突然，一位开发人员出现在您面前。他的 SQL 查询要运行很长一段时间。他问您是否能尽快调整该查询使其能够“工作”。

也许您放松得太早了。您最初的日程安排是花些时间制定战略决策，以使您的数据库运行得更好、更快、更安全。例如，确保数据库是可恢复的，增强底层技术或研究安全性更新等等。相反，您将又花一天的时间集中处理 SQL 等战术活动，而很少或没有时间来考虑战略问题。

作为一名战略 DBA，您想把自己从日常琐事中解脱出来，更多地关注那些引人深思的领域。让一名助理 DBA 帮助您做那些琐事难道不好吗？

有了 Oracle 数据库 10g，您就有了一位自动数据库诊断监控程序 (ADDM) 形式的助理 DBA，这种机器人式的 DBA 会不知疲倦地反复搜索数据库性能统计，以标识瓶颈、分析 SQL 语句、并据此提供多种改进性能的建议，它通常与其他“顾问”（如 SQL Tuning Advisor）一起使用。在这篇文章中，对该过程的工作方式进行了概述。

自动数据库诊断监控程序 (ADDM)

在第 6 周，您了解到被称作快照的自动负载信息库 (AWR)，它定期从数据库中收集详细的与性能相关的度量标准。每次快照后，调用 ADDM 来彻底分析源自快照间差异的数据和度量标准，然后就必要的操作提出建议。正如我早先所提及的，发现问题后，ADDM 可能会依次调用其他顾问（如 SQL Tuning Advisor），来提出改进建议。

我将为您完全展示（而不是用文字来解释）这种特性是如何运行的。假定您正设法诊断一个不可解释的性能问题。在我们介绍的示例中，您知道了哪些 SQL 语句需要调整，或至少知道了哪些 SQL 语句存在问题。但是在现实生活中，您可能没有这些有用信息。

要在 10g 中执行诊断，您将在相关的时间间隔内选择快照进行深入、透彻的分析。在 Enterprise Manager 10g 中，从 Database 主页上，您将选择 "Advisor Central"，然后单击 "ADDM" 链接，它将出现一个类似于图 1 的页面。

　　

图 1：创建 ADDM 任务

在该页中，您可以创建 ADDM 要分析的任务。您知道性能问题发生在晚上 11 点左右，因此选择那个时间间隔的快照，通过 "Period Start" 和 "Period End" 值进行指示。您也可以单击照相机图标，指示开始和终止快照的时间间隔，如此处的红色椭圆形所示。选择时间间隔后，按下 "OK" 按钮，将出现一个类似于图 2 所示的页面。

　　

图 2：ADDM 查找结果

这里 ADDM 在该时间间隔内标识了两个关键的、相关的性能问题：某些 SQL 语句消耗着重要的 CPU 时间，从而使数据库的速度显著减慢。基于这些查找结果，ADDM 建议对图中突出显示的那些语句进行 SQL 调整。

如果您单击某条查找结果，ADDM 会显示更多详细信息。例如，单击问题查找结果，将会出现一个类似于图 3 所示的页面。

　　

图 3：ADDM 查找结果的详细信息

在此您可以看到引发该问题的特定 SQL 语句。ADDM 建议您用 SQL Tuning Advisor 对该 SQL 语句进行彻底的分析，正如在 "Action" 部分中所提到的那样。您可以通过单击它旁边的按钮立即运行该任务，这将调用 SQL Tuning Advisor。

在图 2 中，您可能注意到了一个名称为 "View Report" 的按钮。除了在单独的 Web 页面中提供建议外，ADDM 还能够创建纯文本报表，以进行更快速的一步到位的分析。在我们的示例纯文本报表中提出的全面建议。注意报表是如何给出相关细节的，如所考虑的 SQL 语句、它的散列值等。可以在 Enterprise Manager 的 SQL Tuning Advisor 页中或通过命令行将 SQL ID 用于独立的分析。

在收集了每一张 AWR 快照后就会调用 ADDM，因此可以查看基于相邻快照的建议。因此，如果分析的范围只是两张相邻的快照，就不必创建上面所示的 ADDM 任务。如果您想在两张不相邻的快照之间进行分析，就需要创建 ADDM 任务。

记住 ADDM 所能做的远不止这些；正如您在以前的文章中所看到的，它还提供内存管理、段管理、重作/撤消以及更多的分析和建议。由于在一篇简短的文章中描述所有的 ADDM 功能是不可能的，在此我们只关注 SQL Tuning Advisor。现在让我们看看它是如何工作的。

用 SQL Tuning Advisor 访问分析

在一个典型的运行时优化中，优化器生成一组可能的访问路径，并基于对象统计从中选择出最“经济”的路径。但是，那时它没有时间考虑是否能够调整语句、统计是否陈旧、是否能够创建索引等问题。相反，SQL Tuning Advisor 可以执行这种“专家系统”类型的思考。实质上，优化器能够回答的问题是：“基于可用的资源,获得结果的最佳方式是什么？”而 SQL Tuning Advisor 能够回答的问题是：“基于用户的需求，还需要做些什么来增强性能？”

正如您可能预期的那样，这种“思考”消耗了资源（如 CPU）；因此 SQL Tuning Advisor 在调整模式期间处理 SQL 语句，该模式可以在非高峰时间运行。在创建调整任务时，通过在函数中设置 SCOPE 和 TIME 参数来指示这种模式。在数据库活动少的期间运行调整模式是一个好方法，以使常规用户相对不受影响，稍后再进行分析。

这个概念可以通过示例很好地解释。就看看如下所示的开发人员引起您注意的那个查询事例吧。

　　select account_no from accounts where old_account_no = 11

　　

该语句调整起来并不难，但是为了更容易说明问题，假定它很难调整。激发顾问的方式有两种：使用 Enterprise Manager 或简单明了的命令行。

首先，让我们看看如何在命令行中使用它。我们通过调用提供的包 dbms_sqltune 来调用顾问。

　　declare
　　
　　l_task_id　　 varchar2(20);
　　l_sql　　　　 varchar2(2000);
　　begin
　　l_sql := 'select account_no from accounts where old_account_no = 11';
　　dbms_sqltune.drop_tuning_task ('FOLIO_COUNT');
　　l_task_id := dbms_sqltune.create_tuning_task (
　　sql_text　=> l_sql,
　　user_name　=> 'ARUP',
　　scope　　　=> 'COMPREHENSIVE',
　　time_limit => 120,
　　task_name　=> 'FOLIO_COUNT'
　　);
　　dbms_sqltune.execute_tuning_task ('FOLIO_COUNT');
　　end;
　　/

　　

这个包创建并执行了一个名为 FOLIO_COUNT 的调整任务。接下来，您将需要查看任务执行的结果（也就是说，查看建议）。

　　set serveroutput on size 999999
　　set long 999999
　　select dbms_sqltune.report_tuning_task ('FOLIO_COUNT') from dual;

　

显示的输出结果仔细查看这些建议；顾问说您可以通过在 OLD_ACCOUNT_NO 列上创建一个索引来改进性能。更佳的是，如果创建索引，顾问计算了查询成本，从而使潜在的节省量变得更加可定义、更加具体。

当然，考虑到本示例的简单性，通过手动检查也能得到这种结论。但是，可以想象出该工具对于那些更复杂的查询十分有用，因为对这些查询执行手动检查也许是不可能的或不实际的。

查询重构

假定查询更复杂：

　　select account_no from accounts a 
　　where account_name = 'HARRY' 
　　and sub_account_name not in 
　　( select account_name from accounts 
　　where account_no = a.old_account_no and status is not null);

　　

顾问建议如下：

　　1- Restructure SQL finding (see plan 1 in explain plans section)
　　----------------------------------------------------------------
　　The optimizer could not unnest the subquery 
    at line ID 1 of the execution
　　plan.
　　
　　Recommendation
　　　--------------
　　Consider replacing "NOT IN" with "NOT EXISTS" or ensure 
    that columns used on both sides of the "NOT IN" operator 
    are declared "NOT NULL" by adding
　　either "NOT NULL" constraints or "IS NOT NULL" predicates.
　　
　　Rationale
　　　---------
　　A "FILTER" operation can be very expensive because it 
    evaluates the subquery for each row in the parent 
    query.The subquery, when unnested can
　　drastically improve the execution time because 
    the "FILTER" operation is
　　converted into a join.Be aware that "NOT IN" 
    and "NOT EXISTS" might
　　produce different results for "NULL" values.

　　

这一次顾问不会建议任何结构上的更改（如索引），但会通过用 NOT EXISTS 取代 NOT IN的方式很聪明地猜测到调整查询的正确方式。由于两种构造相似但不相同，顾问给出了这种改变的基本原理，并把决定权留给 DBA 或应用程序开发人员，由他们决定该建议是否对环境有效。