Oracle数据库性能调优的研究

第６期（总第１６３期）　２０１０年１２月　机械工程与自动化　ＭＥＣＨＡＮＩＣＡＬ　ＥＮＧＩＮＥＥＲＩＮＧ　＆　ＡＵＴ０ＭＡＴＩＯＮ　Ｎｏ．６　Ｄｅｃ．　文章编号：１６７２—６４１３（２０１０）０６—０００１—０３　Ｏｒａｃｌｅ数据库性能调优的研究　田　辉，赵黎，韩晓宏　４５０００２）　（河南农业大学机电工程学院。河南　郑州摘要：数据库的性能调优将直接影响整个数据库应用系统的高效运行。讨论了Ｏｒａｃｌｅ数据库性能优化的策略、　目标以及优化的方法，详细介绍了Ｏｒａｃｌｅ数据库诊断和调优工具的配置和使用方法，并针对数据库内存的调　整和优化、Ｉ／０的调整、ＳＱＬ语句的优化与调整以及数据库动态参数的调整进行了介绍和说明。合理、正确地　利用Ｏｒａｃｌｅ数据库提供的工具和动态参数，可以有效地提高数据库的整体性能。　关键词：Ｏｒａｃｌｅ诊断工具；优化器；性能　中图分类号：ＴＰ３１１．１３１　文献标识码：Ａ　０　引言　由于识别数据库运行状况的复杂性，人们通常都　是根据经验来对其进行优化，这样，就容易带来想当　然和经验主义的问题，通常效果不会非常好。　统使用的相同优化方法不过是浪费时问。当系统需要　全面检查时，使用开发系统方法才更加有利。　２诊断与调优工具　２．１　告警日志　Ｏｒａｃｌｅ里监控数据库运行状况和识别问题的工具　很多，整体监控的是Ｓｔａｔｓｐａｃｋ，单个语句分析的是　ＡｕｔｏＴｒａｃｅ、ＳＱＬ　Ｔｒａｃｅ、Ｒｕｎｓｔａｔｓ等。用工具取代经　验，用数据取代猜测，是数据库优化的正确方法。　Ｏｒａｃｌｅ数据库优化的主要目标是，确保用户能够　尽快获得对其语句的响应。由于“尽快”并不是一个　准确的术语，因此必须通过某种方式来量化时间衡量。　通常以响应时间、吞吐量、负载或恢复时间来衡量优　化目标ｎ　］。本文重点介绍利用Ｏｒａｃｌｅ数据库提供的　工具对数据库性能调优的方法。　１　生产过程中的优化步骤　生产系统的优化方法是在用户遇到问题之前解决　问题，优化步骤如下：　告警日志中包括各种提示性日志信息和各种警　告、错误信息。它的名字是ａｌｅｒｔ～数据库ＳＩＤ．１ｏｇ，它　的位置用初始化参数ｂａｃｋｇｒｏｕｎｄ—ｄｕｍｐ—ｄｅｓｔ设置，　显　示　位　置　的　语　句　为：　ＳＱＩ　＞　ｓｈｏｗｐａｒａｍｅｔｅｒｂａｃｋｇｒｏｕｎｄ　ｄｕｍｐ—ｄｅｓｔ。下列重要的　信息被记录在告警日志中：　（１）内部错误，也就是０ＲＡ一６００错误。这些错　误通常是由ＢＵＧ或一些Ｏｒａｃｌｅ的内部原因引起的，　所以被称为内部错误。　（２）坏块错误ＯＲＡ一１５７８或ＯＲＡ～１４９８，也就　是数据文件产生了坏块。　（３）影响数据库结构的操作或参数，如　（１）使用Ｓｔａｔｓｐａｃｋ、Ｏｒａｃｌｅ　Ｅｎｔｅｒｐｒｉｓｅ　Ｍａｎａｇｅｒ　等工具来定位瓶颈或潜在的瓶颈。　（２）瓶颈通常以等待事件的形式出现，请确定等　待事件的原因。　（３）解决等待事件的起因，这可能需要更改系统　全局区的成员大小。　（４）重新运行应用程序，然后使用第（１）步中所使　用的工具，检查所做的更改是否对系统产生了积极的　效果。　（５）如果未达到目标，则重复这个过程。　按照这个结构进行优化的原因是，重复在开发系　｛Ｉ｝河南省自然科学基金资助项目（２００９Ｂ２１００１６）　收稿日期：２０１０－０６—０１；修回日期：２０１０－０７—２０　作者简介：田辉（１　９８０一），女．河南西乎人．讲师．硕士。　Ｃｒｅａｔｅｄａｔａｂａｓｅ创建数据库、Ｓｔａｒｔｕｐ或Ｓｈｕｔｄｏｗｎ开　始或关闭数据库、归档操作或恢复操作等。　（４）在实例启动时的非缺省参数。　（５）其他Ｏｒａｃｌｅ认为需要让ＤＢＡ（系统管理员）知　道的警告和错误信息。　（６）检查点信息。当ｌｏｇ～ｃｈｅｃｋｐｏｉｎｔｓ—ｔｏ—ａｌｅｒｔ参　数设为真时，检查点信息会被记入告警Ｅｌ志。如果想　详细观察检查点的行为，将此参数设为真是很有帮助　的。　总的来说，ＤＢＡ应该经常查看告警日志，或者利　用程序定时搜索告警日志；其他人员在发现警告、错　误时则应及时通报ＤＢＡ。　

