与Oracle数据库相关的应用软件性能优化思路

面向企业级的定制化开发应用软件一般都是数据库-中间件-浏览器客户端三层结构。如果用户在浏览器客户端进行操作时存在性能问题，如页面打开缓慢或者执行某个操作半天没有响应时，往往是后台的数据库或中间件层存在问题。本文重点探讨当中间件层正常，只是数据库层的sql语句执行缓慢时，该如何提高sql语句的执行效率，进而提高前台浏览器客户端的响应时间。

主要从如下几个方面来进行优化。第一，整体调整。第二，索引增加修改或删除。第三，SQL语句重构。第四，使用Oracle特性。

1、整体调整。指的是先对SQL语句涉及的相关表和索引的物理结构和存储占用空间进行调整。并根据表和索引的大小和使用频率决定是否将表、表分区和索引常驻内存、是否需要将表放在keep池热端。具体说来，有如下几步：第一，查看相关表的大小。如果大于4G，可以考虑将该表分区。重点是选好分区列。如果决定将表进行分区，则该表的索引需要重新规划和建立。如果不决定分区，则继续执行第二步。第二，将相关表进行高水位线回收，推荐使用alter table move的方式。一般这样操作之后，表的存储占用会大幅减少，不论执行计划是全表扫描还是走相关索引，执行效率都会有明显增加。第三，如果这些相关表的记录变动不频繁，可以对其进行表压缩。这一步是可选的，在OLTP系统中一般不做表压缩。第四，对相关表的所有索引进行重建。第五，对相关表和这些表上的索引进行统计分析。第六，如果相关的表的存储空间占用在200M以下，并且使用频率非常频繁，可以考虑将相关表放入keep池。该低效SQL用到的索引也放入keep池。第七，将刚刚放入keep池的表置于keep池队列的热端。

[@more@]

2、索引增加、修改或删除。经过上面第一个方面的整体调整之后，很多Sql语句的执行效率已经有了大幅的提高，尤其是对高频率访问的中小表，效果更加明显。但对有些Sql语句其性能依然没有达到目标要求。这时需要考虑重点对这些Sql对应的表的索引进行优化，具体说来，有如下几步：第一，增加索引。一般需要先看看这些低效Sql是否索引缺失。如果Sql语句执行后返回的记录数不多（不超过记录集总数的5%，最多10%），而执行计划中存在对某个大表的全表扫描，对应列上也没有建立索引，这时就要千方百计尝试能否通过增加索引来改变Sql执行计划，通过走索引避免全表扫描来提高Sql的执行效率。第二，修改索引。对于一个生产环境，大多数情况下是索引已经被密密麻麻的建立，一般不存在索引缺失。但普遍存在的情况是明明有索引Sql语句执行时却偏偏不走索引，明明Sql语句执行时走了索引执行效率却很低。对于这两种情况，主要考虑对现有索引进行观察和研究来确定索引类型是否正确，然后根据sql对应的业务类型和相关表的数据分布特点来选择合适的索引类型将原索引进行修改或替代（其实就是删除和建立两个步骤，一般不直接修改）。比如，原来是单列索引不起作用，经研究改为复合索引后Sql执行时开始走索引；原来是一般B-tree索引，虽然Sql执行时走索引但效率不高，经过对表的数据分布进行研究后改为“反向”索引后Sql效率大幅提高等等。第三，删除索引。索引要按需建立，索引不是越多越好，过多的索引不仅会对相关表的DML操作的效率有影响，还会使有些Sql语句的执行效率降低。当Sql语句执行返回的记录数较大（超过记录集总数的10%）或者Sql语句对应的表是小表（小于1M）时，这时Sql语句执行时进行全表扫描要比走索引快。如果某个索引是专门为了“提高”该类型Sql的效率而建立，一般会被认为是索引冗余，建议删除。

3、Sql语句重构。经过第二个方面对索引调整之后可能还是有一些Sql语句的执行效率没有达到目标要求。这时需要考虑对Sql语句进行重构，主要原因有三个，一是只有修改Sql语句才能让该Sql用上某个索引，进而提高执行效率；二是只有修改Sql语句才能让该Sql不要用到某个索引，进而提高执行效率；三是通过上述第一方面和第二方面的努力，即使相应表的物理结构和存储空间已经优化到极致、索引也没有任何问题，但Sql语句执行效率仍然不高，依然达不到目标要求，只有彻底改变实现业务逻辑的算法和处理机制才能提高效率。在进行Sql语句重构时，具体说来，一般分为如下三种情况：第一，对Sql语句增加Hint。这是Sql语句重构里最简单的情况，也是改动最少的情况。一般主要用于在不对Sql语句做其它改动的前提下就能通过使用某个已有索引大幅提高Sql执行效率或不走索引强制全表扫描提高Sql效率的情况。这需要做好测试，一旦测试效果明显，即可增加相应hint。第二，修改Sql语句的结构。当单单通过增加hint无法使用某个索引或即使走了相关索引Sql执行效率也不会提高时，就需要在不改变Sql语句功能的前提下对Sql语句的结构进行重新调整，如由多表嵌套变为多表连接等，使Sql执行时能高效使用索引，进而提高执行效率。第三，修改Sql语句的算法和处理机制。这个动作就比较大了。当前面所有的路都走不通了（第一方面、第二方面、第三方面的前两个情况），就需要跳出现有思维定式，重新研究分析实现业务逻辑的算法和处理机制，这时进行Sql语句重构一般都是颠覆性的，如采用新的算法和处理机制将一条复杂的Sql分拆成几个相对简单和独立的sql，提高分拆后单个Sql的执行效率，再通过应用代码将它们有机的联系起来，可串行，可并行；如对有些业务逻辑，经充分研究其业务特点后，加入预处理机制，进而重构相关Sql语句或加入新的Sql或存储过程来实现等等。

