诊断性能问题：使用扩展SQL 跟踪数据

使用扩展SQL跟踪数据来了解是什么在耗费这么长的时间。

假如有一天你开车去上班，但最后还是没能及时参加一个重要会议。你无法将你的革命性的想法呈现给客户，所以他们也不会采用。你的拖拖拉拉使你感到沮丧，你发誓决不再犯同样的错误。那么，为了不再发生类似情况，你怎么判断问题的原因呢？按照下面这个列表进行检查怎么样？

检查汽车外表是否有缺陷，因为外表有缺陷会使汽车的最高速度降低1%或更多。

检查车轮定位，因为外倾角、后倾角或前束角不合适都会导致汽车的操纵不灵活并且耗费时间。

检查发动机，以确保达到额定马力的99%或更高。如果不是这样，则要考虑重装或更换发动机。

不，你可能不会采用这种检查方法；那样太可笑了。你可能会以完全不同的方式来判断问题之所在，可能只是问你自己一个简单的问题：什么事情让我花了这么长时间？

从这个角度出发，问题就迎刃而解了。如果开车需要40分钟，而你在会议开始前20分钟才动身，那么下次就要提前30分钟动身。如果因为交通拥堵浪费了20分钟，那么，下次要么再早一些动身，换条路线，要么更仔细地查看早7点的路况报告。如果是你迷了路，结果浪费了20分钟去兜圈子，那么下次你大概就要事先看看地图。如此等等。

我感到奇怪的是，那些擅长解决日常性能优化问题的数据库专业人员在工作中却使用完全不同的方法来解决数据库性能问题。许多数据库"调优人员"从来不问，"是什么让这个程序运行了这么长时间？"相反，他们会参考检查内容清单，并试图阻止错误发生：

检查所有Oracle块请求是否都由数据库缓存提供服务

检查是否有全表扫描

检查所有排序是否都在内存中进行

检查重做日志是否与其他所有数据库文件进行了适当的隔离

等等。

对于某些工作来说，使用检查内容清单也许很好。但是对于判断性能问题这样的工作，试图确定理论上可能会出错的每一件事，从而对这个问题进行处理的做法的效率会很低。更有效的方法就是找到这个简单问题的答案：

是什么花了这么长时间？

用于优化Oracle程序的好的策略就如同日常生活中用到的策略。就像这样：

1. 使用专门的仪器来测定程序的性能，从而监视运行速度慢的程序。

2. 为运行慢的程序创建资源描述，把程序的响应时间细分为几种有用的类型。

3. 通过首先处理响应时间最长的部分来缩短程序的响应时间。

当你了解了若干技术细节之后，这个方法就非常简单了。如果你真的这样做，那么每次你都能获得一个有用的方法，久而久之，你将能在进行性能改进之前预知其结果。

跟踪

如果你有用于收集程序中每个执行步骤的时间统计信息的高级工具，那就用吧。但只收集汇总数据（如通过对系统全局区[SGA]或其基础共享存储段采样获得的数据）的工具对于某些类型的问题就不适合。

使用昂贵的监控工具时最常见的汇总错误是它们会跨整个Oracle数据库实例来汇总某一给定时间间隔内资源的使用情况。但是，运行速度慢的程序实际上可能不受资源争用问题的影响，而这个问题却完全控制着系统中一些不太重要的程序的性能。

即便是那些在Oracle数据库会话级上汇总信息的工具在诊断一些重要的问题类型时也存在着缺陷。例如，假设一个程序运行10分钟，调用了10000次Oracle SQL*Net message from client 这一"等待事件"，会话等待该事件的总用时为8.3分钟。这意味着会话对SQL*Net message from client事件的等待时间平均为3秒。但是单从汇总数据看，你无法知道这10000次调用是否每次都用3秒，还是这些调用中也许有一个用了5分钟，而其余9999次调用每次只用0.02秒。这两种情况需要进行完全不同的处理。