・２・　机械工程与自动化　２０１０年第６期　２．２　ＳＱＩ　跟踪　至关重要。　２．２．１　ＳＱＩ—Ｔｒａｃｅ的作用　使用ＳＱＩ～Ｔｒａｃｅ设置ＳＱＩ　跟踪非常简单，ＳＱＩ～　Ｔｒａｃｅ是一个参数，只需要在会话层将它设置为Ｔｒｕｅ，　就开启了ｓＱＩ　跟踪，Ｏｒａｃｌｅ将会把ＳＱＩ　语句的执行　过程记录到跟踪文件中。通过查阅跟踪文件，将有利　于了解ＳＱＩ　语句的执行过程，这将帮助调优、排故　ＳＱＩ　语句。跟踪文件的位置在ｕｓｅｒ—ｄｕｍｐ—ｄｅｓｔ初始　化参数中。　Ｏｒａｃｌｅ为了帮助阅读ＳＱＩ　的跟踪文件，专门提供　３　ＳＱＬ调优　３．１　访问路径　访问路径就是Ｏｒａｃｌｅ访问表数据的方法。这些方　法有：①全表扫描：全表扫描可谓是声名狼籍，很多　人认为它速度慢，是性能的瓶径，但有时使用它反而　可以提高速度；②各种类型的索引访问：是使用最多　的访问方式，通过聚簇或ＲＯＷＩＤ方式直接访问。　３．２　全表扫描　全表扫描又可简称为全扫或全扫描，这个术语隐　了一个Ｔｋｐｒｏｆ工具。Ｔｋｐｏｒｆ工具的使用非常简单，只　需输入Ｔｋｐｒｏｆ跟踪文件的目标文件即可。Ｔｋｐｒｏｆ将　会把跟踪文件处理为更容易阅读的格式，并把处理结　果保存到指定的目标文件中。通过观察跟踪文件的目　标文件，可以观察到比较精确的ＣＰＵ时问、物理读、　逻辑读等信息，这有助于了解一条ｓＱＩ　语句是否有　效。　２．２．２　１００４６事件　用１００４６事件也能完成ｓＱＩ　跟踪，只需要将　“　ａｌｔｅｒｓｅｓｓｉｏｎｓｅｔｓｑ　ｌ＿ｔｒａｃｅ　—ｔｒｕｅ　＂　换　成　“　ａｌｔｅｒｓｅｓｓｉｏｎｓｅｔｅｖｅｎｔｓ　”　１００４６ｔｒａｃｅｎａｍｅｃｏｎｔｅｘｔｆｏｒｅｖｅｒ，ｌｅｖｅｌ１””，就可以完成　这项工作，这条语句也是开启ｓＱＩ　跟踪。结束跟踪的　语　句　是：　ａ１ｔｅｒｓｅｓｓｉｏｎｓｅｔｅｖｅｎｔｓ　１００４６ｔｒａｃｅｎａｍｅｃｏｎｔｅｘｔｏｆｆ”。　２．３　资料视图　Ｏｒａｃｌｅ会在运行时记录很多运行数据，这些数据　我们可以称其为“资料”。虽然记录资料这个操作也需　要消耗额外的ＣＰＵ、ｉ／ｏ等重要资源，而且这必然会　对数据库的性能有些影响，但是Ｏｒａｃｌｅ记录的资料越　多，将越有利于我们了解Ｏｒａｃｌｅ的运行状态，也越有　利于调优、排故，但同时额外的性能负担也越大。因　此，我们可以通过初始化参数ｓｔａｔｉｓｔｉｃｓ—ｌｅｖｅｌ控制　Ｏｒａｃｌｅ记录、收集的资料数量。这个参数有３个值：　ＡＩ　Ｉ—ＴＹＰＩＣＡＩ　、ＢＡＳＩＣ。　ＡＩ　Ｉ　是收集所有资料；ＢＡＳＩＣ是只收集最基本　的资料；ＴＹＰＩＣＡＩ　是默认值，它将收集大多数重要的　资料，并且它对性能的负面影响比较小。在绝大多数　情况下保留此参数的值为ＴＹＰＩＣＡｌ　就行，因为在这　个级别下所收集的资料是足够丰富的。　２．４　等待事件　等待事件和资料有很大的类似之处，它们都是　Ｏｒａｃｌｅ记录的运行时的状态信息，它们都可以算做　Ｏｒａｃｌｅ的运行资料，只不过等待事件可以说是一种特　殊的、作用巨大的资料。它还可以简称为事件。在　Ｏｒａｃｌｅ１０ｇＲ２中，提供了８７２个等待事件。　２．５　Ｓ１ａｔｓｐａｃｋ　Ｓｔａｔｓｐａｃｋ从Ｏｒａｃｌｅ８．１．６开始被引入Ｏｒａｃｌｅ，并　马上成为ＤＢＡ和Ｏｒａｃｌｅ专家用来诊断数据库性能的　强有力的工具口］。通过Ｓｔａｔｓｐａｃｋ，我们可以很容易地　确定Ｏｒａｃｌｅ数据库的瓶颈所在，记录数据库性能状　态．也可以使远程技术支持人员迅速了解你的数据库　运行状况。因此了解和使用Ｓｔａｔｓｐａｃｋ对于ＤＢＡ来说　含很多意思。Ｏｒａｃｌｅ将读取指定段中用于某一点或另　一点上的每个块。在这里，Ｏｒａｃｌｅ将从段的开始读到　结尾，处理每一个块。全扫描是读取Ｏｒａｃｌｅ的大量数　据的行之有效的方法，由于Ｏｒａｃｌｅ知道它打算读取读　段中的每一块，因此它将一次性读取多个块，而非一　次一个块。多块读的数量由初始化参数ＤＢ　ＦＩＩ　Ｅ—　ＭＵＬＴＩＢＩ　ＯＣＫ～ＲＥＡＤ—ＣＯＵＮＴ确定。访问路径就　是Ｏｒａｃｌｅ访问表数据的方法，这些访问路径有全表扫　描、ＲＯＷＩＤ访问、索引扫描。　３．３　Ｒ０ＷＩＤ访问　每一个表都有一个ＲＯＷＩＤ伪列，此伪列中记录　了每一行的物理地址，它包含文件号、块号和行在该　块中的行号。ＲＯＷＩＤ对于获取一个特定的行来说可　能是最快的方法，但是使用ＲＯＷＩＤ获取成千上万的　行并不是最好的方法。因为每使用ＲＯＷＩＤ访问一行，　都是一次逻辑读。在不使用Ｒ０ｗＩＤ访问行时，Ｏｒａｃｌｅ　将在一次逻辑读中访问尽量多的行。　３．４　索引扫描　３．４．１索引唯一扫描　索引唯一扫描和使用ＲＯＷＩＤ访问行是差不多　的，只不过是把每一行的ＲＯＷＩＤ记录到另一处地方，　它被叫做索引，每次访问行时，先访问索引，从索引　中取出行的ＲＯＷＩＤ，根据ＲＯＷＩＤ再真正地访问行。　３．４．２索引范围扫描　如果索引不是唯一型索引，那么，通过此索引的　访问将是索引范围扫描。从根本上说，索引范围扫描　和索引唯一扫描是一样的。　３．４．３索引跳跃扫描　在复合索引中，比如索引包含两个列，Ａ列、Ｂ列，　如果以Ｂ列为条件进行查询，将使用索引跳跃扫描。　３．４．４索引全扫描　索引全扫描（１ｎｄｅｘＦｕＩｌＳｃａｎｓ）不读取索引结构上　的每个块，这与其名字及关于全扫描的知识相背。可　以这样说，它根据部分枝块找到第一个叶块，然后按　叶块双向链表的顺序处理所有的叶块。　３．４．５索引快速全扫描　索引快速全扫描是把索引当作表一样进行全扫描　操作。　３．５连接　３．５．１嵌套循环连接　

