Oracle 10G 新特性——ADDM和查询优化建议器

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2006-04-07 14:47:13

分享到 

利用ADDM解决性能问题 从最终权威那获得SQL调优的帮助：Oracle数据库本身！通过使用SQL profiles来确定查询行为、学习如何使用ADDM快速和轻松地解决普通的性能问题。 作为一个高级DBA，你当然不愿总是被调优某条SQL语句这种杂事缠身。 在10g中，你有了自动数据库诊断监视器（Automatic Database Diagnostic Monitor ADDM），他是一个不知疲倦的收集数据库性能统计信息来定位性能瓶颈、分析SQL语句和不停的提供各种类型的建议以提高性能的机器DBA，它一般和其他“建议器”如SQL Tuning Advisor一起工作。在本文中，你将了解到它是如何工作的。 自动数据库诊断监视器 ADDM 你已经了解了AWR，它是从数据库的定期采集数据（即快照）中收集与性能相关的细节度量数据的工具。获取到一个快照后，ADDM就会彻底的分析从不同快照中比较得出的度量数据，并给出必要动作的建议。找到一个问题后，ADDM可能接着就调用其他Advisor（如SQL Tuning Advisor）以提供提高性能的建议。 不用文字来解释这个特性了，下面来展示一下它是如何工作的。假如你需要定位一个无法解释的性能问题。在这一例子中，你已经知道了哪个SQL语句需要调优，或者你知道哪个语句有问题。而实际情况下，你可能根本没有什么有用信息。 在10g中要做一次诊断，你就必须在相关的快照中选择几个快照以做深入分析。在10g企业管理器中，选择“Advisor Central”页，然后点击“ADDM”链接，打开页面如下： 图一 创建一个ADDM任务   在这个页面中，你能创建被ADDM分析的任务。你知道性能问题出现在下午11点，因此选择这一范围（输入一个起始时间、一个结束时间）内的快照。也可以通过点击哪个照相机图标来选择起始、结束范围。选择好时间范围后，点击OK按钮，打开页面如下： 图二 ADDM查找结果   在这，ADDM发现在这个时间范围内有两个与性能相关的问题：一些SQL语句消耗了大量的CPU时间，导致数据库显著变慢。基于以上查询结果，ADDM建议优化这些语句，并将它们高亮显示在图中。 如果你点击每个查询结果，ADDM会显示更多细节。例如，点击点击以上一个问题，显示结果如下： 图三 ADDM查询结果的细节   这你可以看到导致这一问题的SQL语句。ADDM在操作部分里建议你将这条语句提交给SQL Tuning Advisor来处理。你可以通过点击它后面的按钮立即运行这一任务，那会启动SQL Tuning Advisor。 在图2中，你可能已经注意到了一个名叫“视图报告”的按钮。除了在每个web页面上提供建议外，ADDM还可以为一个更快的一次分析提供文本报告。列表1显示了在我们的文本报告例子中复杂的建议。请注意报告中提供的相关的细节，如问题SQL语句、它的hash值等等。SQL ID是用于通过命令行在企业管理器的SQL Tuning Advisor页面中独立分析的。 表1：           DETAILED ADDM REPORT FOR TASK 'TASK_2024' WITH ID 2024           ------------------------------------------------------               Analysis Period: 16-MAY-2004 from 22:00:31 to 23:01:54          Database ID/Instance: 3607854283/1       Database/Instance Names: STARZ10/starz10                     Host Name: starz              Database Version: 10.1.0.2.0                Snapshot Range: from 1863 to 1865                 Database Time: 1123 seconds         Average Database Load: .3 active sessions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FINDING 1: 93% impact (1041 seconds) ------------------------------------ SQL statements consuming significant database time were found.     RECOMMENDATION 1: SQL Tuning, 93% benefit (1041 seconds)       ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID          "8np5s8nvpv7v3".          RELEVANT OBJECT: SQL statement with SQL_ID 8np5s8nvpv7v3 and          PLAN_HASH 101258408          select cust_name from bookings o          where status not in          (select status from bookings_hist          where folio_id = o.folio_id          and last_mod_time = o.last_mod_time)  FINDING 2: 89% impact (1000 seconds) ------------------------------------ Time spent on the CPU by the instance was responsible for a substantial part of database time.     RECOMMENDATION 1: SQL Tuning, 89% benefit (1000 seconds)       ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID          "8np5s8nvpv7v3".          RELEVANT OBJECT: SQL statement with SQL_ID 8np5s8nvpv7v3 and          PLAN_HASH 101258408          select cust_name from bookings o          where status not in          (select status from bookings_hist          where folio_id = o.folio_id          and last_mod_time = o.last_mod_time) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~           ADDITIONAL INFORMATION           ---------------------- Wait class "Administrative" was not consuming significant database time. Wait class "Application" was not consuming significant database time. Wait class "Cluster" was not consuming significant database time. Wait class "Commit" was not consuming significant database time. Wait class "Concurrency" was not consuming significant database time. Wait class "Configuration" was not consuming significant database time. Wait class "Network" was not consuming significant database time. Wait class "Scheduler" was not consuming significant database time. Wait class "Other" was not consuming significant database time. Wait class "User I/O" was not consuming significant database time.   The analysis of I/O performance is based on the default assumption that the average read time for one database block is 10000 micro-seconds.   An explanation of the terminology used in this report is available when you run the report with the 'ALL' level of detail. 收集了每个AWR快照后再使用ADDM，因此建议都是基于相邻的两个快照信息。所以，如果分析范围仅仅是两个相邻的快照，你就没有必要像上面一样创建一个ADDM任务。如果你想分析两个不相邻的快照，那就需要创建ADDM任务。 记住，这并不是ADDM的全部功能。它还有很多对于内存管理、段管理、redo/undo等等其他方面的分析和建议。我们无法将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 这个语句不是很难调优。有两个方法启动建议器：使用企业管理器或使用命令行。 首先看下如何使用命令行来启动。可以通过调用包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; 结果输出再表2中： 表2：   DBMS_SQLTUNE.REPORT_TUNING_TASK('FOLIO_COUNT') -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name   : FOLIO_COUNT Scope              : COMPREHENSIVE Time Limit(seconds): 120 Completion Status  : COMPLETED Started at         : 04/06/2004 01:01:31 Completed at       : 04/06/2004 01:01:31 ------------------------------------------------------------------------------- SQL ID  : 1mzhrcv0bg0pw SQL Text: select account_no from accounts where old_account_no = 11 ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- Index Finding (see explain plans section below) --------------------------------------------------   The execution plan of this statement can be improved by creating one or more   indices.     Recommendation (estimated benefit: 94.26%)   ------------------------------------------     Consider running the Access Advisor to improve the physical schema design     or creating the recommended index.     create index ARUP.IDX$_00001 on ARUP.ACCOUNTS("OLD_ACCOUNT_NO")     Rationale   ---------     Creating the recommended indices significantly improves the execution plan     of this statement. However, it might be preferable to run "Access Advisor"     using a representative SQL workload as opposed to a single statement. This     will allow to get comprehensive index recommendations which takes into     account index maintenance overhead and additional space consumption.   ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION -------------------------------------------------------------------------------   1- Original -----------   DBMS_SQLTUNE.REPORT_TUNING_TASK('FOLIO_COUNT') -------------------------------------------------------------------------------- Plan hash value: 290945073   ------------------------------------------------------------------------------ | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |          |     1 |    10 |    34   (0)| 00:00:01 | |   1 |  TABLE ACCESS FULL| ACCOUNTS |     1 |    10 |    34   (0)| 00:00:01 | ------------------------------------------------------------------------------   2- Using New Indices -------------------- Plan hash value: 633506680   ------------------------------------------------------------------------------------------ | Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |            |     1 |    10 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| ACCOUNTS   |     1 |    10 |     2   (0)| 0:00:01 | |   2 |   INDEX RANGE SCAN | IDX$_00001 |     1 |      |     1   (0)| 00:00:01 | ------------------------------------------------------------------------------------------ 请仔细看这些建议。建议器说需要在字段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或者应用开发人员决定在这个环境下是否要采用这个建议。 高级调优：SQL Profiles 众所周知，优化器通过检查基于与查询相关的对象的统计数据的最低代价来决定一个查询的查询计划。典型情况下，查询与多个表相关，优化器计算通过检查这些表的统计数据计算最低代价，但是它却不知道它们之间的关系。 例如，一个状态为DELINQUENT的帐号拥有少于$1000的余额。如果谓词中仅有关于DELINQUENT的过滤的子句，一个连接表ACCOUNTS和BALANCES的查询将返回较少的记录。优化器不止这一复杂关系；但是建议器知道，它通过数据并存储在一个SQL Profile中“猜测”到这个关系。通过读取SQL Profile，优化器不仅知道表中数据的分布，还可以知道数据之间的关系。这一额外的信息让优化器产生一个更佳的查询计划，因此会有一个调优的更好的查询。 SQL Profile不需要手工在查询代码中增加提示来调优SQL语句。因而，SQL Tuning Advisor可以不需要改变代码来调优应用了。 这最主要的一点就是，和对象的统计数据不同，SQL Profiles和一个查询映射，而不是和对象映射。另外一个关于同样两张表——ACCOUNTS和BALANCES——的查询可以有一个完全不同的profile。通过使用这些关于查询的元数据，Oracle能提高优化性能。 如果能够创建一个profile（它是在SQL Tuning Advisor的会话期间创建的，在这期间，Advisor产生Profile并建议你接受它。）。除非profile被接受，否则它不会绑定到语句。你可以通过以下语句随时接受profile： begin    dbms_sqltune.accept_sql_profile (       task_name   => 'FOLIO_COUNT',       name        => 'FOLIO_COUNT_PROFILE'       description => 'Folio Count Profile',       category    => 'FOLIO_COUNT'); end; 这一命令将一个早先由Advisor产生的名为FOLIO_COUNT_PROFILE的profile绑定到与前面例子中所描述名叫FOLIO_COUNT的调优任务相关的语句上。（请注意，尽管不是DBA，而是Advisor能创建一个SQL Profile，但只有你能决定是否使用它）。 可以通过视图DBA_SQL_PROFILES查看已经产生的SQL Profiles。字段SQL_TEXT的内容是与profile相关的SQL语句；字段STATUS表明profile是否激活可用。（尽管一个profile已经绑定到一条语句，但它还是需要通过激活来影响查询计划。） 使用ADDM和SQL Tuning Advisor 除了上面描述的三种情况，SQL Tuning Advisor还会鉴别出与一个查询相关的对象中哪些没有统计数据。所以，Advisor执行了四种不同类型的任务： ·         检查对象是否由合法、可用的统计数据用于相应的优化。 ·         试图重写语句以获得更好的性能，并提供重写建议 ·         检查访问路径，看是否可以通过增加额外结构（如索引、物化视图）来提高性能 ·         创建SQL Profiles并将它们绑定到查询语句 基于这些功能，我认为ADDM和SQL Tuning Advisor最少在三种情况下是十分强大的工具： l 反应调优：你的应用一下子变得性能很差。通过使用ADDM，你可以将问题定位到一条或一组SQL语句，它们会被显示在ADDM页面中。随着ADDM的建议，你可以调用SQL Tuning Advisor和修正问题。 l 预先调优：应用运行良好。然而你希望确认所有必要的维护任务都在执行，并且想要知道查询能否调优得更好。你可以在standalone模式下启动SQL Tuning Advisor来确定调优得可能性。 l 开发调优：当在开发的代码测试阶段时，有很多机会来调优查询，而不要等到QA或者生产阶段。你可以在代码最终开发出来前使用命令行方式来调优单个的SQL语句。 使用企业管理器 前面的例子时特地介绍如何在命令行方式下使用SQL Tuning Advisor，这对于创建预先任务脚本比较有用。然而，在大多数情况下。你需要针对用户报告的问题实施调优。10g企业管理器对这种情况就非常方便。 如何使用企业管理器来诊断和调优SQL语句：在数据库主页，点击屏幕底部的“Advisor Central”链接，这就会启动包括所有advisor的页面。下一步，点击图4所示屏幕上方的“SQL Tuning Advisor” 图4：企业管理器的Advisor Central   启动了SQL Tuning Advisor后，如图5 所示，选择“Top SQL”： 图5：SQL Tuning Advisors   这样就启动了一个如图6所示的页面，里面有一张图，包括了在一个时间维度里跟踪的各种等待分类。 图6：Top SQL Chooser   上图中用红色圈标明了一块灰色矩形区域。通过鼠标拖动矩形区域到CPU等待很高的地方。页面的下面部分将显示那一时间段的相关的SQL语句： 图7：基于活动信息选择SQL语句   如你所见，显示在最上面的SQL语句消耗了最多的CPU。点击语句ID查看关于它的细节，会打开以下图： 图8：SQL细节   在这个图中，你可以见到那段时间导致CPU消耗这么搞的那条SQL语句。你可以点击按钮“Run SQL Tuning Advisor”（用红圈标出）来运行Advisor。如下图所示： 图9：SQL Tuning Advisor计划   在advisor计划中，你可以决定任务类型和执行多少分析。例如，在上图中，选择“comprehensive”分析，Advisor会立即运行。Advisor运行完成后，你可以见到它的建议，如图10所示： 图9：Advisor建议   上面藐视的过程和你在命令行方式见到的很类似。然而，这个流程对于你在实际环境中遇到的问题更有用：发现导致问题的原因、接受如何修正它的建议。 总结 ADDM是一个强大的工具，它拥有能分析性能度量数据的“大脑”，并能基于经验丰富的Oracle专家的总结出的最佳经验和方法学给出优化建议，并且全是自动的。这些功能不仅能告诉DBA发生了什么问题及为什么会产生这样的问题，最终要的是，它能告诉下一步如何去做。