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

利用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分析的任务。你知道性能问题出现在下午11点，因此选择这一范围（输入一个起始时间、一个结束时间）内的快照。也可以通过点击哪个照相机图标来选择起始、结束范围。

 

在这，ADDM发现在这个时间范围内有两个与性能相关的问题：一些SQL语句消耗了大量的CPU时间，导致数据库显著变慢。基于以上查询结果，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语句。