２０１０年第６期　田辉，等：Ｏｒａｃｌｅ数据库性能调优的研究　・３・　在嵌套循环连接中，Ｏｒａｃｌｅ从第一个行源中读取　第一行，然后和第二个行源中的数据进行对比，将所　有匹配的记录放在结果集中，然后Ｏｒａｃｌｅ将读取第一　个行源中的下一行。按这种方式直至第一个数据源中　的所在行都经过处理。第一个记录源通常称为外部表　或者驱动表，相应的第二个行源称为内部表。使用嵌　套循环连接是一种从连接结果中提取第一批记录的最　快速的方法。在驱动行源表（就是您正在查找的记录）　较小时，或者内部行源表已连接的列有惟一的索引或　高度可选的非惟一索引时，嵌套循环连接效果是比较　理想的。嵌套循环连接相比其他连接方法其优势是：它　可以快速地从结果集中提取第一批记录，而不用等待　整个结果集完全确定下来。这样，在理想情况下，终　端用户就可以通过查询屏幕查看第一批记录，而在同　时读取其他记录。不管如何定义连接的条件或者模式，　任何两行记录源都可以使用嵌套循环连接，所以嵌套　循环连接是非常灵活的。然而，如果内部行源表（读取　的第二张表）已连接的列上不包含索引或者索引不是　高度可选时，嵌套循环连接效率是很低的。如果驱动　行源表（从驱动表中提取的记录）非常庞大时，其他的　连接方法可能更加有效。　３．５．２排列合并连接　在排列合并连接中，Ｏｒａｃｌｅ分别将第一个源表、　第二个源表按它们各自要连接的列排序，然后将两个　已经排序的源表合并，如果找到匹配的数据，就放到　结果集中。在缺乏数据的选择性或者可用的索引时，或　者两个源表都过于庞大（超过记录数的５％）时，排序　合并连接将比嵌套循环连接更加高效。但是，排列合　并连接只能用于等价连接（ＷＨＥＲＥＤ．ｄｅｐｔｎｏ—　Ｅ．ｄｅｊｐｔｎｏ，而不是ＷＨＥＲＥＤ．ｄｅｐｔｎｏ＞＝Ｅ．ｄｅｐｔｎｏ）。　ＥＮＡＢＩ　ＥＤ设为Ｔｒｕｅ，并且为参数ＰＧＡ—　ＡＧＧＲＥＧＡＴＥ—ＴＡＲＧＥＴ设置了一个足够大的值的　时候，Ｏｒａｃｌｅ才会使用哈希连接。这与嵌套循环连接　有点类似，即Ｏｒａｃｌｅ先建立一张哈希表以利于操作进　行。当使用ＯＲＤＥＲＥＤ提示时，ＦＲＯＭ子句中的第一　张表将用于建立哈希表。　当缺少有用的索引时，哈希连接比嵌套循环连接　更加有效，因为处理内存中的哈希表比检索Ｂ一树索引　更加迅速。哈希连接可能比排序合并连接更快，因为　在这种情况下只有一张源表需要排序。和排序合并连　接、群集连接一样，哈希连接只能用于等价连接。和　排序合并连接一样，哈希连接使用内存资源，并且当　用于排序内存不足时，会增加临时表空间的ｉ／ｏ（这将　使这种连接方法速度变得极慢）。最后，只有基于代价　的优化器才可以使用哈希连接。　３．６优化器　如果数据库的优化器模式设置为选择性　（ＣＨＯＯＳＥ），那么实际的优化器模式将和是否运行过　ａｎａｌｙｚｅ命令有关。如果ｔａｂｌｅ已经被ａｎａｌｙｚｅ过，优化　器模式将自动成为ＣＢＯ；反之，数据库将采用ＲＵＩ　Ｅ　形式的优化器。　在缺省情况下，Ｏｒａｃｌｅ采用ＣＨＯＯＳＥ优化器＿４ｊ，　为了避免那些不必要的全表扫描（ｆｕｌｌｔａｂｌｅｓｃａｎ），必须　尽量避免使用ＣＨＯ０ＳＥ优化器，而直接采用基于规　则或者基于成本的优化器。　４结束语　由于Ｏｒａｃｌｅ系统的灵活性、复杂性、性能问题的　排列合并连接需要临时的内存块，以用于排序（如果　ＳＯＲＴ—ＡＲＥＡ—ＳＩＺＥ设置得太小的话），这将导致在　临时表空间占用更多的内存和磁盘Ｉ／Ｏ。　３．５．３　ＨＡＳＨ连接　原因多样性以及Ｏｒａｃｌｅ数据库的动态特性，优化成为　Ｏｒａｃｌｅ数据库管理中最困难的领域。本文主要从优化　的概念及手段方面讲述如何使用工具对数据库系统进　行优化。但并未针对服务器、网络、磁盘以及ＣＰＵ等　情况进行分析，这也将是下一步的研究方向。　参考文献：　［１］藤永昌．Ｏｒａｅｌｅ９Ｉ数据库管理员大全［Ｍ］．北京：清华大学　出版社，２００４．　当内存能够提供足够的空间时，哈希（ＨＡＳＨ）连　接是Ｏｒａｃｌｅ优化器通常的选择。在哈希连接中，Ｏｒａｅｌｅ　访问一张表（通常是较大的表），并在内存中建立一张　基于连接键的哈希表；然后它扫描连接中其他的表（通　常是较大的表），并根据哈希表检测是否有匹配的记　录。只有在数据库初始化参数ＨＡＳＨ—ＪＯＩＮ　［２］蒋蕊，王磊，王毳．Ｏｒａｃｌｅ９ＩＤＢＡ手册ＥＭ］．北京：机械工业　出版社，２００２．　［３］Ｄｏｎａｌｄ　Ｋ　Ｂ．Ｏｒａｃｌｅ　ＳＴＡＴＳＰＡＣＫ性能调整技术［Ｍ］．袁　勤勇，译．北京：机械工业出版社，２００２．　［４３王海亮．精通ＯｒａｃｌｅＪ０Ｇ系统管理［Ｍ］．北京：中国水利　水电出版社，２００５．　Ｒｅｓｅａｒｃｈ　ｏｎ　Ｏｒａｃｌｅ　Ｄａｔａｂａｓｅ　Ｐｅｒｆｏｒｍａｎｃｅ　Ｏｐｔｉｍｉｚａｔｉｏｎ　ＴＩＡＮ　Ｈｕｉ，ＺＨＡＯ　Ｌｉ，ＨＡＮ　Ｘｉａｏ－ｈｏｎｇ　（Ｃｏｌｌｅｇｅ　ｏｆ　Ｍｅｃｈａｎｉｃａｌ　ａｎｄ　Ｅｌｅｃｔｒｉｃａｌ　Ｅｎｇｉｎｅｅｒｉｎｇ，Ｈｅｎａｎ　Ａｇｒｉｃｕｌｔｕｒａｌ　Ｕｎｉｖｅｒｓｉｔｙ，Ｚｈｅｎｇｚｈｏｕ　４５０００２，Ｃｈｉｎａ）　Ａｂｓｔｒａｃｔ：Ｄａｔａｂａｓｅ　ｐｅｒｆｏｒｍａｎｃｅ　ｔｕｎｉｎｇ　ｗｉｌｌ　ｄｉｒｅｃｔｌｙ　ａｆｆｅｃｔ　ｔｈｅ　ｅｆｆｉｃｉｅｎｔ　ｏｐｅｒａｔｉｏｎ　ｏｆ　ｄａｔａｂａｓｅ　ａｐｐｌｉｃａｔｉｏｎ　ｓｙｓｔｅｍ．Ｔｈｉｓ　ａｒｔｉｃｌｅ　ｄｉｓｃｕｓｓｅｓ　ｔｈｅ　Ｏｒａｃｌｅ　ｄａｔａｂａｓｅ　ｐｅｒｆｏｒｍａｎｃｅ　ｏｐｔｉｍｉｚａｔｉｏｎ　ｓｔｒａｔｅｇｙ，ｏｂｊｅｃｔｉｖｅｓ　ａｎｄ　ｍｅｔｈｏｄｓ，ａｎｄ　ｉｎｔｒｏｄｕｃｅｓ　ｔｈｅ　ｃｏｎｆｉｇｕｒａｔｉｏｎ　ａｎｄ　ｕｓｅ　ｏｆ　Ｏｒａｃｌｅ　ｄａｔａｂａｓｅ　ｔｕｎｉｎｇ　ｔｏｏｌｓ　ｆｏｒ　ｄｉａｇｎｏｓｉｓ　ｉｎ　ｄｅｔａｉｌ．Ａｎｄ　ｔｈｅ　ａｄｊｕｓｔｍｅｎｔ　ａｎｄ　ｏｐｔｉｍｉｚａｔｉｏｎ　ｏｆ　ｄａｔａｂａｓｅ　ｍｅｍｏｒｙ，Ｉ／Ｏ，ＳＱＬ　ｓｔａｔｅｍｅｎｔｓ　ａｎｄ　ｄｙｎａｍｉｃ　ｐａｒａｍｅｔｅｒｓ　ｏｆ　ｔｈｅ　ｄａｔａｂａｓｅ　ｉｓ　ｐｒｅｓｅｎｔｅｄ　ａｎｄ　ｅｘｐｌａｉｎｅｄ．Ｒｅａｓｏｎａｂｌｅ　ｕｓｅ　ｏｆ　Ｏｒａｃｌｅ　ｄａｔａｂａｓｅ　ｔｏｏｔｓ　ａｎｄ　ｄｙｎａｍｉｃ　ｐａｒａｍｅｔｅｒｓ　ｃａｎ　ｅｆｆｅｃｔｉｖｅｌｙ　ｉｍｐｒｏｖｅ　ｔｈｅ　ｏｖｅｒａｌｌ　ｐｅｒｆｏｒｍａｎｃｅ　ｏｆ　ｄａｔａｂａｓｅ．　Ｋｅｙ　ｗｏｒｄｓ：Ｏｒａｃｌｅ　ｄｉａｇｎｏｓｔｉｃ　ｔｏｏｌｓ；ｏｐｔｉｍｉｚｅｒ；ｐｅｒｆｏｒｍａｎｃｅ