使用扩展SQL跟蹤数据来了解是什么在耗费这么长的时间
假如有一天你开车去上班但最后还是没能及时参加一个重要会议你无法将你的革命性的想法呈现给客户所以他们也不会采用你的拖拖拉拉使你感到沮丧你发誓决不再犯同样的错误那么为了不再发生类似情况你怎么判断问题的原因呢？按照下面这个列表进行检查怎么样？
检查汽车外表是否有缺陷因为外表有缺陷会使汽车的最高速度降低%或更多
检查车轮定位因为外倾角后倾角或前束角不合适都会导致汽车的操纵不灵活并且耗费时间
检查发动机以确保达到额定马力的%或更高如果不是这样则要考虑重装或更换发动机
不你可能不会采用这种检查方法那样太可笑了你可能会以完全不同的方式来判断问题之所在可能只是问你自己一个简单的问题什么事情让我花了这么长时间？
从这个角度出发问题就迎刃而解了如果开车需要分钟而你在会议开始前分钟才动身那么下次就要提前分钟动身如果因为交通拥堵浪费了分钟那么下次要么再早一些动身换条路线要么更仔细地查看早点的路况报告如果是你迷了路结果浪费了分钟去兜圈子那么下次你大概就要事先看看地图如此等等
我感到奇怪的是那些擅长解决日常性能优化问题的数据库专业人员在工作中却使用完全不同的方法来解决数据库性能问题许多数据库调优人员从来不问是什么让这个程序运行了这么长时间？相反他们会参考检查内容清单并试图阻止错误发生
检查所有Oracle块请求是否都由数据库缓存提供服务
检查是否有全表扫描
检查所有排序是否都在内存中进行
检查重做日志是否与其他所有数据库文件进行了适当的隔离等等
对于某些工作来说使用检查内容清单也许很好但是对于判断性能问题这样的工作试图确定理论上可能会出错的每一件事从而对这个问题进行处理的做法的效率会很低更有效的方法就是找到这个简单问题的答案
是什么花了这么长时间？
用于优化Oracle程序的好的策略就如同日常生活中用到的策略就像这样
使用专门的仪器来测定程序的性能从而监视运行速度慢的程序
为运行慢的程序创建资源描述把程序的响应时间细分为几种有用的类型
通过首先处理响应时间最长的部分来缩短程序的响应时间
当你了解了若干技术细节之后这个方法就非常简单了如果你真的这样做那么每次你都能获得一个有用的方法久而久之你将能在进行性能改进之前预知其结果
跟蹤
如果你有用于收集程序中每个执行步骤的时间统计信息的高级工具那就用吧但只收集汇总数据（如通过对系统全局区[SGA]或其基础共享存储段采样获得的数据）的工具对于某些类型的问题就不适合
使用昂贵的监控工具时最常见的汇总错误是它们会跨整个Oracle数据库实例来汇总某一给定时间间隔内资源的使用情况但是运行速度慢的程序实际上可能不受资源争用问题的影响而这个问题却完全控制着系统中一些不太重要的程序的性能
即便是那些在Oracle数据库会话级上汇总信息的工具在诊断一些重要的问题类型时也存在着缺陷例如假设一个程序运行分钟调用了次Oracle SQL*Net message from client 这一等待事件会话等待该事件的总用时为分钟这意味着会话对SQL*Net message from client事件的等待时间平均为秒但是单从汇总数据看你无法知道这次调用是否每次都用秒还是这些调用中也许有一个用了分钟而其余次调用每次只用秒这两种情况需要进行完全不同的处理
在这种情况下最能为你提供帮助的诊断数据是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则数据库内核将把值而不是真正的持续时间发送到跟蹤文件中如果对MAX_DUMP_ FILE_SIZE严加限制则会在跟蹤文件中生成下面这样的消息而不是你想要的时间数据
*** DUMP FILE SIZE IS LIMITED TO BYTES ***
接下来是激活跟蹤有几种方法可以采用过去的方法是使用ALTER SESSION命令如下所示
alter session set events
trace name context forever level
/* code to be traced goes here */
alter session set events
trace name context off
更好的方法是使用DBMS_SUPPORT包来激活扩展SQL跟蹤
dbms_supportstart_trace(waits=>true binds=>true)
/* code to be traced goes here */
dbms_supportstop_trace()
请注意DBMS_SUPPORT 没有文档说明可能也不是数据库默认安装的一部分要了解DBMS_SUPPORT的信息请参考MetaLink ( )
跟蹤别人的代码如果你想跟蹤没有读/写权限的代码则激活扩展SQL跟蹤就有点麻烦了但也不会难很多你首先要获得你想跟蹤的会话的V$SESSIONSID和V$SESSIONSERIAL#值然后使用下面的过程调用可以设置所选会话的TIMED_STATISTICS和MAX_DUMP_FILE_SIZE参数
dbms_systemset_bool_param_in_session(
sid =>
serial# =>
parnam=> timed_statistics
bval=> true)
dbms_systemset_int_param_in_session(
sid =>
serial# =>
parnam=> max_dump_file_size
intval=> )
（对于Oracle 以前的版本你可以用ALTER SYSTEM命令处理这些参数）
接下来要激活跟蹤有几种方法可以采用包括下面两个
方法一是使用DBMS_SUPPORT
dbms_supportstart_trace_in_session(
sid =>
serial# =>
waits => true
binds => true)
/* code to be traced executes during this time window */
dbms_supportstop_trace_in_session(
sid=>
serial => )
若想激活扩展SQL跟蹤请不要使用名为SET_SQL_TRACE_IN_SESSION的DBMS_SUPPORT过程该过程不允许在跟蹤文件中指定等待和绑定的数据
第二种方法更为精致但在Oracle数据库g之前的版本中并不支持这种方法 DBMS_MONITOR包的引入解决了许多复杂诊断数据收集问题这些问题是由连接共享和多线程操作所引起的你可以在Oracle数据库g中指定要跟蹤的服务模块或行动而不指定要跟蹤的Oracle数据库会话
dbms_monitorserv_mod_act_trace_enable(
service_name=> APPS
module_name => PAYROLL
action_name => PYUGEN
waits => true
binds => true
instance_name => null)
/* code to be traced executes during this time window */
dbms_monitorserv_mod_act_trace_disable(
service_name=> APPS
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
and later */
alter session
set events trace name context forever level ;
select Howdy it is ||sysdate from dual;
exit;
然后在由USER_DUMP_DEST实例参数的值命名的目录中寻找文件名中包含字符串Hello的最新写入的trc文件用你最喜欢的文本编辑器打开它 阅读Oracle MetaLink注释或（Optimizing Oracle Performance《优化Oracle性能》）一书以便大概了解原始跟蹤文件中有些什么一定要运行跟蹤文件上的tkprof并研究其输出但也不要由于有了tkprof就不再看原始的跟蹤文件跟蹤文件中还有许多tkprof没有向你展示的内容
如果你不仅需要一个由简单的SELECT from DUAL 生成的跟蹤文件还需要一个更感兴趣的跟蹤文件那么需要跟蹤下面这条SQL语句
select object_type owner object_name from dba_objects;
由此得到的跟蹤数据会让你感到很满意因为Oracle数据库内核替你完成了惊人的工作量
创建资源描述
有了正确而详细的诊断数据之后你需要以摘要的形式对其进行查看这有助于你以最快的速度做出响应至少是从世纪年代开始计算机程序员使用的摘要格式就是资源描述资源描述只是一张表它将所用时间分解为若干有用的子集并按各子集所用时间降序排列下面是一个资源描述的例子
Response Time Component Duration
Freeway at <% speed limit m%
Finding a parking spot m%
Waiting at traffic lightsm%
Freeway at ≥% speed limitm %
Otherm %
Total m %
这个资源描述说明买一辆速度更快的车不会使你能够更快地到达工作地点
要从跟蹤文件创建资源描述有两种方法可以采用
自己动手《Optimizing Oracle Performance》一书中有所说明
使用别人的工具Oracle的tkprof和trcanalyzer（跟蹤分析器）工具可为你完成一部分工作但不是全部
对数据做出响应
有了详细的诊断数据及其要点就要决定对所看到的东西如何做出响应对资源描述做出响应的经验做法非常可靠且相当简单首先减少花费时间最长的部分方法是减少调用它的次数 下一步
这种方法几乎总是正确的理解减少给定组件的调用次数的方法需要对不同等待事件名称的含义有所了解例如当被跟蹤的Oracle会话等待buffer busy waits这个等待事件时该会话会向跟蹤文件发送会生成足够多的信息并显示正在等待哪一个缓沖区以及为什么要等待当一个会话等待SQL*Net message from client事件时跟蹤文件中生成的数据的位置会告诉你执行过的数据库调用哪个是多余的
在Oraclei第版中有多个不同的等待事件在Oracle数据库g中几乎有个等待事件但不必担心你根本不必知道它们都是什么意思你只需知道你的重要程序花费大部分时间所等待的那些事件是什么意思
看看你能做些什么
有了合适的诊断数据你就能迅速解决相应的问题或者证明这些问题不值得解决
下面给出诊断数据能够解决的一部分问题清单
整个系统的问题以及个别用户（业务）操作的具体问题
查询错误包括写得不好的SQL语句有问题的索引以及数据密度问题
A应用程序错误包括解析过度不使用数组运算等等在内的应用程序
串行化错误包括不必要的频繁发生或费时的锁定锁存或存储缓沖区活动
网络错误如选择的协议不当网络设备有问题
磁盘输入/输出错误如高速缓存大小不适当负载不平衡以及配置不当
容量不足如交换分页和CPU占用过多
使用Oracle的扩展SQL跟蹤数据以及提出什么如此费时？这种问题的方法能带来的最好结果是在开始诊断和解决问题之前你将不必再猜测性能问题会是什么
