Oracle数据库参数优化

学术论坛　

浅谈Ｏｒａｃｌｅ数据库ＳＱＬ性能优化　

吴秀君　（乌鲁木齐职业大学新疆乌鲁木齐８３０００２）　

摘要：本文通过对ｓＱＬ语句优化技术的介绍，对数据查询语句的优化方式进行分析，得出应用程序的执行最终将归结为数据库中的ｓＱＬ语句执行　因此ｓＱＬ语句的执行效率的好坏直接影响系统资源的占用情况，所以为了提高ｏＲＡｃＬＥ数据库的性能，必须多角度优化ｓＱＬ语句。　关键词：Ｏｒａｃｌｅ数据库ＳＱＬ语句优化　中图分类号：ＴＰ３ｌ１．１３８　文献标识码：Ａ　文章编号：１００７—９４１６（２０１３）０９—０１９４—０２　

１引言　

随着软件技术的不断发展，系统性能越来越重要。信息系统都　离不开数据库应用，而Ｏｒａｃｌｅ￣据库的应用系统一般规模比较大，如　

何优化ＯＲＡＣＬＥ数据库的性能就显得尤为重要。为了保证Ｏｒａｃｌｅ￣　

据库运行在最佳的性能状态下，在信息系统开发之前就应该考虑数　

据库的优化策略。优化策略一般包括服务器操作系统参数调整、数　

据库参数调整、网络性能调整、应用程序ｓＱ　分析及设计等几个　

方面，本文就如何优化ｓＱＬ语句的方法来实现对ＯＲＡＣＬＥ数据库性　

能的优化。　

２数据库ＳＱＬ查询优化的原则　

数据库ｓＱＬ查询优化的一个基本原则就是：通过尽可能少的磁　

盘Ｉ／Ｏ访问获取所需要的数据。要评价查询优化性能，需要在数据库　优化前后比较其评价指标：响应时间和吞吐量之间的权衡、数据库　

的命中率以及内存的使用效率，并以此来衡量优化的效果和指导优　

化的方向。优质的查询语句可以使应用系统的性能得到大大提高。　

拙劣的应用ｓＱＬ语句、大小不合适的ＳＧＡ内存结构、效率差的ｓＱＩ语　句、执行计划过度的文件Ｉ／Ｏ及访问数据库资源的紊乱等都会影响　

数据库系统的性能和执行效率。对于影响０Ｒ　ＣＬＥ数据库系统性能　

的要素除了主机ＣＰＵ，ＲＡＭ，存储系统，ＯＳ参数配置，ＯＲＡＣＬＥ参　数配置之外，最关键的要素就是ｓＱＬ的编程质量，因ＩＩ￣ＳＱＬ语句尤其　

数据库・信息技术・Ｉｎｆｏｒｍａｔ　ｉｏｎ　Ｔｅｃｈｎｏｌｏｇｙ　

Ｏｒａｃｌｅ数据库的　性能优化方法研究　

