说调优

了解使用Oracle 10g新顾问程序的方法，消除调优工作中的猜测。

"自动"一词似乎已经毫无限制地应用于Oracle数据库10g的许多新特性当中了--如自动数据库诊断监测器（ADDM）、自动工作量信息库（AWR）、自动空间管理和自动SQL调优。 但是，先不要认为Oracle正在设法让我们失业，应把"自动"理解为"自动驾驶仪"，而不是"自动开罐器"。没有人会仅仅因为飞机上的仪器内置了某些智能而使其能在高空飞行，就建议将机长从驾驶舱撤出。

同样，对于数据库调优，即使是专家也可能使用一些智能性建议。我们都已采用TKPROF、Explain Plan和Statspack等工具来确保获得最佳性能。 我们已经重新运行统计、删除统计、修改init.ora参数、创建索引、删除索引、重新编写SQL，采用了各种方法，以寻求更好的性能。利用数据库管理员的技巧包来找出问题点的解决方案固然有其优点，但这也是重复而耗时的。Oracle数据库10g内置的自动调优功能不仅提供了这些功能，还提供了更多功能，并达到了极致--性能最佳的数据库--运行速度极快。这都基于Oracle数据库该版本所内置的新的智能型基础架构。

智能型基础架构

Oracle数据库10g综合的智能型基础架构可对整个数据库进行探测，使数据库能够在运行过程中对其自身进行监测和诊断，并通知数据库管理员出现了问题，数据库管理员便可采取有效的纠正措施。

简要地说，Oracle数据库10g中这种新的智能型基础架构的几个关键组件包括AWR、ADDM和一个"自动顾问程序"（automatic advisors）数组，它们可以使数据库管理员避免许多猜测和重复劳动。简单地说，AWR包含了Statspack所提供的功能，还会汇总大量新的统计数据。AWR会收集、处理和维护性能统计数据（默认情况下，AWR每60分钟进行一次数据快照），用于问题检测和自我调优，并将所收集的数据存储在可由ADDM来分析的数据库中。

ADDM蕴含了Oracle公司内外许多Oracle专家的集体智慧，可为有效管理和诊断数据库性能提供基础性的知识和分析。它进行"根本原因"（root-cause）分析，并跨几种重要的数据库对象类（如应用程序、模式和内存利用率）提出具体建议。

例如，对于一个特定的模式对象，ADDM要确定"对数据库块的读写争用耗费大量的数据库时间，"并给出此结论的报告（在通过命令行生成的ADDM报告中或通过Oracle企业管理器［OEM］控制台进行）。这样一个结论的详细信息中可能还会包括这样一个事实：向需要空闲列表的表中插入数据存在着一种高级插入方式。同时会建议："考虑在一个本地管理的表空间中使用Oracle的自动段空间管理……" 建议还可以包括：对数据库资源消耗多于共享的SQL语句运行特定的顾问程序对话。

在以后的专栏中，我将探讨ADDM、AWR及其他一些利用了这一新基础架构智能的新顾问程序。

优化器改进

在这批新工具和智能型基础架构中，数据库管理员能最快享受到的好处之一是快速而轻松地调优SQL语句。SQL调优顾问程序使你可以在不修改源代码的情况下调优SQL语句。这一特性很有用，尤其是对打包的应用程序，例如，当你等待厂家的补丁程序时，但是它也可以用于任何SQL的调优（例如，通过游标高速缓存，或指定一个SQL文本串）。

在进行详细讨论之前，我们先简要地看一看这一特殊顾问程序（明确地讲是优化器）所依赖的隐含功能的概况。

一般来讲，Oracle SQL性能的核心是Oracle基于成本的优化器（CBO），该组件对获得数据的可能途径进行评估，并从许多可能的备选方案中生成最佳执行计划。

执行计划定义了Oracle数据库执行语句所用的步骤组合；它们包括语句所访问的每个表的访问方法以及这些表的排序（连接顺序）。

优化器可确定执行特定SQL语句的最有效方式。对于任一特定的SQL语句，指定其有效的可选方式的可能数目后，优化器会对它们进行快速评估，在一秒钟之内生成一个执行计划。

