Oracle ——优化内存

http://www.toadworld.com/Experts/GuyHarrisonsImprovingOraclePerformance/OracleTuningSeries/ October2008OptimizingMemory/tabid/481/Default.aspx

本文内容

逻辑 IO 和物理 IO
调优缓冲区高速缓存
调优 PGA
ORACLE 11G 自动内存管理
结论

逻辑 IO 和物理 IO

逻辑 IO 的组成是请求数据库文件中的信息以及临时结果集，临时结果集是连接和子查询所请求，或是排序结果，或是为连接和类似的操作创建的哈希结构。减少逻辑 IO 是 SQL 调优的主要目标之一，但同样重要的是，尽可能阻止逻辑 IO 变成物理 IO。

逻辑 IO 操作是很高效的内存操作，它用现实世界中的电子来实现，而电子以光速通过集成电路。而现在的大多数物理 IO 都是由旋转磁盘设备，并移动读写头。这些机械操作与内存操作相比简直是令人难以置信的慢。尽管磁盘设备变得越来越快，但无论如何它们都不会跟 CPU 和内存访问一样快。

因此，尽可能防止逻辑 IO 变成物理 IO 是绝对重要的。这是有效内存配置的关键。

优化内存有两种 IO 类型。数据文件的 IO 可以通过合适的缓存区大小来被最小化；而临表空间的 IO 可以通过优化 PGA 来被最小化。如图 1 所示。

图 1 Oracle 使用缓冲池和 PGA 内存来避免数据文件和临时表空间

调优缓存区高速缓存

分配给 Oracle 缓冲区高速缓存的内存存储数据库块的副本，这样，如果在内存中请求了一个数据块，就能减少执行物理 IO 的请求。以前，Oracle DBA通过检查的“缓冲区高速缓存命中率”（在内存中满足 IO 请求数据块的百分比）来调优缓冲区高速缓存的大小。但这种方法已证明很容易出错。如果所有的事情（条件）都是等价的，那么一个较高的缓存区高速缓存命中率当当然比一个低命中率要好。可问题是，当采用这些度量时，事情（条件）几乎从来就不等价的，并且一个非常高的缓存区高速缓存命中率的常见副作用是不必要的高逻辑 IO。

在现代 Oracle 数据库，可以通过利用 Oracle 咨询方面的视图，准确地确定调整缓冲区高速缓存大小。V$DB_CACHE_ADVICE^[1] 视图显示了，具有不同大小的缓存区高速缓存将发生或避免物理 IO 的数量。检查该视图将有助于是否增加缓冲区高速缓存来帮助避免 IO，或是否减少缓冲区高速缓在不影响 IO 的情况下释放内存。

Oracle 可以让你设置单独的内存区域来高速缓存不同大小的数据块，也可以让你指定 KEEP 或 RECYCLE 区域从全表扫描高速缓存数据块。通过把频繁访问的小表放在 KEEP，以及把不常用的大表放在 RECYCLE，来优化 IO。V$DB_CACHE_ADVICE 可以让你适当地调整每个区域的大小，尽管这种调整在 Oracle 10g 和11g 可以自动发生。

缓冲区高速缓存位于系统全局区（System Global Area，SGA），它还有其他重要的共享区域，如共享池（shared pool）、Java 池和大型池（large pool）。Oracle 10g 在 SGA_MAX_SIZE 参数的约束范围内可以自动调整其大小。

Oracle 通过 LRU （Least Recently Used）算法管理缓冲池中的数据块。这个算法很复杂，但是一般来说，越是经常访问的数据块，停留在缓冲池的时间越长。很长时间没被访问的数据块会因“过期”而被移出内存。

为了避免全表扫描，一次淹没缓冲池，该算法对全表扫描读取使用索引读。从全表扫描中的数据块倾向立即“过期”，而从索引查找获得的数据块将进入缓冲池，直到被其他数据块取代。

调优 PGA

除了磁盘读取来访问数据，而不是在缓冲区高速缓存，当要求排序或执行一个哈希连接时，Oracle 可能会执行大量 IO。在可能情况下，Oracle 在 PGA 内执行排序或哈希连接。但是，如果内存不够，那么 Oracle 会写到“临时”表空间的临时段。

没有足够的 PGA 所造成的影响是很大的。如果内存少，那么在排序期间，Oracle 可能从磁盘读写数据很多次。图 2 显示对于带 ORDER BY 子句的一个全表扫描，响应时间如何随着内存的减少而显著增加。

图 2 IO from sorting can become excessive if the memory available is too small

排序和哈希连接的可用内存的大小主要由 PGA_AGGREGATE_TARGET 参数决定。V$PGA_TARGET_ADVICE[2] 视图将显示增加或减少 PGA_AGGREGATE_TARGET 如何影响临时表的 IO。

ORACLE 11G 自动内存管理

Oracle 数据库 10g 会在 PGA 和 SGA 内部有效地管理内存。但在此之前，直到 Oracle 11g，仍不能在两个区域之间移动内存，只能依靠 DBA 来确定有效地把内存分配给这两个区域。不幸地，这两个相关的建议使用不相同的单位度量 IO——V$DB_CACHE_ADVICE 视图使用 IO 数量，而 V$PGA_TARGET_ADVICE 使用 IO 字节。这样，如果减少整体 IO，就很难解决了。如果增加这些区域中的一个，就得以牺牲掉另一个为代价。另外，把 IO 保存报告与 IO 时间等待联系起来也很困难。不过，在 PGA 与 SGA 之间权衡一个合适的大小可能是最重要的内存配置，它对数据库必须执行的物理 IO 的数量有很大影响。

如果设置了 Oracle 11g 的 MEMORY_TARGET 参数，那么 Oracle 会尝试在 SGA 和 PGA 之间优化内存，并且尝试在指定的内存目标内保持两者的大小。这可能是一大进步，因为在 Oracle 10g 中很难确定确定最佳设置。

自动内存管理（Automatic memory management）值得尝试，特别是如果你关心你的整体 IO 负载。然而，对于某些应用程序来说，自动内存管理可能不是最佳的解决方案。自动内存管理往往超过约一小时的短期窗口来调整内存分配。如果你的系统有突发工作量，或短期需求高峰，那么自动内存管理可以要一天后才能发挥出来。在这种情况下，较好的方式是，确定内存的优化设置，或是用固定值，也可以交替设置。例如，晚上，我们可能会从 SGA 到 PGA 切换内存，这是当有很长的批处理作业需要大排序区域时；而在白天，我们再把内存移到 SGA 以最小化 OLTP 的数据文件 IO。

为你的工作量确定准确的内存数量是一个复杂的工作，要求你把各种咨询转化成一个共同的度量单位。幸好，如果你使用 Toad，那么 Spotlight 内存管理工具将为你做这一切。图 3 显示运行中的内存管理模块。