罗瑞明　（深圳技师学院深圳５１８０００）　【摘要】由于硬件、操作系统、应用程序以及参数等方面的原因，数据库的性能在运行一段时间后都会下降。对数　据库系统性能进行优化，是保证其正常运行的重要手段。文章从ＳＱＬ语句、ＣＰＵ调整、磁盘Ｉ／０、内存分配和系统网　络几个方面出发，对Ｏｒａｃｌｅ数据库进行了深入分析，并提出性能优化策略，从而为保障Ｏｒａｃｌｅ数据库系统高效地　运行，提供具有实际应用价值的参考。　【关键词】Ｏｒａｃｌｅ数据库；性能；影响因素；优化方法　Ｏｒａｃｌｅ　Ｄａｔａｂａｓｅ　Ｐｅｒｆｏｒｍａｎｃｅ　Ｏｐｔｉｍｉｚａｔｉｏｎ　Ｍｅｔｈｏｄ　Ｒｅｓｅａｒｃｈ　Ｌｕｏ　Ｒｕｉ－ｍｉｎｇ　（ＳｈｅｎＺｈｅｎ　Ｔｅｃｈｎｉｃｉａｎ　Ｃｏｌｌｅｇｅ　ＳｈｅｎＺｈｅｎ　５１８ｏ０ｏ）　【Ａｂｓｔｒａｃｔ】Ｄｕｅ　ｔｏ　ｔｈｅ　ｈａｒｄｗａｒｅ，ｏｐｅｒａｔｉｎｇ　ｓｙｓｔｅｍ，ａｐｐｌｉｃａｔｉｏｎ　ｐｒｏｇｒａｍ　ａｎｄ　ｐａｒａｍｅｔｅｒｓ　ａｎｄ　ｏｔｈｅｒ　ｒｅａｓｏｎｓ，ｔｈｅ　ｐｅｒｆｏｒｍａｎｃｅ　ｏｆ　ｔｈｅ　ｄａｔａｂａｓｅ　ａｆｔｅｒ　ｒｕｎｎｉｎｇ　ｆｏｒ　ａ　ｐｅｒｉｏｄ　ｏｆ　ｔｉｍｅ　ｗｉｌｌ　ｄｅｃｒｅａｓｅ．Ｔｈｅ　ｄａｔａｂａｓｅ　ｓｙｓｔｅｍ　ｐｅｒｆｏｒｍａｎｃｅ　ｏｐｔｉｍｉｚａｔｉｏｎ　ｉｓ　ａ　ｉｍｐｏｒｔａｎｔ　ｍｅａｎｓ　ｔｏ　ｇｕａｒａｎｔｅｅ　ｔｈｅｉｒ　ｎｏｒｍａｌ　ｏｐｅｒａｔｉｏｎ．Ｔｈｉｓ　ａｒｔｉｃｌｅ　ｆｒ０ｍ　ｔｈｅ　ＳＱＬ　ｓｔａｔｅｍｅｎｔ，ＣＰＵ　ａｄｊｕｓｔｍｅｎｔ，ｄｉｓｋ　Ｉ／０，ｍｅｍｏｒｙ　ａｌｌｏｃａｔｉｏｎ　ａｎｄ　ｎｅｔｗｏｒｋ　ｓｙｓｔｅｍ竹１。ｍ　ｓｅｖｅｒａｌ　ａｓｐｅｃｔｓ，ｆｏｒ　ｔｈｅ　Ｏｒａｃｌｅ　ｄａｔａｂａｓｅ　ｔｏ　ｃａｒｒｙ　ｏｎ　ｔｈｅ　ｔｈｏｒｏｕｇｈ　ａｎａｌｙｓｉｓ，ａｎｄ　ｐｕｔ　ｆｏｒｗａｒｄ　ｔｈｅ　ｏｐｔｉｍｉｚａｔｉｏｎ　ｓｔｒａｔｅｇｉｅｓ　ｔｏ　ｅｎｓｕｒｅ　ｅｆｆｉｃｉｅｎｔ　ｏｐｅｒａｔｉｏｎ　ｏｆ　Ｏｒａｃｌｅ　ｄａｔａｂａｓｅ　ｓｙｓｔｅｍ　ａｎｄ　ｐｒｏｖｉｄｅ　ｕｓｅｆｕｌ　ｒｅｆｅｒｅｎｃｅ．　【Ｋｅｙｗｏｒｄｓ】Ｏｒａｃｌｅ　ｄａｔａｂａｓｅ；ｐｅｒｆｏｒｍａｎｃｅ；ｉｎｆｌｕｅｎｃｉｎｇ　ｆａｃｔｏｒｓ；ｏｐｔｉｍｉｚａｔｉｏｎ　ｍｅｔｈｏｄ　