在这种情况下最能为你提供帮助的诊断数据是Oracle的扩展SQL跟踪数据。扩展SQL跟踪文件按时间顺序显示了Oracle数据库内核在指定时间内所完成工作的逐条记录。收集扩展SQL跟踪数据几乎是免费的。最大的花销是存储每一个需要引起注意的跟踪文件所需磁盘空间（很少超过几兆字节）的费用。

跟踪自己的代码。如果能访问程序的源代码，则打开其扩展SQL跟踪就非常容易。首先必须确保会话的TIMED_STATISTICS和MAX_DUMP_ FILE_SIZE参数设置正确：

CODE:

alter session set timed_statistics=true;

alter session set max_dump_file_size=unlimited;

如果没有设置TIMED_STATISTICS=TRUE，则数据库内核将把0值而不是真正的持续时间发送到跟踪文件中。如果对MAX_DUMP_ FILE_SIZE严加限制，则会在跟踪文件中生成下面这样的消息，而不是你想要的时间数据：

*** DUMP FILE SIZE IS LIMITED TO 1048576 BYTES ***

接下来是激活跟踪。有几种方法可以采用。过去的方法是使用ALTER SESSION命令，如下所示：

CODE:

alter session set events '10046 trace name context forever, level 12'

/* code to be traced goes here */

alter session set events '10046 trace name context off'

更好的方法是使用DBMS_SUPPORT包来激活扩展SQL跟踪：

CODE:

dbms_support.start_trace(waits=>true, binds=>true)

/* code to be traced goes here */

dbms_support.stop_trace()

请注意DBMS_SUPPORT 没有文档说明，可能也不是数据库默认安装的一部分。要了解DBMS_SUPPORT的信息，请参考MetaLink ( metalink.oracle.com)。

跟踪别人的代码。如果你想跟踪没有读/写权限的代码，则激活扩展SQL跟踪就有点麻烦了。但也不会难很多。你首先要获得你想跟踪的会话的V$SESSION.SID和V$SESSION.SERIAL#值。然后使用下面的过程调用，可以设置所选会话的TIMED_STATISTICS和MAX_DUMP_FILE_SIZE参数：

CODE:

dbms_system.set_bool_param_in_session(

sid => 42,

serial# => 1215,

parnam => 'timed_statistics',

bval => true)

dbms_system.set_int_param_in_session(

sid => 42,

serial# => 1215,

parnam => 'max_dump_file_size',

intval => 2147483647)

（对于Oracle8 8.1.6以前的版本，你可以用ALTER SYSTEM命令处理这些参数。）

接下来要激活跟踪。有几种方法可以采用，包括下面两个：

方法一是使用DBMS_SUPPORT：

CODE:

dbms_support.start_trace_in_session(

sid => 42,

serial# => 1215,

waits => true,

binds => true)

/* code to be traced executes during this time window */

dbms_support.stop_trace_in_session(

sid => 42,

serial => 1215)

若想激活扩展SQL跟踪，请不要使用名为SET_SQL_TRACE_IN_SESSION的DBMS_SUPPORT过程。该过程不允许在跟踪文件中指定等待和绑定的数据。

第二种方法更为精致，但在Oracle数据库10g之前的版本中并不支持这种方法。 DBMS_MONITOR包的引入解决了许多复杂诊断数据收集问题，这些问题是由连接共享和多线程操作所引起的。你可以在Oracle数据库10g中指定要跟踪的服务、模块或行动，而不指定要跟踪的Oracle数据库会话：

CODE:

dbms_monitor.serv_mod_act_trace_enable(

service_name => 'APPS1',

module_name => 'PAYROLL',

action_name => 'PYUGEN',

waits => true,

binds => true,

instance_name => null)

/* code to be traced executes during this time window */

dbms_monitor.serv_mod_act_trace_disable(

service_name => 'APPS1',

module_name => 'PAYROLL',

action_name => 'PYUGEN')

利用DBMS_MONITOR包，Oracle可为要跟踪的特定的业务操作提供完全支持激活或停止诊断数据收集的方法。

测试扩展SQL跟踪。试一试吧。查看第一个跟踪文件只需使用一个简单的SQL*Plus会话，就如同下面这样：

CODE:

alter session set timed_statistics=true;

alter session set max_dump_file_size=unlimited;

