为 pga_aggregate_target 开发特征码
Oracle 数据库中的 PGA 区域非常重要，因为它控制排序操作以及 SQL 散列联接的速度。在以下的某一种情况出现时，您可能希望动态更改 pga_aggregate_target 参数：
只要 v$sysstat 统计量 "estimated PGA memory for one-pass” 的值超过 pga_aggregate_target，您就希望增加 pga_aggregate_target。
只要 v$sysstat 统计量 “workarea executions ? multipass” 的值大于百分之一，数据库就可能得益于额外增加的 RAM 内存。
有可能出现过量分配 PGA 内存的情况，而只要 v$sysstat 行 “workarea executions?optimal” 的值持续显示百分之百时，您可能会考虑减少 pga_aggregate_target 的值。
v$pgastat 视图提供对 PGA 使用情况以及自动内存管理程序的实例级汇总统计信息。为快速获得概要信息，有个简单的查询提供了关于所有 Oracle Database 10g 连接的总体 PGA 使用情况的极佳统计信息：
check_pga.sql
-- *************************************************************
-- Display detailed PGA statistics
--
-- *************************************************************
column name format a30
column value format 999,999,999
select
name,
value
from
v$pgastat
;
该查询的输出可能类似于以下信息：
NAME VALUE
------------------------------------------------------ ----------
aggregate PGA auto target 736,052,224
global memory bound 21,200
total expected memory 141,144
total PGA inuse 22,234,736
total PGA allocated 55,327,872
maximum PGA allocated 23,970,624
total PGA used for auto workareas 262,144
maximum PGA used for auto workareas 7,333,032
total PGA used for manual workareas 0
maximum PGA used for manual workareas 0
estimated PGA memory for optimal 141,395
maximum PGA memory for optimal 500,123,520
estimated PGA memory for one-pass 534,144
maximum PGA memory for one-pass 52,123,520
在上面来自于 v$pgastat 的显示内容中，我们看到以下重要的统计信息：
Total PGA used for auto workareas ― 该统计量监视所有以自动内存模式运行的连接的 RAM 使用情况。记住，Oracle 没有允许所有内部进程使用自动内存特性。例如，Java 和 PL/SQL 将会分配 RAM 内存，而这将不会计算在总的 PGA 统计量中。因此，您应该从分配的总 PGA 中减去该值，以便了解由连接所使用的内存量和由 Java 和 PL/SQL 所使用的 RAM 内存量。
Estimated PGA memory for optimal/one-pass ― 该统计量估计出以最优化模式执行所有任务连接 RAM 请求时需要多少内存。记住，当 Oracle Database 10g 遇到内存短缺情况时，DBA 将调用多步操作，试图找到最近释放的 RAM 内存。在 Oracle Database 10g 中，该统计量对于监视 RAM 使用情况非常重要，大部分 Oracle DBA 会将 pga_aggregate_target 增加到此值。
在 Oracle Database 10g 中可以使用称为新顾问实用程序的 v$pga_target_advice。该实用程序显示从当前值的 10% 到 200% 的不同大小的 pga_aggregate_target 的最优化、一步和多步 PGA 执行的临界差别。
列表 4 显示使用这一新的实用程序的示例查询，以下是输出的示例。在这里我们看到，已经为当前的处理超量分配了 pga_aggregate_target，可以安全地从这一区域提取 RAM 并将它分配到其他地方：
Estimated Estimated
Target(M) Cache Hit % Over-Alloc.
---------- ----------- -----------
113 73 0
225 81 0
450 92 0
675 100 0
900 100 0
1080 100 0
1260 100 0 <= current size
1440 100 0
1620 100 0
1800 100 0
2700 100 0
3600 100 0
5400 100 0
7200 100 0
可以看到，您能够方便地创建自动方法来检测 PGA 内存短缺情况（使用 Statspack）并编写作业来动态更改 pga_aggregate_target，以确保为排序和散列联接进行最优化的 RAM 使用。
为数据缓冲区开发特征码
DBA 将会注意到，在实际情况中，数据缓冲区命中率 (DBHR) 的变化会随着测量间隔的频率增加而增加。例如，Statspack 可能在以小时为单位的间隔时报告 DBHR 为百分之九十二，但在采样率以两分钟为间隔时，将显示很大的变化，如图 3 所示。
作为一般性原则，应该调整主机上的所有可用内存，并且应该为 db_cache_size 分配达到增益递减点的 RAM 资源。（参见图 4）。在该点处增加缓冲区块不会显著提高缓冲区命中率。
新的 v$db_cache_advice 视图类似于 Oracle7 中推出的一个用于跟踪缓冲区命中情况的旧实用程序 x$kcbrbh；同样，x$kcbcbh 视图用于跟踪缓冲区遗漏情况。数据缓冲区命中率可以提供与 v$db_cache_advice 所提供内容相类似的数据，因此多数 Oracle 调整的专业人员可以使用这两种工具来监视其数据缓冲区的有效性。
当 v$db_cache_advice 实用程序已经启用，并且数据库已经运行了足够长的时间来提供有代表性的结果时，可以使用列表 5 中的脚本来执行高速缓存建议功能。使用这一脚本，您可以获得对您所有缓冲区池的高速缓存建议，包括 2k、4k、8k、16k 和 32k 数据缓冲区。
该脚本的输出如下所示。注意，数值的范围从 db_cache_size 当前大小的百分之十直到当前大小的两倍。
Estd Phys Estd Phys
Cache Size (MB) Buffers Read Factor Reads
---------------- ------------ ----------- ------------
30 3,802 18.70 192,317,943 <- 10% size
60 7,604 12.83 131,949,536
91 11,406 7.38 75,865,861
121 15,208 4.97 51,111,658
152 19,010 3.64 37,460,786
182 22,812 2.50 25,668,196
212 26,614 1.74 17,850,847
243 30,416 1.33 13,720,149
273 34,218 1.13 11,583,180
304 38,020 1.00 10,282,475 Current Size
334 41,822 .93 9,515,878
364 45,624 .87 8,909,026
395 49,426 .83 8,495,039
424 53,228 .79 8,116,496
456 57,030 .76 7,824,764
486 60,832 .74 7,563,180
517 64,634 .71 7,311,729
547 68,436 .69 7,104,280
577 72,238 .67 6,895,122
608 76,040 .66 6,739,731 <- 2x size
如图 4 中所标注，数据缓冲区最优化设置的位置就是附加缓冲区的临界效益开始减少的位置。当然，该优化点将在一段时间后改变，这就是为什么我们需要预先重新配置 SGA 的原因，以便于我们能够根据当前的处理需要来更改数据缓冲区的大小。
对于趋势分析，DBHR 中的变化并不重要，可以沿两个方向生成平均数据缓冲区命中率：一周中每天的平均 DBHR 和一天中每小时的平均 DBHR。
记住，在数据缓冲区中变化快速地发生，有时长期的分析将会提供线索，指出数据库中的处理故障问题。几乎每个 Oracle 数据库都提供链接到常规处理计划的模式，称为特征码。
以下显示一个 Statspack DBHR 每小时平均值脚本的输出。报告基于六个月的数据收集，显示每天的平均命中率。如果在电子表格中绘制该数据，则该数据库的 DBHR 特征码变得显而易见。
hr BHR
-- -----
00 .94
01 .96
02 .91
03 .82
04 .80
05 .90
06 .94
07 .93
08 .96
09 .95
10 .84
12 .91
13 .96
14 .95
17 .97
18 .97
19 .95
20 .95
21 .99
22 .93
23 .94
该数据的绘图如图 5 所示，我们看到一些有趣的重复趋势。
从图中清楚地看到，每天上午 3:00、4:00 和 10:00，DBHR 降到百分之九十以下。为解决此问题，DBA 可以安排动态的调整，每天为 db_cache_size 添加更多的 RAM。
一个类似的脚本可以提供一周中每天的平均 DBHR，如列表 6 所示；脚本的输出如下。
DOW BHR
--------- -----
sunday .91
monday .98
tuesday .93
wednesday .91
thursday .96
friday .89
saturday .92
然后我们可以将该数据粘贴到一个电子表格中并绘制成图表，如图 6 所示。
该报告在确定周期性或常规的缓冲区特征码时很有用，而图形清楚地显示，需要在周一和周五增加 db_cache_size。要了解原因，您可以使用 Statspack 来调查一周中这几天与其他天的区别。
然后，让我们来检查另一种自调整方法，我们利用这种方法自动识别对象并将其分配到 KEEP 池中，用于完全的 RAM 高速缓存。
自动进行 KEEP 池分配
根据 Oracle 文档，“适合放入 KEEP 池中的段小于 DEFAULT 缓冲区池大小的 10%，并至少引发系统中总 I/O 量的 1%。”很容易找到小于其数据缓冲区大小 10% 的段，但 Oracle 没有在段级跟踪 I/O 的直接机制。一种解决方案是将每个段都放置到一个单独的表空间中，在此情况下 Statspack 将显示总的 I/O，但对于具有数百个段的复杂模式而言，这不是切实可行的解决方案。
由于 KEEP 的核心构思是完全高速缓存，我们希望找到那些小型并且具有不成比例的活动量的对象。利用这一原则，您可以考虑对以下情况的对象进行高速缓存：
该对象消耗了数据缓冲区总大小的 10% 以上
该对象的 50% 以上已经处于数据缓冲区中（根据 x$bh 查询）。
为识别这些对象，我们首先解释数据库中所有查找小型表、全表扫描的 SQL。接着，我们重复地检查数据缓冲区高速缓存，查看任何其数据块的 80% 以上位于 RAM 中的对象。 接下来的步骤
为 KEEP 池识别表和索引的最佳方法会检查数据缓冲区中的当前块。对于此查询，其规则很简单：任何其数据块的 80% 以上处于数据缓冲区中的对象应该进行完全高速缓存。
不合适的表或索引不大可能满足这一标准。当然，您可能在一天之中需要多次运行这一脚本，因为缓冲区内容变化非常快。
可以每小时通过 dbms_job 运行列表 7 中的脚本，并自动监视 KEEP 池的候选对象。每当找到一个候选对象时，DBA 将执行该语句并调整总的 KEEP 池大小，以适应于新的对象。以下是该脚本的输出：
alter TABLE LIS.BOM_DELETE_SUB_ENTITIES storage (buffer_pool keep);
alter TABLE LIS.BOM_OPERATIONAL_ROUTINGS storage (buffer_pool keep);
alter INDEX LIS.CST_ITEM_COSTS_U1 storage (buffer_pool keep);
alter TABLE ISM3.FND_CONCT_PROGRAMS storage (buffer_pool keep);
alter TABLE ISM3.FND_CONCT_REQUESTS storage (buffer_pool keep);
alter TABLE IS.GL_JE_BATCHES storage (buffer_pool keep);
alter INDEX IS.GL_JE_BATCHES_U2 storage (buffer_pool keep);
alter TABLE IS.GL_JE_HEADERS storage (buffer_pool keep);
当您已经识别出要分配到 KEEP 池的段时，需要调整 db_keep_cache_size 参数，以确保具有足够的块将所有分配到池中的段进行完全高速缓存。
当然，自动化方法有很多例外情况。例如，这些脚本不处理表分区和其他对象类型。它们只应该用作 KEEP 池高速缓存策略的框架，而不应该就这样运行。
数据库自调整的前景
尽管有许多新的 Oracle Database 10g 特性使自动调整变得更容易，但最直接相关的特性是新的 dbms_advisor PL/SQL 包和 Java pool advisory 实用程序：
Java Pool Advisory ― 该实用程序帮助预测 java_pool_size 内存区域添加或删除 RAM 帧的益处。
SQLAccess Advisor ― 这是新的 dbms_advisor 程序包的一个新的令人激动的 Oracle Database 10g 特性，它使您能获得关于创建、维护和使用索引及物化视图的建议。
tune_mview advisor 实用程序 ― 这是 Oracle Database 10g dbms_advisor 程序包的另一个新组件，使您能获得关于何种物化视图可用于超级管理 SQL 查询的专家建议。
记住，只要 Oracle 提供了功能强大的工具，如 alter system 语句，您就应该利用它们来自动执行常规的 DBA 任务。这样将为您节省更多时间来钻研 Oracle 数据库管理中更复杂的领域。
Donald K. Burleson [don@burleson.cc] 是世界上最优秀的 Oracle 数据库专家之一。他已经撰写了 19 本书籍，在全国性杂志上发表了 100 多篇文章，并担任 Oracle Internals（一本领先的 Oracle 数据库期刊）的主编。Burleson 最新的一本书是创建自调整的数据库，由 Rampant TechPress 出版。Don 的网站是 http://www.dba-oracle.com 和 http://www.remote-dba.net/。
表 1RAM 区域压力过大的情况过量分配的情况共享池库高速缓存遗漏无遗漏数据缓冲区高速缓存命中率 < 90%命中率 > 95%PGA 集合高度的多步执行100% 最优化执行