２０１１年第２７期　ＳＣＩＥＮＣＥ＆ＴＥＣＨＮＯＬＯＧＹ　ＩＮＦＯＲＭＡＴＩＯＮ　Ｏ　ＩＴ论￣２０　科技信息　Ｏ　ＲＡ　ＣＬＥ数据库优化技术研究　曾　（莆田市公安局指挥中心　实　福建莆田３５１１００１　【摘￣］ＯＲＡＣＬＥ数据库是一个以关系型和面向对象为中心管理数据的数据库管理软件系统，其在管理信息系统、企业数据处理、因特网　及电子商务等领域有着非常广泛的应用。本文对ＯＲＡＣＬＥ数据库性能调整和优化进行了简要分析和研究，将ＳＱＬ语句、表和数据规范化的优　化作为主要研究内容。通过ＯＲＡＣＬＥ数据库的优化，能够有效地提高各终端程序的运行性能，提升数据库服务器应用性能。　【关键词】ＯＲＡＣＬＥ；数据库；优化　随着数据库应用的普及，数据库应用日趋大型化，用户数量的增　加．数据库应用系统的响应速度下降．性能问题越来越突出。尤其是数　据仓库及决策支持系统的应用，表和索引的数据量不断膨胀，这就给　数据的安全性和执行效率带来很大问题。为了提高数据的存取效率，　数据库系统的性能调整与优化对于整个系统的正常运行起着至关重　要的作用。基于此．本文主要研究ＳＱＬ语句、表和数据规范化的优化　内容．给出了一般情况下Ｏｒａｃｌｅ数据库应用系统的性能优化方法。　１　ＯＲＡＣＬＥ　ＳＱＬ语句优化　ＯＲＡＣＬＥ　ＳＱＬ语句的性能问题常常是由于在索引设计和查询没　计方面存在各种缺陷引起的。ＳＱＬ语句优化的实质就是在结果正确前　提下．充分利用索引．减少表扫描Ｉ／Ｏ次数，尽量避免表搜索的发生。　１．１索引Ｉｎｄｅｘ的优化设计　管理组织索引。索引可以大大加快数据库的查询速度，索引把表　中的逻辑值映射到安全的ＲｏｗＩＤ．因此索引能进行快速定位数据的物　理地址。但是有些ＤＢＡ发现，对一个大型表建立的索引，并不能改善　数据查询速度．反而会影响整个数据库的性能．这主要是和ＳＧＡ的数　据管理方式有关　ＯＲＡＣＬＥ在进行数据块高速缓存管理时，索引数据　比普通数据具有更高的驻留权限．在进行空间竞争时，ＯＲＡＣＬＥ会先　移出普通数据　对一个建有索引的大型表的查询时，索引数据可能会　用完所有的数据块缓存空间，ＯＲＡＣＬＥ不得不频繁地进行磁盘读写来　获取数据．因此在对一个大型表进行分区之后．可以根据相应的分区　建立分区索引　如果对这样大型表的数据查询比较频繁．或者干脆不　建索引。另外．ＤＢＡ创建索引时，应尽量保证该索引最可能地被用于　ｗｈｅｒｅ子旬中．如果对查询只简单地制定一个索引．并不一定会加快速　度，因为索引必须指定一个适合所需的访问路径。　聚簇的使用。所谓聚簇，简单地说就是把几个表放在一起，按一定　公共属性混合存放　聚簇根据共同码值将多个表的数据存储在同一个　ＯＲＡＣＬＥ块中．这时检索一组ＯＲＡＣＬＥ块就同时得到两个表的数据．　这样就可以减少需要存储的ＯＲＡＣＬＥ块．从而提高应用程序的性能　优化设置的索引．就必须充分利用才能加快数据库访问速度。　ＯＲＡＣＬＥ要使用一个索引，有一些最基本的条件：一是ｗｈｅｒｅ子名中　的这个字段，必须是复合索引的第一个字段；二是ｗｈｅｒｅ子名中的这　个字段，不应该参与任何形式的计算。　１．２　ＳＱＬ查询优化　数据库系统是管理信息系统的核心．从大多数系统的应用实例来　看，查询操作在各种数据库操作中占据的比重最大．查询速度的快慢　直接影响数据库的推广和应用．对于大型数据库来说．这一点显得尤　其重要。由于查询操作在ＳＱＬ语句中代价最大．因此优质的查询语句　可以大大提高应用系统的性能　查找有问题的ＳＱＬ语句　利用ＳＱＬ　Ｔｍｃｅ工具分析ＳＱＬ语句。　Ｏｒａｃｌｅ的ＳＱＬ　Ｔｒａｃｅ工具是确定ＳＱＬ语句是否被合理优化的最好方　法之一　如果发现当前会话行为异常或性能下降．则可以通过该工具　获得有关系统操作性能的信息，如解析、执行和返回数据的次数、ＣＰＵ　时间和执行时间、物理读和逻辑读操作次数、库缓冲区命中率等。　建立合适的ｓＯＬ语句。①避免相关子查询。一个字段的标签同时　在主查询和ｗｈｅｒｅ子句中的查询中出现。那么当主查询中的字段值改　变之后，子查询必须重新查询一次。对于子查询来说，查询嵌套层次越　多．效率越低．因此应当尽量避免它。如果子查询不可避免，那么要在子　查询中过滤掉尽可能多的行　在Ｏｒａｃｌｅ中相关子查询的执行效率特别　低引人临时表可以使其速度快１００倍左右。￣ＳＥＬＥＣＴ子句中避免使　８４　用　ＯＲＡＣＬＥ在解析的过程中，会将“书＂依次转换成所有的列名，这个　工作是通过查询数据字典完成的，意味着将耗费更多的执行时间。③　避免使用耗费资源的操作。带有ＤＩＳＴＩＮＣ．ｒ’ＵＮＩＯＮ，ＭＩＮＵＳ，　ＩＮＴＥＲＳＥＣＴ．ＯＲＤＥＲ　ＢＹ的ＳＱＬ语句会启动ＳＱＬ引擎执行耗费资源　的排序（ＳＯＲＴ）功能，应尽量避免使用。通常，带有ＵＮＩＯＮ，ＭＩＮＵＳ，　ＩＮＴＥＲＳＥＣＴ的ＳＱＬ语句都可以用其他方式重写。④选择联合查询的　连接次序。当查询涉及到５张以上的表时，Ｆｒｏｍ语句中表出现的顺序　可能会影响连接的执行效果　根据优化器的不同，ＳＱＬ语句中基础表　的选择也不一样。如果使用的是ＣＢＯ，优化器会检查ＳＱＬ语句中的每　个表的物理大小，索引的状态然后选用花费最低的执行路径；如果使　用ＲＢＯ，并且所有的连接条件都有索引对应，在这种情况下，基础表　就是ＦＲＯＭ子句中列在最后的那个表，因为ＯＲＡＣＬＥ的解析器是按　照从右到左的顺序处理ＦＲＯＭ子句中的表名的。　２　ＯＲＡＣＬＥ表的优化　利用表分区　分区将数据在物理上分隔开．不同分区的数据可以　制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查　询时．只需要在表分区中进行扫描．而不必进行ＦＴＳ（ＦｕＩＩ　ＴａＢｌｅ　Ｓｃａｎ，　全表扫描）．明显缩短了查询时间。另外处于不同磁盘的分区也将对这　个表的数据传输分散在不同的磁盘Ｉ／Ｏ．一个精心设置的分区可以将　数据传输对磁盘Ｉ／Ｏ竞争均匀地分散开。　避免出现行连接和行迁移。在建立表时，由于参数ｐｃｔｆｒｅｅ和　ｐｃｔｕｓｅｄ不正确的设置，数据块中的数据会出现行链接和行迁移，也就　是同一行的数据不保存在同一的数据块中　如果在进行数据查询时遇　到了这些数据，那么为了读出这些数据，磁头必须重新定位，这样势必　会大大降低数据库执行的速度。因此，在创建表时，就应该充分估计到　将来可能出现的数据变化。正确地设置这两个参数，尽量减少数据库　中出现行链接和行迁移　控制碎片。碎片是对一组非邻接的数据库对象的描述。碎片意味　着在执行数据库的功能时要耗费额外的资源（磁盘Ｉ／Ｏ．磁盘驱动的循　环延迟，动态扩展，链接的块等），并浪费大量磁盘空间。当两个或多个　数据对象在相同的表空间中．会发生区间交叉。在动态增长中．对象的　区间之间不再相互邻接　为了消除区间交叉将静态的或只有小增长的　表放置在一个表空间中．而把动态增长的对象分别放在各自的表空间　中。在ｃｒｅａｔｅ　ｔａｂ】ｅ、ｃｒｅａｔｅ　ｉｎｄｅｘ、ｃｒｅａｔｅ　ｔａｂｌｅｓｐａｅｅ、ｃｒｅａｔｅ　ｃｌｕｓｔｅｒ时，在　ｓｔｏｒａｇｅ子句中的参数的合理设置，可以减少碎片的产生。　别名的使用。别名是大型数据库的应用技巧。就是表名、列名在查　询中以一个字母为别名．查询速度要比建连接表快１．５倍。　回滚段的交替使用。由于数据库配置对应用表具有相对静止的数　据字典和极高的事务率特点。而且数据库的系统索引段、数据段也具　有相对静止．并发现在应用中最高的负荷是回滚段表空间　把回滚段　定义为交替引用．这样就达到了循环分配事务对应的回滚段．可以使　磁盘负荷很均匀地分布　３　ＯＲＡＣＬＥ数据规范化的优化　根据约束条件的不同．有１ＮＦ、２ＮＦ、３ＮＦ三种范式。数据规范化的　优点是减少了数据冗余。节约了存储空间，相应逻辑和物理的１，０次　数减少．同时加快了增、删、改的速度。但是一个完全规范化的设计并　不总能生成最优的性能．因为对数据库查询通常需要更多的连接操　作．从而影响到查询的速度。因为“分离”越深，产生的关系越多，结构　越复杂。关系越多．连接操作越频繁，而连接操作是最（下转第５６页）

