Oracle数据库跟踪

hotyxm说的trace文件应该类似于oracle数据库的3种类型的常见诊断文件吧，它们是报警文件、后台进程跟踪文件（background trace files）、用户进程跟踪文件。报警文件包括数据库的日常 *** 作细心，他存放在由BACKGROUND_DUMP_DEST参数所定义的目录下。

但是这些文件都是关于数据库自身 *** 作的，我还没有学到怎么查询软件对数据库的访问信息，不知oracle数据库又没得这样的功能， ruhaisanren给的那个网址上面的东西好像很专业，你试试，成功了要告诉我们哦。

如果您定期运行跟踪并且保留所有结果以便进行历史趋势分析 那么通过跟踪捕获的数据的价值将大大增加 但是 存储空间很快会成为约束 我们的主要生产服务器每小时执行 百万个事务 而持续时间为 分钟的跟踪会创建 GB 大小的跟踪文件 我们的系统致力于整理所有数据并且只保存其精华 您可以安排任何服务器的特定跟踪（或者设置循环跟踪） 并且自动加载和处理跟踪文件 正如您上个月所看到的那样 我们的系统从 T SQL 中剔除了不重要的详细信息 从而将事务类型减少到可管理的数量 并且生成和保存了开销最大的事务的报表 在经过几个周的积累之后 这样的报表可以提供对整个服务器或任何特定事务类型进行性能趋势分析的数据 安装 您可以将我们的系统安装在任何已经将服务器连接链接到您希望运行跟踪的所有 SQL Server 的网络 SQL Server 上 因此 为了保存跟踪文件 必须可以从被跟踪的服务器通过网络对该中心服务器的硬盘驱动器进行访问 中心跟踪服务器充当所有跟踪的计划程序 数据处理器 循环报表的发布者 历史数据的储存库以及 DBA 可以生成即席报表和进行性能调查的分析服务器 该设计将对被跟踪的服务器的影响降低至最低程度 并且最大限度降低了由于造成磁盘空间不足或引起处理开销而破坏这些服务器的工作的可能性 您还可以直接在每个被跟踪的服务器上安装和使用该系统 — 只要该服务器具有足够的磁盘空间和处理能力 出于本文的目的 让我们将我们的中心跟踪服务器称为 TRACESQL 并且将我们的被跟踪的服务器称为 PRODSQL 如果您计划使用同一服务器来跟踪其本身 则请用同一名称来替换 TRACESQL 和 PRODSQL 下面介绍如何安装 OpenSQLTrace 如果您打算只跟踪已经安装 OpenSQLTrace 的同一服务器（换句话说 TRACESQL 和 PRODSQL 是同一服务器） 则请跳过步骤 和 配置从 TRACESQL 到 PRODSQL 的链接服务器连接 它必须允许具有启动用来管理服务器端跟踪的系统存储过程的权限 最容易的方法是使用在 PRODSQL 上具有 System Administrator 角色的帐户 但是您显然需要考虑您的特定环境中的安全要求 查明哪个帐户被用来在 PRODSQL 上运行 MSSQLServer 服务 它必须是网络帐户 在 TRACESQL 上选择一个硬盘驱动器分区以用来存储跟踪文件 它必须具有足够的空间 以容纳来自 PRODSQL 的跟踪文件 — 大小很可能为几个 GB 但是 正如别人所说的那样 每个人需要的空间可能有所不同 跟踪文件大小取决于服务器活动 事务混合和跟踪的持续时间 如果 TRACESQL 和 PRODSQL 是不同的服务器 则请在 TRACESQL 上 在您所选的驱动器上创建一个名为 TRACE 的共享文件夹 并且将该共享上的所有权限授予在 PRODSQL 上运行 MSSQLServer 服务的网络帐户 在 TRACESQL 上创建一个名为 Trace 的数据库 分配足够空间以存储多个完整的跟踪文件 另外 还要分配一点儿额外的空间 以存放所保存的报表 将存储在数据库中的跟踪文件的过期时间是可配置的 在我们的环境中 我们只将它们保留一个周 但是我们无限期地保留已编译的摘要报表 以便进行历史趋势分析 下载本文随附的 OpenSQLTrace sql 脚本 并且从 TRACESQL 服务器上的查询分析器中执行它 这会在 Trace 数据库中创建所有需要的对象 并且创建一个每天安排一次的作业以清除过期的跟踪表 （如果您先前已经在同一数据库中安装了该系统 则请注意 该脚本删除并重新创建了所有对象 包括已保存的数据 但未过期的跟踪表除外 ） 如果 TRACESQL 和 PRODSQL 是同一服务器 则改变在上一步中创建的用户定义函数 ufn_Trace_File_Name 更改以下行 return( \\ + rtrim( @@servername ) + \TRACE\ +以使用您在步骤 中创建的 TRACE 文件夹的硬编码路径 确切的路径取决于您的环境 例如 如果您在驱动器 D: 上创建了 TRACE 文件夹 则请按如下方式更改代码 return( D:\TRACE\ + 用法示例 上个月的文章提供了有关提炼跟踪文件和生成摘要报表的存储过程的用法示例 请注意 可下载的新脚本具有 Calculate_Most_Expensive_Transactions 过程的重命名版本 新的名称为 Calculate_Hit_Parade 本月的脚本公开了由以下示例说明的新功能 设置带有摘要处理的一次性无人参与跟踪 为了测试该系统 让我们设置一次性跟踪 从 TRACESQL 上的查询分析器中执行以下过程 Schedule_Trace PRODSQL default 这会在 TRACESQL 上安排一个在两分钟内运行的作业 在 PRODSQL 上启动一个运行一分钟的跟踪 并且将文件保存到 TRACESQL 上的 TRACE 共享中 它还将在 TRACESQL 上安排另一个作业以便在跟踪的估计结束时间之后运行 分钟 将文件加载到 Trace 数据库中的表中 提炼已记录的 T SQL 语句（有关详细信息 请参阅上个月的文章） 生成开销最大的事务的摘要 并且将其保存到 Trace 数据库中的表中 （提示 您可以使用 fn_trace_getinfo() 来监视跟踪进度 ）这两个作业在成功完成后都将自动删除它们自身 如果您迫不及待地希望更快地运行该测试 则可以手动启动安排的第一个作业 等待一分钟（跟踪持续时间） 然后手动启动第二个作业 在第二个作业完成后 您便能够在 Trace 数据库的 Hit_Parade_Archive 表中找到已保存的开销最大的事务的报表 并且使用存储过程 Retrieve_Report 来检索它 默认情况下 系统会记录 T SQL 批处理和远程过程调用的完成 如果您希望记录其他跟踪事件 或者更进一步并分别记录在存储过程内部执行的每个查询 则需要通过 @Event_Class_Filter 参数向 Schedule_Trace 提供事件列表 安排每日跟踪 如果您需要每天运行跟踪 则可以如前所述安排一个跟踪（只须指定预期跟踪启动时间而不是默认时间 并且指定预期持续时间而不是一分钟） 然后 手动更改所安排的两个作业（运行跟踪和处理跟踪）的属性 以设置每天执行而不是一次性执行的计划 同时 在 EM 的已安排作业对话框中取消选中 Notifications 选项卡上的 Automatically delete job 选项 以防止作业在完成后删除它们自身（通过 Schedule_Trace 设置的默认行为） 检索和分析摘要报表 要检索任何跟踪的摘要报表 需要知道用来加载数据的跟踪表名称 跟踪表在过期（该参数可配置）时被自动从 TRACE 数据库中删除 但是从它们中提取的报表总是 与原来的表名称相关联 （Trace_Directory 表包含所有已处理的跟踪表的目录 ）可以按照服务器名称和跟踪时间查找跟踪表名称 执行以下存储过程以检索一个摘要报表 Retrieve_Report <Trace_Table_Name> 您可以在上个月的文章中查看示例摘要报表 我们通常将这些报表复制并粘贴到 Excel 中（在本月的下载中包含其中一个报表） 在那里可以容易地对数据进行排序和分析 在我们的环境中 我们还创建了一个 DTS 软件包 以便将开销最大的事务的日常报表以电子表格格式自动发布到网络共享 开发人员可以访问该报表 以查看他们的存储过程是如何执行的并且识别瓶颈 [我为作者为开发人员反馈所做的准备以及负责任的态度而喝采 — 编者 ]按聚合类型获得事务的实际源代码 在您识别开销最大的事务类型之后 您就可能希望查看在一个类型下聚合的所有事务的未经提炼的实际 T SQL 代码 为了完成该 下钻 工作 请执行以下存储过程 Report_TSQL_by_ID <Trace_Table_Name> <SQL_Type_ID>其中 <SQL_Type_ID>是从指定为<Trace_Table_Name> 的跟踪表派生的摘要报表中的事务类型的数字 ID 比较两个报表 最有效的分析方法之一是并排比较两个不同的摘要报表 您可能希望比较同一服务器的两个不同跟踪的性能 或者比较具有相同事务混合的两个不同服务器的性能 存储过程 Compare_Reports 采用两个跟踪表（来自 Trace_Directory 表）的名称作为参数 并且比较它们的已保存的报表 对于每个事务类型 它都会显示来自第一个跟踪和第二个跟踪的统计信息以及绝对和相对差异 只有当您在两个报表中跟踪相同的事件类型时 对这两个报表进行比较才会有意义 在我们的环境中 我们在同一时间 同一服务器上使跟踪运行相同的分钟数 从而使逐日比较显得合理 但是 我们可以想到很多分析任务会要求比较两个不同服务器中的跟踪 或者比较在每天的不同时间执行的跟踪 我们将跟踪比较报表复制并保存到 Excel 中 以便进行进一步的分析 它们可以帮助我们回答如下问题 事务混合中发生了哪些可能导致性能下降的更改？哪些事务的处理开销变得更大？同一服务器上的特定存储过程的执行频率或平均持续时间在两个日期之间是如何更改的？摘要报表中出现了哪些新的事务类型？在两个跟踪之间如何比较特定事务类型的 I/O 和 CPU 开销？从所有已保存的报表中检索特定事务类型的历史记录 有时 您可能希望查看特定事务的性能是如何随着时间的推移而变化的（例如 当您调查瓶颈事务 需要分析并且可能需要以图形方式表示响应速度随着时间的推移而发生的下降或提高时） 我们还使用它来验证应用于存储过程的修改是否的确已经改善了它们的性能 我们每天为我们的主要生产服务器运行跟踪并且保存所有报表 经过几个月的收集 该信息使我们可以为我们希望调查的任何 lishixinzhi/Article/program/SQLServer/201311/22044

Oracle-trace文件分析

如果一个系统的执行效率比较低，一个比较好的方法是通过跟踪用户的会话并且使用tkprof工具使用排序功能格式化输出，从而找出有问题的SQL语句。

例如首先从os上利用top命令找到当前占用cpu资源最高的一个进程的PID号9999；

然后在数据库中根据PID号找到相应的sid和serial#

select ssid,sserial# from v$session s,v$process p where spaddr=paddr and pspid='9999';

然后通过exec dbms_monitorsession_trace_enable(sid,serial#)开启trace；

最后利用tkprof察看trace输出。

开启Trace文件输出

可以通过以下方法开启Trace文件输出（需要ALTER SESSION系统权限）:

1) alter session/system set sql_trace=true

2) exec dbms_monitorsession_trace_enable/dbms_monitordatabase_trace_enable

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

Trace文件的位置

网络宽带，磁盘IO，查询速度都会影响到数据库的性能。

    具体问题具体分析，举例来说明为什么磁盘IO成瓶颈数据库的性能急速下降了。

   为什么当磁盘IO成瓶颈之后, 数据库的性能不是达到饱和的平衡状态，而是急剧下降。为什么数据库的性能有非常明显的分界点，原因是什么？

    相信大部分做数据库运维的朋友，都遇到这种情况。 数据库在前一天性能表现的相当稳定，数据库的响应时间也很正常，但就在今天，在业务人员反馈业务流量没有任何上升的情况下，数据库的变得不稳定了，有时候一个最简单的insert *** 作， 需要几十秒，但99%的insert却又可以在几毫秒完成，这又是为什么了？

dba此时心中有无限的疑惑，到底是什么原因呢 磁盘IO性能变差了？还是业务运维人员反馈的流量压根就不对？ 还是数据库内部出问题？昨天不是还好好的吗？

 当数据库出现响应时间不稳定的时候，我们在 *** 作系统上会看到磁盘的利用率会比较高，如果观察仔细一点，还可以看到，存在一些读的IO 数据库服务器如果存在大量的写IO,性能一般都是正常跟稳定的，但只要存在少量的读IO,则性能开始出现抖动，存在大量的读IO时（排除配备非常高速磁盘的机器），对于在线交易的数据库系统来说，大概性能就雪崩了。为什么 *** 作系统上看到的磁盘读IO跟写IO所带来的性能差距这么大呢？

如果亲之前没有注意到上述的现象，亲对上述的结论也是怀疑。但请看下面的分解。

在写这个文章之前，作者阅读了大量跟的IO相关的代码，如异步IO线程的相关的，innodb_buffer池相关的，以及跟读数据块最相关的核心函数buf_page_get_gen函数以及其调用的相关子函数。为了将文章写得通俗点，看起来不那么累，因此不再一行一行的将代码解析写出来。

咱们先来提问题。 buf_page_get_gen函数的作用是从Buffer bool里面读数据页，可能存在以下几种情况。

提问 数据页不在buffer bool 里面该怎么办？

  回答：去读文件，将文件中的数据页加载到buffer pool里面。下面是函数buffer_read_page的函数，作用是将物理数据页加载到buffer pool, 中显示

buffer_read_page函数栈的顶层是pread64(),调用了 *** 作系统的读函数。

buf_read_page的代码

 如果去读文件，则需要等待物理读IO的完成，如果此时IO没有及时响应，则存在堵塞。这是一个同步读的 *** 作，如果不完成该线程无法继续后续的步骤。因为需要的数据页不再buffer 中，无法直接使用该数据页，必须等待 *** 作系统完成IO

再接着上面的回答提问：

当第二会话线程执行sql的时候，也需要去访问相同的数据页，它是等待上面的线程将这个数据页读入到缓存中，还是自己再发起一个读磁盘的然后加载到buffer的请求呢？   代码告诉我们，是前者，等待第一个请求该数据页的线程读入buffer pool。

试想一下，如果第一个请求该数据页的线程因为磁盘IO瓶颈，迟迟没有将物理数据页读入buffer pool, 这个时间区间拖得越长，则造成等待该数据块的用户线程就越多。对高并发的系统来说，将造成大量的等待。 等待数据页读入的函数是buf_wait_for_read，下面是该函数相关的栈。

通过解析buf_wait_for_read函数的下层函数，我们知道其实通过首先自旋加锁pin的方式，超过设定的自旋次数之后，进入等待，等待IO完成被唤醒。这样节省不停自旋pin时消耗的cpu,但需要付出被唤起时的开销。

再继续扩展问题： 如果会话线程A 经过物理IO将数据页1001读入buffer之后，他需要修改这个页，而在会话线程A之后的其他的同样需要访问数据页1001的会话线程，即使在数据页1001被入读buffer pool之后，将仍然处于等待中。因为在数据页上读取或者更新的时候，同样需要上锁，这样才能保证数据页并发读取/更新的一致性。

由此可见，当一个高并发的系统，出现了热点数据页需要从磁盘上加载到buffer pool中时，造成的延迟，是难以想象的。因此排在等待热点页队列最后的会话线程最后才得到需要的页，响应时间也就越长，这就是造成了一个简单的sql需要执行几十秒的原因。

再回头来看上面的问题，mysql数据库出现性能下降时，可以看到 *** 作系统有读IO。 原因是，在数据库对数据页的更改，是在内存中的，然后通过检查点线程进行异步写盘，这个异步的写 *** 作是不堵塞执行sql的会话线程的。所以，即使看到 *** 作系统上有大量的写IO，数据库的性能也是很平稳的。但当用户线程需要查找的数据页不在buffer pool中时，则会从磁盘上读取，在一个热点数据页不是非常多的情况下，我们设置足够大的innodb_buffer_pool的size, 基本可以缓存所有的数据页，因此一般都不会出现缺页的情况，也就是在 *** 作系统上基本看不到读的IO。  当出现读的IO时，原因时在执行buf_read_page_low函数，从磁盘上读取数据页到buffer pool, 则数据库的性能则开始下降，当出现大量的读IO，数据库的性能会非常差。

以上就是关于Oracle数据库跟踪全部的内容，包括:Oracle数据库跟踪、OpenSQLTrace：自动跟踪处理和分析系统、如何分析trace文件等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！