oacle数据库性能优化方法论和佳实践(oacle数据库性能调优)

导语：关于Oracle数据库性能优化，你可能已经走了弯路！

OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，以完成当前小事务处理为目的，例如银行交易系统和网购系统。

1.OLTP性能关注指标 OLTP

全称为联机事务处理（Online Transaction Processing），其特点是事务性非常高，一般都是高可用的在线系统，以小的事务以及小的查询为主，其每秒执行的Transaction以及Execute SQL的数量是其性能指标的主要指标。数据库每秒处理的Transaction达到几百以上，Select 语句的执行量每秒上千至万。典型的OLTP系统有电子商务系统和银行。

2.OLTP系统的瓶颈点

OLTP系统最容易出现瓶颈的地方就是CPU资源与IO资源。

（1） CPU资源主要消耗在逻辑读总量、自定义的函数或者存储过程上这样的系统中单个语句执行速度虽然很快，但是执行次数非常多，同样会导致很大的逻辑读总量。要减少单个语句的逻辑读，或者是减少执行次数。自定义函数、decode等的频繁使用，也会消耗大量的CPU资源用户这些调用的解析，要尽量避免调用自定义的函数和计算过程。

（2） 磁盘子系统在OLTP环境中，它的瓶颈取决于它的IOPS处理能力.

因为在OLTP环境中，磁盘物理读一般都是db file sequential read，也就是单块读，虽然数据量不大，但是操作非常次数非常频繁。超过磁盘的IOPS的时候系统比如面临IO的性能问题。

3.OLTP系统的优化策略

OLTP常用的设计与优化方式为：Cache技术、B-tree索引技术、绑定变量和热块争用处理

（1） Cache技术使得数据可以从内存获取，相对于从磁盘子系统获得数据有数量级上的响应优势，Oracle data buffer、应用服务器的buffer以及Web cache优化是提高OLTP系统的好对策。

（2） B-tree索引优先考虑。简洁语句格式，减少语句解析，尽量减少表关联，尽量减少分布式事务，基本不使用分区技术、物化视图技术、并行技术及位图索引。

（3） OLTP 系统的SQL语句提交非常频繁，所以数据库的变更也非常频繁。尽可能让数据块保存在内存当中，对于SQL来说，尽可能使用变量绑定技术来达到SQL 重用，减少物理I/O 和重复的SQL 解析。

（4） 注意热块问题争用。 当数据库有争用是，Oracle使用Latch来机制串行化用户的操作。Latch机制保证了数据库的数据一致性，但是Latch机制会导致用户等待，获取这个数据块的用户越多，Latch等待就越明显,当Latch数量分配不足时候，CPU就会进行频繁的SPIN，到达阀值以后，语句暂时进入休眠，就会导致CPU进行上下文切换（contex switch），这时候CPU要保存当前进程的运行时信息如堆栈、信号量等数据结构，引入后续进程的状态信息，处理完成后再切换回原来的进程。这将消耗巨大的CPU开销。这种热块争用可能是数据块，也可能是回滚端块。 对于数据块来讲，通常是数据库的数据分布不均匀导致，如果是索引的数据块，可以考虑创建反向索引来达到重新分布数据的目的，对于回滚段数据块，可以适当多增回滚段来减少这种争用。

二、OLAP系统的优化思路：

OLAP是数据仓库系统的主要应用，主要是统计分析业务，提供对历史数据进行多维度的统计分析，主要服务于决策支持。

1.OLAP系统性能关注指标

OLAP也叫DSS决策支持系统，等同于数据仓库。OLAP的业务操作往往非常复杂，执行语句虽然不多，但是单条语句的耗时长，读取数据量大，IO子系统的吞吐量（带宽）是重要考核标准。

2.OLAP系统的瓶颈点

OLAP操作往往对上百万甚至上亿条记录进行统计分析，数据读取量上GB甚至几十GB以上，受限于数据库内存，通过 Cache来缓存基本没有效果。

此时需要突破的瓶颈是IO子系统的吞吐量，而该吞吐量首先受限于磁盘的个数或者磁盘系统的阵列策略。

3.OLAP系统的优化策略

在OLAP系统中，常使用分区技术、并行技术。

（1） 分区技术在OLAP系统中的获益主要体现在数据库管理上，分区主要的功能是管理上的方便性，它并不能绝对保证查询性能的提高，有时候分区会带来性能上的提高，有时候会降低。下列情况可以使得效率提升。如表分区可以减少表扫描的IO，结合并行的话，全表扫描的效率也提高。

（2） 并行技术如RAC中的多节点的同时扫描，把任务分布在多个RAC的节点。

（3） 由于Cache作用效果不大，OLAP在内存上可优化的余地很小，增加CPU 处理速度和磁盘I/O 速度或者数量，或者优化磁盘阵列都能直接的提高数据库性能，提高带宽等。但成本大，需要额外硬件投入。

三、针对Oracle性能优化，个人的一点建议:

1.分区技术要慎用，对分区索引有性能提高的促进。但是对不使用分区索引的语句时分区技术并无性能提高，甚至造成性能降低，如全局索引和本地索引。

2.并行技术适用于大型的任务时才有性能提高，小事务的并行计算甚至会导致性能下降，因为事务并行分配同样需要开销资源。

3.绑定变量的策略适用于OLTP系统，不适用于OLAP系统中。OLAP系统因为整个系统的语句执行数量很小，分析时间远远小于执行时间。OLTP系统通常用户并发数很大，用户的请求十分密集，通过使用绑定变量可以使得SQL实现重用。

4.为避免阻塞和死锁，不建议用在OLTP环境中使用位图索引。物化视图和触发器也类似，在DML频繁的OLTP系统上，很容易成为瓶颈，甚至是Library Cache等待；而位图索引适用在OLAP则可能会因为使用恰当而提高查询性能。OLTP注重的是快速提交处理，OLAP注重的是大吞吐处理。

5.对于OLAP系统，SQL语句逻辑优化贡献大，因为它的数据量很大，是否全表扫描和索引是否有效利用都将导致性能迥然不同。

本文内容由小开整理编辑！