基于Oracle的OLTP与OLAP数据库内存设计和优化

摘 要：内存是oracle体系结构的重要部分，合理分配和使用内存，对于性能调优有至关重要的作用。ORACLE数据库的oltp与olap两类系统对数据库要求截然不同，本文从内存设计的角度分析一下这两类系统优化手段的差异。

关键词：ORACLE；OLTP；OLAP；内存设计；优化

中图分类号：TP311.13

Oracle是高度可调的数据库系统，它提供了许多特性，正确地设置和调整可以有效提高系统性能。内存是Oracle体系结构的重要部分，合理分配和使用内存，对于性能调优有至关重要的作用。要进行数据库优化，首先应该弄清数据库类型及其特点。从数据处理角度分类，数据库可分为两大类：联机事务处理OLTP（On-Line Transaction Processing）和联机分析处理OLAP（On-Line Analytical Processing）。这两类系统对数据库要求截然不同，所带来的优化技术和理念也不太一样，本文从内存设计的角度分析一下这两类系统优化手段的差异。

内存设计通常是通过调整ORACLE内存参数来实现的。ORACLE的内存可分为SGA（shared global area）共享全局区和PGA（program global area）进程全局区两部分。OLTP系统由于处理的事务为高并发且数据量小，SGA比PGA更为重要；OLAP系统处理的事务数据量大且并发量比较小，大部分查询可能运行一次可能很久不会在运行，SQL的重用意义不大，很多命中率指标对于OLAP也影响较小，因此SGA相对次要，而较多大数据的排序，HASH操作都需要在PGA完成，PGA大小直接决定了处理效率。

尽管从Oracle 10g开始，Oracle已经减少了对内存命中率的关注，转而通过工作时间（CPU时间或服务时间）和等待工作时所消耗的时间（等待时间）来分析系统的性能，但对一个OLTP库来说，库缓存命中率（Library Hit）和数据缓冲区命中率（Buffer Hit）仍是极重要的指标。OLTP系统是一个SQL执行非常密集的系统，Library Hit低说明共享池里很多SQL不能重用，需要重新解析，这会大大增加CPU负荷，降低系统性能，影响SQL执行效率。加大SHARED POOL；使用绑定变量，修改cursor_sharing参数以减少硬解析，是提高库缓存命中率的有效手段。

数据缓冲区命中率表示在不需要进行磁盘访问的情况下在内存结构中找到常用数据块的频率，可视为OLTP系统的晴雨表，一个好的OLTP系统应该可以得到95%或更大的命中率。由于CPU对内存的访问速度要比从磁盘的速度快千倍，当SQL所需要的数据块都能从内存取得时，SQL执行效率无疑比从磁盘读数据高很多，对于一个OLTP库来说，由于处理的数据量都较小，尽可能让数据块保存在内存中，提高内存命中率，是一个极其重要的优化目标。把缓冲区命中率从90%提高到95%可能使系统性能翻倍。对缓冲区命中率偏低的OLTP系统，合适的增大Buffer Cache的大小，可改善数据库性能。也可通过对某些表设置keep buffer pool和recyclebuffer pool属性来提高该指标。

而在OLAP系统中，就无法以这两个指标来衡量数据库性能。OLAP库运行的SQL所查询的数据块量巨大，不可能长期缓存在内存中；每次执行的SQL重复率不高，连接用户少，SQL硬解析的代价可以忽略，SQL是否重用并不重要，在某些情况下，使用绑定变量重用SQL，甚至会因为选择了错误的执行计划而导致严重的后果。

对于OLAP系统，我们可以用另一种内存命中率来评估系统，即PGA内存排序命中率。该指标反映了在PGA中完成的排序操作的比例。当要求排序或执行一个哈希连接时，Oracle可能会执行大量IO。Oracle会尽量在 PGA 内执行排序或哈希连接。但是，如果内存不够，那么Oracle会将数据写到临时表空间的临时段。如果PGA太小，那么在排序期间，Oracle可能从磁盘读写数据很多次。数据库响应时间会随着内存的减少而显著增加。OLAP系统的排序操作或哈希连接操作极多，过小的PGA设置对OLAP型数据库性能的影响是灾难性的。除了设置足够大的PGA尺寸，将临时表空间部署在IO速度较快的磁盘上；启用并行等方法也可提升这类操作的效率。

Oracle的内存SGA和 PGA两大块，前者帮助我们阻止逻辑读取变成物理读取，后者帮助我们阻止排序和哈希操作产生IO到临时表空间，很难做到平衡。

从Oracle 10g开始，Oracle提供了自动共享内存管理ASMM（Automatic Shared Memory Management）新特性。所谓ASMM，就是指我们不再需要手工设置shared pool、buffer pool等若干内存池的大小，而是为SGA设置一个总的大小尺寸即可。Oracle10g数据库会根据系统负载的变化，自动调整各个组件的大小，从而使得内存始终能够流向最需要它的地方。

根据Oracle的建议，Oracle最多可以使用80%的物理内存，其余20%保留给操作系统使用，在这80%的内存中，对于OLTP系统，Oracle建议分配20%给PGA使用，剩下80%分配给SGA。

OLTP：PGA_AGGREGATE_TARGET = （total_mem * 80%） * 20%

在OLAP系统中，由于会运行一些很大的查询，Oracle建议分配分配50%给PGA使用，结合实际情况，该比例有时可以提高至70%。

OLAP：PGA_AGGREGATE_TARGET = （total_mem * 80%） * 50%

而在Oracle 11g中，通过设置一个参数memory_target就能够实现SGA和PGA组件依据工作负荷进行自动内存分配。这又是一大进步，因为在Oracle10g中DBA往往难以确定最佳设置。

Oracle推荐使用自动内存管理简化内存分配，但对于某些应用程序来说，自动内存管理可能不是最佳的解决方案。自动内存管理需要收集相关信息来调整内存分配，这可能会有超过约一小时的窗口。如果你的系统有突发工作量，或短期需求高峰，那么自动内存管理功能可能一天后才能发挥出来。现在很多数据库是OLAP/OLTP的融合，白天处理的事务偏于OLTP型，晚上则较多批处理作业，偏于OLAP型。在这种情况下，较好的方式是，确定内存的优化设置，或是用固定值，也可以交替设置。

Oracle数据库系统性能与多方面因素有关，在这里从内存设计角度提供了一些优化思路，尽管Oracle 11g已经提供了更为强大的内存自动管理功能，但作为DBA，要想让所维护的数据库处于较好的性能下，仍应该对这两类系统的业务差别和Oracle体系结构有深刻理解，才能做到有的放矢，而不是希望通过简单修改某个参数，就能让数据库性能得到提升。

参考文献：

[1]谭怀远.让Oracle跑得更快――Oracle10g性能分析与优化思路[M].电子工业出版社，2010.

[2]（美）尼米克（Niemiec，R.J）.Oracle Database 10g性能调整与优化[M].清华大学出版社，2009.

作者单位：中国电信股份有限公司汕头分公司，广东汕头 515041