[Oracle]10g：用等待界面诊断性能问题
使用扩展 SQL 跟踪数据来了解是什么在耗费这么长的时间。假如有一天你开车去上班，但最后还是没能及时参加一个重要会议。你无法将你的易做图性的想法呈现给客户，所以他们也不会采用。你的拖拖拉拉使你感到沮丧，你发誓决不再犯同样的错误。那么，为了不再发生类似情况，你怎么判断问题的原因呢？按照下面这个列表进行检查怎么样？
检查汽车外表是否有缺陷，因为外表有缺陷会使汽车的最高速度降低 1% 或更多。
检查车轮定位，因为外倾角、后倾角或前束角不合适都会导致汽车的操纵不灵活并且耗费时间。
检查发动机，以确保达到额定马力的 99% 或更高。如果不是这样，则要考虑重装或更换发动机。
不，你可能不会采用这种检查方法；那样太可笑了。你可能会以完全不同的方式来判断问题之所在，可能只是问你自己一个简单的问题：什么事情让我花了这么长时间？
从这个角度出发，问题就迎刃而解了。如果开车需要 40 分钟，而你在会议开始前 20 分钟才动身，那么下次就要提前 30 分钟动身。如果因为交通拥堵浪费了 20 分钟，那么，下次要么再早一些动身，换条路线，要么更仔细地查看早 7 点的路况报告。如果是你迷了路，结果浪费了 20 分钟去兜圈子，那么下次你大概就要事先看看地图。如此等等。
我感到奇怪的是，那些擅长解决日常性能优化问题的数据库专业人员在工作中却使用完全不同的方法来解决数据库性能问题。许多数据库 " 调优人员 " 从来不问， " 是什么让这个程序运行了这么长时间？ " 相反，他们会参考检查内容清单，并试图阻止错误发生：
检查所有 Oracle 块请求是否都由数据库缓存提供服务
检查是否有全表扫描
检查所有排序是否都在内存中进行
检查重做日志是否与其他所有数据库文件进行了适当的隔离
等等。
对于某些工作来说，使用检查内容清单也许很好。但是对于判断性能问题这样的工作，试图确定理论上可能会出错的每一件事，从而对这个问题进行处理的做法的效率会很低。更有效的方法就是找到这个简单问题的答案：
是什么花了这么长时间？
用于优化 Oracle 程序的好的策略就如同日常生活中用到的策略。就像这样：
• 使用专门的仪器来测定程序的性能，从而监视运行速度慢的程序。
• 为运行慢的程序创建资源描述，把程序的响应时间细分为几种有用的类型。
• 通过首先处理响应时间最长的部分来缩短程序的响应时间。
当你了解了若干技术细节之后，这个方法就非常简单了。如果你真的这样做，那么每次你都能获得一个有用的方法，久而久之，你将能在进行性能改进之前预知其结果。
跟踪
如果你有用于收集程序中每个执行步骤的时间统计信息的高级工具，那就用吧。但只收集汇总数据（如通过对系统全局区 [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 参数设置正确：
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 命令，如下所示：
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 跟踪：
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 参数：
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 ：
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 数据库会话：
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 会话，就如同下面这样：
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.7
and 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 性能》）一书，以便大概了解原始跟踪文件中有些什么。一定要