alter session set tracefile_identifier='Hello';

/* only in Oracle Database 8.1.7and later */

alter session set events '10046 trace name context forever, level 12';

select 'Howdy, it is '||sysdate from dual;

exit;

然后在由USER_DUMP_DEST实例参数的值命名的目录中寻找文件名中包含字符串"Hello"的最新写入的.trc文件。用你最喜欢的文本编辑器打开它。 阅读Oracle MetaLink注释39817.1或（Optimizing Oracle Performance，《优化Oracle性能》）一书，以便大概了解原始跟踪文件中有些什么。一定要运行跟踪文件上的tkprof，并研究其输出，但也不要由于有了tkprof就不再看原始的跟踪文件。跟踪文件中还有许多tkprof没有向你展示的内容。

如果你不仅需要一个由简单的SELECT from DUAL 生成的跟踪文件，还需要一个更感兴趣的跟踪文件，那么需要跟踪下面这条SQL语句：

CODE:

select object_type, owner, object_name from dba_objects;

由此得到的跟踪数据会让你感到很满意，因为Oracle数据库内核替你完成了惊人的工作量。

创建资源描述了正确而详细的诊断数据之后，你需要以摘要的形式对其进行查看，这有助于你以最快的速度做出响应。至少是从20世纪70年代开始，计算机程序员使用的摘要格式就是资源描述。资源描述只是一张表，它将所用时间分解为若干有用的子集，并按各子集所用时间降序排列。下面是一个资源描述的例子：

CODE:

Response Time Component Duration

-------------------------- ----------

Freeway at <50% speed limit 28.3m 59%

Finding a parking spot 7.2m 15%

Waiting at traffic lights 5.2m 11%

Freeway at ≥50% speed limit 4.0m 8%

Other 3.1m 6%

-------------------------- ----------

Total 47.8m 100%

这个资源描述说明买一辆速度更快的车不会使你能够更快地到达工作地点。

要从跟踪文件创建资源描述，有两种方法可以采用。

自己动手。《Optimizing Oracle Performance》一书中有所说明。

使用别人的工具。Oracle的tkprof和trcanalyzer（跟踪分析器）工具可为你完成一部分工作，但不是全部。

对数据做出响应

有了详细的诊断数据及其要点，就要决定对所看到的东西如何做出响应。对资源描述做出响应的经验做法非常可靠且相当简单：首先减少花费时间最长的部分，方法是减少调用它的次数。

这种方法几乎总是正确的。理解减少给定组件的调用次数的方法，需要对不同等待事件名称的含义有所了解。例如，当被跟踪的Oracle会话等待"buffer busy waits"这个等待事件时，该会话会向跟踪文件发送会生成足够多的信息，并显示正在等待哪一个缓冲区以及为什么要等待。当一个会话等待SQL*Net message from client事件时，跟踪文件中生成的数据的位置会告诉你执行过的数据库调用哪个是多余的。

在Oracle9i第2版中，有350多个不同的等待事件。在Oracle数据库10g中，几乎有700个等待事件。但不必担心：你根本不必知道它们都是什么意思。你只需知道你的重要程序花费大部分时间所等待的那些事件是什么意思。

看看你能做些什么

有了合适的诊断数据，你就能迅速解决相应的问题，或者证明这些问题不值得解决。

下面给出诊断数据能够解决的一部分问题清单：

整个系统的问题以及个别用户（业务）操作的具体问题

查询错误，包括写得不好的SQL语句、有问题的索引以及数据密度问题

A应用程序错误，包括解析过度、不使用数组运算等等在内的应用程序

串行化错误，包括不必要的频繁发生或费时的锁定、锁存或存储缓冲区活动

网络错误，如选择的协议不当、网络设备有问题

磁盘输入/输出错误，如高速缓存大小不适当、负载不平衡以及配置不当

容量不足，如交换、分页和CPU占用过多

使用Oracle的扩展SQL跟踪数据以及提出"什么如此费时？"这种问题的方法能带来的最好结果是在开始诊断和解决问题之前你将不必再猜测性能问题会是什么。

原文转自：http://www.ltesting.net