除了优化器的这一所谓"正常"模式外，Oracle数据库10g现在还提供一种"调优"模式（在Oracle文献中有时称作"自动调优优化器"）。正如其名称意义所示，优化器的调优模式明确地用于SQL调优对话（使用SQL调优顾问程序和SQL访问顾问程序），以生成可在运行时加速性能的附加信息。调优模式包含了正常模式的性能，同时还提供扩展功能，因此它能够在创建执行计划的过程中进行进一步的分析。

在调优模式下，优化器进行四个关键级别的分析，生成可以为优化器返回SQL语句结果提供附加信息的统计数据：

SQL统计数据分析。优化器会检查缺少或陈旧的统计数据，并给出适当的建议--例如，为指定的数据库对象收集统计数据--以确保生成最佳执行计划。（优化器还会生成附加信息，如果建议的措施未被采用，这些信息会存储在一个在运行时使用的SQL附加信息集合中）。

SQL附加信息集合。优化器会进行更广泛的分析，并汇总可以使查询运行更为理想的必要附加信息，将其存储在一个SQL附加信息集合中。SQL附加信息集合包含的信息可以使SQL编译器对特定的SQL文本的执行计划进行优化。SQL附加信息集合在运行时使用（当优化器返回正常模式时），用于提高SQL的性能，但不改变源代码。

SQL访问分析。优化器对访问方式进行分析，核查索引是否处于最佳使用状态，如果不是，则建议适当地创建索引，以提供更快的访问方式。（可以单独运行不同的SQL访问顾问程序工具来汇总所有访问结构的建议--特别是物化视图、物化视图记录和整个SQL工作量的索引。 在以后的专栏中我会介绍这一工具。）

SQL结构分析。当优化器构建执行计划、给出提高性能的建议时，它会分析SQL语句的结构（语义、语法和设计），生成大量的注释和诊断。 例如，为了显著提高性能，优化器可能会建议用NOT EXISTS代替NOT IN，尽管NOT EXISTS在语义上与NOT IN不同，但它们所产生的结果相同。（不过，只有在该查询的相关连接列中没有NULL值时，你才应该这样更改--这就是SQL调优顾问程序让你来决定是否采用结构分析所生成建议的原因。）

如果在"综合"模式下运行SQL调优顾问程序，四个分析都会进行；但是SQL统计数据分析、SQL访问分析和SQL结构分析只在"有限"模式下进行--不生成SQL附加信息集合。如果你想调优应用程序代码，如含有打包应用程序的代码，你应使用综合模式，以确保获得SQL附加信息集合。

优化器的调优模式在SQL调优顾问程序和SQL访问顾问程序对话期间使用。

在综合模式下使用SQL调优顾问程序来为一个或多个SQL语句生成SQL附加信息集合可能会花很长时间--优化器在忙于汇总和生成附加统计数据和注释。不过，通过更改默认设置值（30分钟），可以限制优化器花在特定调优任务上的时间。

不过，在调优SQL语句，尤其是在处理打包应用程序（其SQL源代码不能直接控制）时，使用SQL附加信息集合将提供极大的好处。

SQL调优顾问程序可以在OEM基于Web的新控制台接口内的很多地方启动，也可以通过DBMS_SQLTUNE包用（AQL*Plus）命令行来进行访问。要使用SQL调优顾问程序（或任何其他顾问程序），都需要具有ADVISOR（顾问程序）权限（Oracle数据库10g版本最新提供；默认情况下，数据库管理员具有ADVISOR权限）。

新的自管理Oracle数据库的一个创新就是诊断自身性能问题的能力。Oracle 数据库10g中包括一个内置于数据库核心的自诊断引擎，叫做自动数据库诊断监测器（ADDM）。ADDM以较短的定期间隔（默认为30分钟）自动监测数据库状态，提供持续的数据库性能诊断。ADDM（以及顾问程序）中的很多数据都会根据相应的数据类型显示为图形--如按时间绘制的线图、条形图、饼状图，因此这些信息一目了然。

除了查看主动ADDM分析的结果之外，还可以使用OEM的PL/SQL接口通过OEM或命令行手动运行ADDM。ADDM对潜在的瓶颈进行自顶向下的分析，得出包括根本原因和带有原理阐述建议的一组分析结果。除了识别问题以外，ADDM还对每个问题对系统总体性能有多大影响及解决该问题后会得到多少好处进行报告。这种影响－好处分析有助于数据库管理员将精力集中在那些解决后能获得最大性能收益的问题上。

