随着数据库管理员在自调整工作方面变得更加成熟许多 Oracle 规格可能变为自调整在 Oracle Database g 中我们将看到比以前更多的自调整功能
例如Oracle Database g 的动态内存分配特性使得创建一个自调整的 Oracle SGA 成为可能通过演示在本文中我将说明如何检查 Oracle i Database 中的 Oracle 例程以及根据服务器上和数据库内的处理需求来调整 sort_area_size 或 pga_aggregate_targetlarge_pool_sizesga_max_size 和 db_cache_size 的内存区域这里讨论的技巧的基础是使用 Statspack 来随时监控内存区域并显示系统资源利用率的信号图
我还将讨论如何创建一种智能机制以根据当前的处理需求来自动地重新配置 Oraclei Database并提供了示例代码这些示例代码将使您能够开始编写自己的能够有效地仿效 Oracle Database g 功能的自动化脚本例如我将提供一个脚本它将自动识别小型常用的程序段并将它们分配给 KEEP 池以全部进行缓存（重要注意事项这种仿效仅考虑外部的行为但不反映新版本的内部实施）因为每个数据库都各不相同所以为了清楚起见这些脚本特意进行了省略和简化因此您将需要扩展这些示例并编写适合您的环境的自定义的自动化脚本
具有以下特性的商店将从自动化的自调整中最大程度的获益
双模式系统 — 在在线事务处理 (OLTP) 和数据仓库处理模式之间转换的系统尤其将从自调整 RAM 区域中获益
位的商店 — 运行 位服务器的商店受其 RAM 区域大小（最大约为 GB）的限制对于这些商店最有效地使用 RAM 资源尤为重要
记住拥有一个非常大的 db_cache_size 的趋势正在下降也很重要虽然对数据的直接访问是利用散列法来完成的但有时数据库必须检查 RAM 缓存中的所有内存块
高失效率的系统 — 无论何时当程序产生一个截断表使用临时表或运行一次大型的数据清除时Oracle 必须清除 db_cache_size 中的所有内存块以除去已被使用的内存块对于拥有大于 gB 的 db_cache_size 的系统这种方法可能造成过多的开销
更新率高的系统 — 当执行一次异步写操作时数据库写入器 (DBWR) 过程必须清除 db_cache_size 中的所有内存块拥有一个巨大的 db_cache_size 可能给数据库写入器造成过重的负担
首先让我们回顾一下创建自调整数据库背后的准则
自调整背后的准则
重新配置一个 Oracle 例程的最常用的技巧是使用一个脚本并通过 dbms_job 或一个外部调度程序（如 UNIX cron）来调用这个脚本为了说明一个简单的例子考虑一个白天在 OLTP 模式下运行而晚上在数据仓库模式下运行的数据库对于这种类型的数据库您可以安排一项作业来将例程 SGA 内存重新配置成最适合于在该 Oracle 例程中执行的处理类型的配置
列表 包含一个 UNIX 脚本该脚本用来重新配置 Oracle以便进行决策支持处理注意为了适应数据仓库行为对 shared_pooldb_cache_size 和 pga_aggregate_target 中的配置作了重要的修改该脚本被安排在每晚 :pm 通过 dbms_job 来调用
在列表 中我们看到了建立了自调整 Oracle Database g 的基础的 alter system 命令记住 RAM 是一种昂贵的 Oracle 服务器资源DBA 有责任在服务器上充分地分配 RAM 资源未得到利用的 RAM 将浪费昂贵的硬件资源
即使得到了充分的分配分配过度的 RAM 也是一种浪费例如当您只需要 m 时分配一个 shared_pool_size=m 是效率低下的因为 RAM 可以被 SGA 的另一个区域（如 db_cache_size）使用
为了说明 RAM 重新配置的概念考虑下面这个例子一个分配不足且数据缓沖命中率很低的 K 数据缓沖区和一个分配过度且数据缓沖命中率很高的 K 数据缓沖区（参见图 ）
图 分配过度和分配不足的 RAM 区域
使用 alter system 命令我们可以在数据缓沖区之间调整 RAM以将 RAM 重新分配给最需要的地方（参见图 ）
图 RAM 的动态重分配
您可以在很多种 Oracle 脚本（包括动态 SQLdbms_job 和 shell 脚本）中使用 alter system 命令列表 是调整 RAM 缓存大小的一个简单的 SQL*Plus 脚本这个脚本向您提示缓存的名称和大小并发出适当的 alter system 命令来调整 RAM 区域的大小下面是输出的内容
SQL> @dyn_sga
Enter cache to decrease:shared_pool_size
Enter cache to increase:db_cache_size
Enter amount to change:
alter system set shared_pool_size = ;
System altered
alter system set db_cache_size = ;
System altered
现在我们看到了在 Oracle Database g 中如何轻易地改变 RAM 区域下面让我们研究一下调用 RAM 区域自动调整的一些规则
何时触发动态重配置
无论何时当监控脚本的例程指示有一个负担过重的 RAM 区域时您必须选择从哪一个区域中窃取内存表 显示了阈值条件的一个简单的示例该阈值条件触发 SGA 的三个主要区域的动态内存修改当然每个系统都各不相同您将要根据您的需求来调整这些阈值例如许多商店实施了多个 blocksize并分离了 db_k_cache_size （用于索引表空间）db_keep_cache_size（用于小型引用频繁的对象）等的 RAM 区域
记住数据库的需求将根据正在执行的 SQL 不断变化是很重要的在 :am 最优的一个 SGA 可能在 :pm 就不是最优了为了了解处理特性的变化您可以运行 Statspack 报表来查明 Oracle 改变 RAM 存储需求的那些时间您还可以运行 v$db_cache_advicev$pga_target_advicev$java_pool_advice 和 v$db_shared_pool_advice 实用程序来查看 RAM 区域大小的变化带来的边际效益
一种使动态 SGA 重新配置自动化的流行的方法是识别趋势您可以使用 Statspack 来预测那些处理特性变化的时间并使用 dbms_job 程序包或动态 SQL 来执行特定的 SGA 修改让我们详细了解一下基于趋势的方法
显示系统信号图
基于趋势的重新配置的一种常见的方法是使用 Statspack 历史数据来显示可预测的趋势并根据信号图用这些趋势来修改数据库
这种方法与零库存生产很相似其中零部件正好在需要组装时才出现在工厂车间里Oracle Database g 使 DBA 能够预见处理需求并定期地安排适当的干预操作从而确保为处理需求的变化即时地提供 SGA 资源
自调整 Oracle 的内存区域涉及到改变几个 Oracle 参数的值虽然存在 多个 Oracle Database g 参数来管理数据库的各方面配置但只有少数几个参数对自动的 Oracle SGA 调整很重要
db_cache_size — db_cache_size 确定 Oracle SGA 中的数据库块缓沖的数量并且代表着 Oracle 内存最重要的一个参数
db_keep_cache_size — 这个数据缓沖池是 Oraclei 中 db_block_buffers 的一个子缓沖池但从 Oraclei Database 开始成为一个单独的 RAM 区域
db_nn_cache_size — Oracle Database g 有单独的数据缓沖池您可以使用这些数据缓沖池来分离数据并分离具有不同 I/O 特性的对象
shared_pool_size — shared_pool_size 定义系统中由所有用户共享的池包括 SQL 区域和数据字典缓存
pga_aggregate_target — pga_aggregate_target 定义为系统范围的排序和散列连接保留的 RAM 区域
您可以看到甚至不需要对您的 Oracle 数据库状况的最重要的量度进行归零校正让我们从检查库缓存中的趋势开始并确定如何自动调整 shared_pool_size
使用 Oracle Database g 顾问实用程序
Oracle Database g 拥有完整的顾问实用程序它们将准确地预测改变任意的 RAM 区域大小将带来的变化Oracle Database g 中的顾问实用程序包括
共享池建议 — v$shared_pool_advice
PGA 目标建议 — v$pga_target_advice
数据缓存建议 — v$db_cache_advice
Java 池建议 — v$java_pool_advice
这些实用程序是确保自调整变化正确合理的一种极好的方式以下内容将显示如何调用和解释这些顾问实用程序当您能够轻松地解释它们的输出时您就可以编写自动化的脚本来生成建议解释输出并自动改变 RAM 区域的大小
共享池建议实用程序
这一顾问功能在 Oraclei Database Release 中得到了扩展包含了一个称为 v$shared_pool_advice 的新的建议实用程序在将来的版本中它可能最终将被扩展至所有的 SGA RAM
从 Oraclei Database Release 开始当共享池的大小从当前值的 % 变为当前值的 % 时v$shared_pool_advice 视图将显示 SQL 分析的边际差异
共享池建议实用程序非常易于配置安装后您可以运行一个简单的脚本来查询 v$shared_pool_advice 视图并查看不同 shared_pool 大小的 SQL 分析的边际变化以下脚本的输出将告诉您动态增加或减少 shared_pool_size 参数带来的变化
************************************************
Display shared pool advice
************************************************
set lines
set pages
column cheading Pool |Size(M)
column cheading Size|Factor
column cheading Est|LC(M)
column cheading Est LC|Mem Obj
column cheading Est|Time|Saved|(sec)
column cheading Est|Parse|Saved|Factor
columncheading Est|Object Hits format
SELECT
shared_pool_size_for_estimatec
shared_pool_size_factorc
estd_lc_sizec
estd_lc_memory_objectsc
estd_lc_time_savedc
estd_lc_time_saved_factorc
estd_lc_memory_object_hitsc
FROM
v$shared_pool_advice;
Est Est
Time Parse
Pool Size Est Est LC Saved Saved Est
Size(M) Factor LC(M) Mem Obj (sec) Factor Object Hits
下面我们看一下共享池在当前大小的 % 到当前大小的 % 的变化范围内的统计数字这些统计数字可以使您很好地了解 shared_pool_size 的真正大小如果您使用自动化的 alter system 命令来自动调整 SGA 区域的大小那么创建这种输出并编写一个程序来解释结果是确保共享池和库缓存始终有足够的 RAM 的一种极好的方式接下来让我们看看我们如何能够随时跟蹤共享池的使用并显示信号图从而允许我们预测那些需要共享池调整的时间
我们可以使用 Statspack 来创建一个列表来显示那些库缓存丢失率低于指定水平的时间如列表 中所示以下输出显示在每天上午 : 到 : 之间共享池重复出现 RAM 不足
Cache Misses Library Cache
Yr Mo Dy Hr execs While Executing Miss Ratio
在这个例子中DBA 需要在内存不足的时间段内为 shared_pool_size 安排额外的 RAM
显示 pga_aggregate_target 的信号图
Oracle Database g 中的 PGA 区域非常重要因为它控制排序操作和 SQL 散列连接的速度当以下条件的任何一个为真时您可能需要动态地修改 pga_aggregate_target 参数
无论何时当 v$sysstat 统计的用于一次通过的估计 PGA 内存的值超过 pga_aggregate_target 时您需要增加 pga_aggregate_target
无论何时当 v$sysstat 统计的工作区执行 — 多次通过的值大于百分之一时数据库就可以从额外的 RAM 内存中获益
有可能 PGA 内存分配过度无论何时当 v$sysstat 行工作区执行 — 最优的值始终测量为百分之百时您可以考虑减少 pga_aggregate_target 的值
v$pgastat 视图提供了例程级的 PGA 使用率汇总统计和自动的内存管理器要获得一个快速的概览一次简单的查询可以为所有 Oracle Database g 连接提供一个极好的总体 PGA 使用统计表
check_pgasql
*************************************************************
Display detailed PGA statistics
*************************************************************
column name format a
column value format
select
name
value
from
v$pgastat
;
该查询的输出可能如下所示
NAME VALUE
aggregate PGA auto target
global memory bound
total expected memory
total PGA inuse
total PGA allocated
maximum PGA allocated
total PGA used for auto workareas
maximum PGA used for auto workareas
total PGA used for manual workareas
maximum PGA used for manual workareas
estimated PGA memory for optimal
maximum PGA memory for optimal
estimated PGA memory for onepass
maximum PGA memory for onepass
在之前 v$pgastat 的显示中我们看到了下面这些重要的统计量
用于自动工作区的全部 PGA — 这个统计量监控运行在自动内存模式下的所有连接的 RAM 使用记住不是所有的内部过程都被 Oracle 允许使用自动内存特性例如Java 和 PL/SQL 将分配 RAM 内存而它不会计入总体的 PGA 统计因此您应当从分配的总体 PGA 中减去这个值以查看连接使用的内存和 Java 及 PL/SQL 使用的 RAM 内存
用于最优/一次通过的估计 PGA 内存 — 这个统计量估计在最优模式下执行所有的任务连接 RAM 需求需要多少内存记住当 Oracle Database g 遇到内存不足时DBA 将调用多次通过操作来试图找到当前释放的 RAM 内存这个统计量对于监控 Oracle Database g 中的 RAM 消费至关重要大多数 Oracle DBA 将把 pga_aggregate_target 增加到这个值
在 Oracle Database g 中您可以使用名称为 v$pga_target_advice 的新的顾问实用程序这个实用程序将显示不同大小的 pga_aggregate_target （范围从当前值的 % 到 %）在最优一次通过和多次通过 PGA 执行中的边际变化
列表 显示了使用这个新的实用程序的一个示例查询下面是一个输出示例这里我们看到对于当前的处理我们已经为 pga_aggregate_target 分配了过多的内存从这个区域取出内存并将其分配到其它地方是安全的
Estimated Estimated
Target(M) Cache Hit % OverAlloc
<= current size
您可以看到您可以容易地创建自动化的方法来检测 PGA 内存不足（利用 Statspack）并编写作业来动态改变 pga_aggregate_target以便为排序和散列连接确保最优的 RAM 使用率