２０１１年３月　鸶＿｜０。｜　一０　。　。§　第９期总５１７期　羹　＿＿。｜　。　ｊ　－ｌ　０　Ｏｒａｃｌｅ￣据库，陡能优化分析　刘朝宇　（南昌市公安消防支队江西　南昌　３３０００６；同济大学软件学院上海２０００００）　中图分类号：ＴＰ　文献标识码：Ａ　文章编号：１００８－９２５Ｘ（２０１１）０３—０１２１—０３　摘要：　随着网络的普及，电子商务技术得到了迅速发展　・各个站点的访问量随之增大，数据库规模也不断扩大，数据库系统的性能　问题就越来越被广泛关注。因此，如何对数据库进行优化至关重要口本文针对这一问题使用０ｒａｃｌｅ￣ｆｌｌ研究对象进行研究。通　过索引　划分表空间、优化回滚段、分区等技术，对数据库的存储结构和存储路径进行优化，对数据库性能进行测试，寻找一套最佳　酶啦豫Ｉ鸯募　关键词：数据库优化分莲索引回滚段　。　１、选题背景及意义　目前，几乎所有的应用程序都要和数据库打交道。尤其对于　数据量大的系统，用户在进行业务处理时往往感到速度很慢，不　能满足应用的要求。究其原因：一是硬件设备（￣ｓｃｐｕ、磁盘）的存　取速度跟不上，内存容量不够大，当然和网速也有很大的关系；　二是应用程序设计方面的问题；三是没有对数据库进行合理的优　化。针对这一严重影响工作效率的问题，本文结合项目开发经验，　阐述具体的配置方法。对数据库性能的优化可以大大节省用户在　对数据库进行操作的时间，从而可以大大提高工作效率。　作为全球第一大数据库厂商，Ｏｒａｃｌｅ数据库在国内外获得了诸　多成功应用，据统计，全球９３％的上市．ＣＯＭ公司、６５家”财富全球　１００强”企业不约而同地采用Ｏｒａｃｌｅ数据库来开展电子商务。我圆很　多企业、政府单位及电子商务网站也采用了Ｏｒａｃｌｅ作为数据库服务　器。Ｏｒａｃｌｅ数据库服务器是高度可优化的软件产品，经常性的调整　可以优化应用系统的性能，防止出现系统瓶颈【１】。因此本文选用　Ｏｒａｃｌｅ数据库作为研究的对象。　２、系统调整　所谓系统调整是指为了改变系统特性而对系统的软件或硬件　进行的修改。主要表现在以下几个方面：　提高系统吞吐量：使系统能在最短的时间内完成最大的工作量。　缩短系统响应时间：提高系统返回数据的速度。　支持众多用户：使系统能支持最大数据的用户。　很多因素都会影响服务：￣ＪＬＯｒａｅｌｅ的性能：Ｏｒａｃｌｅ实例的调整、操　作系统、硬件以及由用户所产生的数据库负载等等。很多因素都可能　会导致系统性能下降。下面是一些导致系统性能下降的因素［２】：　１．Ｉ／０子系统超载：这可能导致系统需要等待磁盘返回数据而　降低整个系统的运行速度。　２．计算机系统内存不足：内存不足将减少Ｏｒａｃｌｅ用于存放最　近访问过的数据的缓冲区空间，并导致操作系统频繁换页和交　换，从而导致计算机系统额外的Ｉ／０开销。　３．Ｏｒａｃｌｅ资源缺乏：如果Ｏｒａｃｌｅ的共享缓冲池太小，将引发系　统性能方面的问题。　４硬件：硬件方面的问题可能是系统中包括了故障或是系统　中的某种资源（如磁盘或内存）不足。　５．操作系统：这部分的内容包括对数据库的调整和Ｘ￣Ｏｒａｅｌｅ　所使用的操作系统资源的调整。　６．Ｏｒａｃｌｅ：为了获得优化的系统性能，正确调整Ｏｒａｃｌｅ配置是　非常重要的。如果调整不善将会严重影响系统性能。　针对以上影响系统性能的问题，本文将提出六个方案来阐述　具体的性能优化过程。　３、数据库优化方案　对数据库优化的方案有很多，本文主要从以下几个方面着手　研究分析：　１．所有表不建索引，并且数据库内所有的表都存放在同一个　表空间内，对海量数据测试其系统的响应时间。　２．添加索引并从中选择最优索引字段、划分表空间、分离各表　与索引及回滚段、进行分区并对分区建立索引，然后以同样的方法　测试系统的响应时间。　３．优化存储技术。确定数据块、盘曲、非簇表的空间与簇表空　间的数量和大小；优化回滚段；进行缓冲区管理；再测试系统的响　应时『日Ｊ。　４、通过添加显示图来测试系统的响应时间　３．１方案一：不采用任何优化技术来测试系统的性能　１．创建用户：默认存储在ｕｓｅｒｓ表空间内；　２．数据准备：创建数据表，包括三张数据字典，两张业务表，　一个用户表，六张表存在六个关联。其中主业务表（Ｄｒａｇｌｎ表）　１６３００００条记录，占用１４０Ｍ的空间；（ＤｒａｇＯｕｔ表）１４０００００条记　录，占用１２０Ｍ的空间；　３．测试：测试系统的性能，记录查询所用的时间，与下一个方　案进行比较。　３．２方案二：添加索引，优化其查询效率　索引的选择性是指索引列中不同值的数目与表中记录数目的　比。一个索引的选择性越接近于１，表明这个索引的效率就越高。　本测试方案，首先确定用户常用的字段，然后列出全部预索引　字段，最后确定预索引字段的选择性并对选择性高的字段设置索　引。确定索引的选择性，可以有两种方法：手工测量和自动测量。　由于只是对表ｄｒａ　ｎ，ｄｒａｇｏｕｔ的六个字段分别进行并列索引，所　以采用手工测量索引的选择性，从而确定哪种并列索引方案最优。　３．３方案三：划分表空间，分离各类表与索引，回滚段　在以上方案的基础上划分表空间，分离表、索引、回滚段，从　而达到优化数据库性能的目的。具体表现为：数据库管理员可以将　组成同一个表空问的数据文件放在不同的硬盘上，做到硬盘之间　Ｉ／Ｏ负载均衡。　本方案将通过以下方法对数据库进行优化：　１．将表和索引分开。　２．将用户表空间从系统表空间（ｓｙｓｔｅｍ）中分离出来。这将使　用户操作对数据库功能的影响最小化。取消用户在其它表空间中　的定额，并且将缺省表空间设置值改为ＵＳＥＲＳ表空间。　３．创建回滚段专用的表空间，防止空间竞争影响事务的完　成。回滚段的Ｉ／Ｏ通常与ＤＡＴＡ和ＩＮＤＥＸＥＳ表空间的Ｉ／Ｏ同时发　生。将回滚段与数据段分隔开有助于避免发生Ｉ／Ｏ冲突，从而简化　对它们的管理。本方案对表空间创建回滚段并指定表空间为ＲＢＳ　表空间；　４．创建临时表空间用于排序操作，尽可能的防止数据库碎片　：