主动调优对话期的调优步骤

无论是调优单个语句还是调优具有不同来源（ADDM、AWR Top SQL或二者组合）的一系列语句，所有的调优活动都是以创建调优任务为起点的。对于打包应用程序，占用太多资源的SQL代码很可能会显现在由特定SQL_ID标识的OEM Top SQL页中（在Oracle数据库10g中，每个SQL语句现在都由一个SQL_ID来标识）。以下是从命令行使用DBMS_SQLTUNE包来调优SQL语句的方法。

步骤1 创建调优任务以标识SQL语句。在本例中，SQL文本是指使用绑定变量的语句。

create_tuning_task(
sql_text => 'select * from emp where
emp_id = :bnd',
bind_list =>
sql_binds(anydata.ConvertNumber(100)),
user_name => 'scott',
scope => 'comprehensive',
time_limit => 60,
task_name => 'my_sql_tuning_task',
description => 'task to tune a query on
a specified employee');

因为本例任务中的时限被定为60，所以我们允许优化器用整整一分钟时间来进行分析。还要注意作用域参数的"综合"设置，它意味着由优化器进行、用以提高性能的任何附加分析都可以在SQL附加信息集合中获得。

要调优打包应用程序中的SQL，需要找出其SQL_ID［如果它引出了问题（例如），就可以在OEM的Top SQL页找到它，或通过查询新框架（SQL_ADVISOR_%）的表］，并按下面代码所示创建调优任务：

create_tuning_task(sql_id =>			'q1rsx05369psft');

根据优化器实际所用时间（直至最高时限）的不同，create_ tuning_task函数最终会返回一个可标识该任务的独特的字符ID；此ID可与其他API一起使用［例如interrupt（中断）、cancel（取消）、drop（删除）］。

步骤2 现在已经有了此任务，并具备了我们的参数设置，但若不执行并启动进程，它什么都不会做。 要执行此调优任务：

execute_tuning_task(task_name => 			'my_sql_tuning_task');

任务完成后会返回提示。该任务的执行结果会在后台被发送到该新框架（基础架构）所依赖的表中。 你可以使用DBA_ADVISOR_%视图（DBA_ADVISOR_FINDINGS、DBA_ADVISOR_ RECOMMENDATIONS等）之类的许多新视图来查询这些表，其中存储着执行结果和建议。

步骤3 通过调用report_tuning_task过程来查看结果：

set long 10000;
select report_tuning_task(task_name => 'my_sql_tuning_task') from dual;

report_tuning_task过程生成任务结果的完整报告，其中包括执行结果和建议，以及输出到控制台的内容。（OEM也能达到同样的详细程度。）

步骤4 适当地采纳建议。假定已在综合模式下运行了该调优任务，并生成了一个SQL附加信息集合，则可以通过执行accept_sql_profile命令来使用SQL附加信息集合：

accept_sql_profile(
task name => 'my_sql_tuning_task',
name => 'my_sql_profile');

上例中的name是可选项；如果未给出name，系统会为该附加信息集合生成一个惟一名称。接受SQL附加信息集合会将其永久地存储到数据字典中，在运行时会用到它：应用程序下次运行时，优化器（返回"正常"模式）将在后台使用该附加信息集合来加速应用该优化器的SQL语句性能，而与其来源（打包应用程序或其他）无关。什么会更容易呢？

结论

至少可以这样说，Oracle数据库10g中可管理性的新改进非常丰富，它为当今忙得不可开交的数据库管理员提供了一个得力的"自动驾驶仪"。本专栏介绍的只是SQL调优顾问程序新功能的皮毛。SQL调优功能可以轻松地提高任何SQL代码的性能，包括打包应用程序中的SQL代码，而无需修改源代码，数据库管理员也不必指出要向优化器发送哪些提示来提高性能。这一新基础架构所提供的功能和众多新的顾问程序都不会取代数据库管理员，但它们将使我们能够专注于工作中更有挑战性和更有趣的部分。