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

基于Oracle的OLTP与OLAP数据库优化差异之内存设计要进行数据库优化，首先应该弄清数据库类型及其特点。

从数据处理角度分类，数据库可分为两大类：联机事务处理OLTP（on-line transaction processing）和联机分析处理OLAP（On-Line Analytical Processing），OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易，电信业务等。

OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。

两类系统处理的数据量不同，技术的使用也是不一样的，在OLTP系统中，oracle、db2等是主流数据库产品，数据量在几个TB甚至更小。

在OLAP系统中，除了传统的数据库外，有越来越多的其他数据库产品出现，如sysbase IQ,GP，国产数据库GBase 8a，达梦7等，数据量大多在几十TB甚至更高，数据处理主要集中在查询统计分析、大数据量的导入导出等。

在这里，我们主要讨论的还是最通用的ORACLE数据库的两类系统的优化思路和方式的差异。

数据库具体是OLTP还是OLAP取决于你的业务类型。

OLTP和OLAP两类系统对数据库要求截然不同。

除了系统本身自有的特点之外，在技术方面也存在巨大的差别，所带来的优化技术和理念也不太一样。

OLTP主要是交易型数据库，其事务特征为高并发而数据量小，大部分情况都要求瞬间出结果，系统要求的实时性、稳定性、安全性较高。

这类数据库的系统瓶颈主要在CPU和内存上，在优化思路上，更加关注CPU的利用率；各种命中率指标，譬如SHARED POOL， BUFFER CACHE的命中率；SQL语句绑定变量等。

由于数据量相对比较小，因此OLTP大部分都是集中式的。

OLAP主要是分析型数据库，并发量比较小，但单个sql消耗巨大，大部分查询需要处理海量数据，用户对响应时间的要求远不如OLTP用户。

由于数据量巨大，这类数据库的系统瓶颈主要在磁盘io上，一些传统的优化指标和手段，如SHARED POOL， BUFFER CACHE 的命中率等对OLAP型数据库意义不大，而分区，并行操作，压缩，物化视图，全文索引等技术可能都会在OLAP中体现。

OLAP库基本都是上TB的，有的是几百TB，甚至PB，因此很多OLAP库可能都分成了N个库，以便于分布式处理。

OLAP一般都是采用星星模式或者雪花模式的。

下面从内存设计的优化角度具体分析一下这两类系统优化手段的差异：内存设计通常是通过调整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 执行效率。

使用绑定变量是减少硬解析，提高库缓存命中率的有效手段。

数据缓冲区命中率表示在不需要进行磁盘访问的情况下在内存结构中找到常用数据块的频率，可视为OLTP系统的晴雨表，一个好的OLTP系统应该可以得到95%或更大的命中率，由于CPU对内存的访问速度要比从磁盘的速度快千倍，当SQL所需要的数据块都能从内存取得时，SQL执行效率无疑比从磁盘读数据高很多，对于一个OLTP库来说，由于处理的数据量都较小，尽可能让数据块保存在内存中，提高内存命中率，是一个极其重要的性能优化指标。

把缓冲区命中率从90%提高到95%可能使系统性能翻倍。

对缓冲区命中率偏低的OLTP 系统，合适的增大Buffer Cache的大小，可改善数据库性能。

而在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设置一个总的大小尺寸即可。

Oracle 10g数据库会根据系统负载的变化，自动调整各个组件的大小，从而使得内存始终能够流向最需要它的地方。

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

OLTP：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%在OLAP系统中，由于会运行一些很大的查询，Oracle建议分配分配50％给PGA使用，结合实际情况，该比例可以提高至70％至80%。

OLAP：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%而在Oracle 11g中，如果设置了MEMORY_TARGET 参数，那么 Oracle 会尝试在 SGA 和PGA 之间优化内存，并且尝试在指定的内存目标内保持两者的大小。

这又是一大进步，因为在 Oracle 10g 中很难确定最佳设置。

但对于某些应用程序来说，自动内存管理可能不是最佳的解决方案。

自动内存管理往往超过约一小时的短期窗口来调整内存分配。

如果你的系统有突发工作量，或短期需求高峰，那么自动内存管理可以要一天后才能发挥出来。

现在很多数据库是OLAP/OLTP的融合，可能白天处理的事务偏于OLTP型，晚上则较多批处理作业，偏于OLAP型。

在这种情况下，较好的方式是，确定内存的优化设置，或是用固定值，也可以交替设置。

例如，晚上，我们可能会从 SGA 到 PGA 切换内存；而在白天，我们再把内存移到 SGA。

尽管通过修改Oracle内存参数的大小通常可以有效地提高Oracle性能,但是由于内存有限,不可能无限增大，需要在高速缓存命中率和物理内存之间取得平衡。

在增大SGA或PGA 之前，必须明白这些改动对系统的物理内存可能产生的影响，如果增加后的参数值比系统可用内存大，就会严重降低系统性能。

而一个不合适的过大的数据库高速缓存，甚至会导致数据库性能的退化。

当系统处理任务时，如果没有足够的内存，它就会开始执行分页和交换，以完成这个激活的任务。

当系统执行分页时，会将当前没有使用的信息从内存移到硬盘上。

这样就可以为当前需要内存的程序分配内存。

如果频繁地发生分页，系统性能就会严重降低，从而导致很多程序的执行时间变长。

当系统执行内存交换时，会将活动进程临时地从内存移到硬盘上，这样另一个活动进程就可以得到所需要的内存。

内存交换基于系统循环时间。

如果内存交换过于频繁，就会产生大量的I0，应用的性能可能会急剧恶化。

系统内存的配置应尽可能实现如下目标：1)减少分页；2)减少内存交换；3)尽可能让系统全局区(SGA)能驻留内存。

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

专著（文献类型标识：M） [序号] 主要责任者。

题名[M]。

出版地：出版者，出版年，起止页码。

参考文献：Oracle Database 10g性能调整与优化让Oracle跑得更快：Oracle 10g性能分析与优化思路。