4、使用Oracle特性。Oracle作为一款优秀的数据库管理软件，在基础数据库理论和相关通用标准和通用技术之上提供了很多Oracle独有的特性，正确使用这些Oracle特性，会使Sql语句乃至整个数据库的性能获得极大提升。Oracle特性可以和上述三个方面的任一方面结合起来使用。具体说来，平常经常用到的特性如下：第一，分区特性。可以对表进行分区，也可以对索引进行分区，还可以对物化视图进行分区。分区是把双刃剑，不合理的分区不会提高Sql性能，然而一旦熟悉分区特性，彻底掌握该特性，通过与其他特性相结合（如并行执行、压缩等）可以发挥出惊人的效果，极大的提高Sql乃至整个数据库性能和呑吐量。第二，高级压缩特性。这一特性主要应用在备份和数据仓库中，最初主要是为了省钱（通过节省存储空间而节省成本），不过，压缩特性不光能让你省钱，用好了的话，对合适的表进行压缩还可以提高相关Sql的执行效率。第三，并行执行特性。这个特性在多CPU硬件环境和RAC多节点环境（节点多于2个）下可以极大的提高一些Sql的执行效率，这些Sql的类型主要是全表扫描类查询、大数据量加载、大索引建立、分区表上的 DML 操作等。第四，查询重写特性。这一特性涉及到物化视图，是一个“用空间换时间的思路”。不过，使用该特性有一个前提，就是查询的时效性要求。如果要求实时查询并且基表的变化非常频繁，则该特性不适用。如果允许每次查询只要能查到某个时间间隔之前即可，并且基表的变化也不是很频繁，为了大幅提高相关Sql的查询性能，可以考虑建立物化视图，启用查询重写特性，使每次的查询操作直接访问已经建好和同步好的物化视图的数据，由于物化视图已经建好、同步好（即要查的数据已经准备好），故查询效率大幅提高。第五，负载均衡特性。当Oracle数据库配置为RAC多节点实例的集群数据库时，可以根据需要确定是否启动负载均衡特性。通过负载均衡特性可以很好的分担应用压力，提高系统的吞吐量。如果不启用RAC数据库的负载均衡特性，也可以考虑人工手动进行负载均衡，即通过对中间件层各应用模块在RAC数据库各实例上的合理分布和动态调整，使RAC数据库的各实例所在节点的CPU利用率和内存使用情况保持在一个相对正常的范围内。第六，PL/SQL特性。面向企业级的定制化开发应用软件如果使用Oracle数据库一般都需要专门的数据库开发工程师使用pl/sql来编写相关的存储过程、函数、触发器等。而目前的现状是大量的pl/sql代码都是使用Oracle 8i甚至更加古老的时代的语法和处理机制来编写的。Oracle9i及9i以上的大量的PL/SQL新特性并没有被使用。很多传统上比较消耗资源的操作其实已经在新的PL/SQL版本中被优化，比如一些低效的循环操作的pl/sql代码写法在新版本中其实已经提供了新的语法通过更高效的批量操作提高了执行效率。所以，数据库开发工程师一定要对PL/SQL新特性敏感，经常更新自己的PL/SQL知识，不要再出现数据库版本已经升到了oracle 10g甚至11g而相关PL/SQL代码仍然停留在8i水平的悲剧。第七，内存数据库特性。对于一些业务逻辑并不复杂但执行速度要求非常高的Sql，可以考虑采用Oracle的内存数据库特性，将这些Sql及对应的所有对象全部加载到内存数据库中，并将内存数据库与传统Oracle数据库相结合，一起对外提供服务。第八，Exadata一体机特性。如果应用系统实在太庞大，如果数据库的数据量实在太夸张，如果业务逻辑实在太复杂而不得不进行大量的全表扫描和巨量的DML操作，那就选择Exadata一体机吧，这一Oracle的新技术，可以让全表扫描跟走索引一样快，光这一点就可以颠覆传统应用系统针对性能问题的设计思路，进而使整个开发过程变得更简单，效率却不会下降。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24663694/viewspace-1044992/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24663694/viewspace-1044992/