Oracle 9i数据库的动态SGA特性探索

   来源：赛迪网技术社区    作者：yuanjin

Oracle9i数据库在内部特性方面有着非常大的增强，其中一个最令Oracle DBA兴奋的莫过于可以动态设置全部的Oracle SGA控制参数。与8i不同的是，原来都将初始化参数放到一个文本文件中，并且在数据库启动的时候读取，Oracle9i却可以通过ALTER DATABASE和ALTER SYSTEM命令复位全部的Oracle参数。

在9i前，如果想对Oracle数据库的处理模式作一些改变的话，Oracle管理员必须关闭数据库并且重新设置INIT.ORA文件中的参数，然后重新启动数据库。对于白天使用OLTP模式运作，晚上切换到数据仓库模式的Oracle数据库来说，这种重新设置是经常做的。

对于需要停止和重新启动Oracle数据库来修改参数来说，Oracle9i在这方面有明显的加强，它令实现数据库连续可用的目标变得更加简单。

这种可以在Oracle SGA中动态增加和缩小不同区域的能力为Oracle数据库管理员提供了一些令人激动的新特性。SGA每个区域的数据库活动都可以独立地被监视，而且也可以在Oracle数据库中，根据使用的模式分配和取回资源。

我们首先来看以下Oracle9i数据库和Oracle8i数据库的一些区别。Oracle9i的一个最重要的加强是对于连接到Oracle数据库的全部专用连接，都无需要拥有一个独立的PGA空间。在Oracle8i中，对于专用的Oracle连接，我们都需要在内存中分配一个独立的区域，称为Program Global Area或者PGA。PGA空间中包含有SORT_AREA_SIZE和额外的RAM控制结构以用来维护连接任务的状态。在Oracle9i中，PGA空间已经被Oracle SGA中的一个新内存空间代替，它是通过PGA_AGGREGATE_TARGET参数来设置的（见图1）。

　　　

图一

由于全部的内存使用都在Oracle SGA中分配，所以Oracle数据库管理员可以将分配给Oracle服务器的内存加大，可以分配至直到Orace服务器全部内存的80%。Oracle建议将服务器其余的20%内存保留给操作系统的任务。

当用户连接到Oracle9i数据库时，排序工作所需要的内存将会在Oracle9i的PGA_AGGREGATE_TARGET区域中分配。这可以令Oracle9i比Oracle8i跑得更快，这是由于内存只在需要的期间才分配，并且在完成后就可以马上释放给其它连接的Oracle任务使用。

动态修改SGA区域

由于Oracle管理员现在可以增加和减少SGA的全部区域，因此我们可以快速地查看一下SGA区域是怎样的，这样我们就可以知道Oracle DBA如何监视这些区域的使用并且为Oracle数据库更有效地重新分配内存。SGA的区域可以分为以下的部分。

数据缓冲（Data buffers）--Oracle9i拥有多达7个独立的数据缓冲来保存磁盘送来的数据块。这些包含有传统的KEEP pool，RECYCLE pool和DEFAULT pool，还有为每个Oracle数据库支持的块大小(2KB, 4KB, 8KB, 16KB和32KB) 而建立的独立数据缓冲池（见图2）。

　　　

图2 独立的Oracle9i数据缓冲

我们可以监视这7个数据缓冲区域的命中率，如果缓冲的命中率保持在百分之九十以上，我们可以减少分配给这些数据缓冲的内存，并且将它们重新分配给其它Oracle实例中需要额外内存的地方。

当数据缓冲的命中率（DBHR）下降时，我们可以将内存由一个数据缓冲中分离出来，并且将它重新分配给其它的数据缓冲（如图3所示）。

　　

图3 在Oracle9i的数据缓冲之间重新分配内存

共享池（Shared pool）--Oracle9i的共享池有一个很重要的作用是分析和执行Oracle SQL语句。低的library cache命中率表示分配给library cache的内存不足，当shared pool需要对SQL语句进行大量的分析和执行时，Oracle9i的数据库管理员可以使用ALTER SYSTEM来为shared pool加入额外的内存。

PGA区域--分配给PGA_AGGREGATE_TARGET的内存是用来让Oracle连接维护与连接相关的信息（例如游标的状态），并且对SQL的结果集进行排序。

Log buffer--对于Oracle redo log缓冲是否有大量活动，我们可以在log switch（日志转换）的频率上看出来。Oracle管理员可以监视redo log区域的活动，并且在Oracle数据库需要额外的内存为原始的缓冲区域服务时，动态地增加内存。

现在就让我们来仔细看以下这些内存区域之间是如何作用的。

 

改变PGA的内存分配

当以下的其中一个条件是真时，我们将需要动态地修改PGA_AGGREGATE_TARGET参数。

当V$SYSSTAT中对"estimated PGA memory for one-pass" 的统计值超出PGA_AGGREGATE_TARGET时，我们就需要增加PGA_AGGREGATE_TARGET的值。

当V$SYSSTAT中对"workarea executions - multipass" 的统计值超过百分之一时，数据库将会由更多的内存中得到好处。你可能过高地估计了PGA内存的空间，当V$SYSSTAT中"workarea executions - optimal"的值一直是100%时，可以考虑减少PGA_AGGREGATE_TARGET的值。

我们可以通过一个简单的脚本来查看shared pool是否需要更多的内存。

量度Library Cache的丢失率:

　　set lines 80;
　　set pages 999;
　　
　　column mydate heading 'Yr. Mo Dy Hr.' format a16
　　column c1 heading "execs" format 9,999,999
　　column c2 heading "Cache Misses|While Executing" format 9,999,999
　　column c3 heading "Library Cache|Miss Ratio" format 999.99999
　　
　　break on mydate skip 2;
　　
　　select 
　　to_char(snap_time,'yyyy-mm-dd HH24') mydate,
　　sum(new.pins-old.pins) c1,
　　sum(new.reloads-old.reloads) c2,
　　sum(new.reloads-old.reloads)/
　　sum(new.pins-old.pins) library_cache_miss_ratio
　　from 
　　stats$librarycache old,
　　stats$librarycache new,
　　stats$snapshot sn
　　where
　　new.snap_id = sn.snap_id
　　and
　　old.snap_id = new.snap_id-1
　　and
　　old.namespace = new.namespace
　　group by
　　to_char(snap_time,'yyyy-mm-dd HH24')
　　;
　　Cache Misses
　　Yr. Mo Dy Hr. execs While Executing LIBRARY_CACHE_MISS_RATIO
　　---------------- ---------- --------------- -----------------
　　2001-12-11 10 10,338 3 .00029
　　2001-12-12 10 182,477 134 .00073
　　2001-12-14 10 190,707 202 .00106
　　2001-12-16 10 2,803 11 .00392

　　

由上面的例子看到，在每天的9：00AM到10：AM之间，shared pool明显缺少内存。我们就可以在这段期间动态地重新设置shared_pool参数以由db_cache_size中分配额外的内存。

SGA的阀值摘要

由下面的表一可以看到，在监视SGA的内存使用时，有几个明显的阀值可以利用。我们可以写一些脚本并在其中集成一些智能，这样就可以在处理的需求变化时重新设置SGA。

　　RAM Area Too-small Condition Too-Large Condition 
　　Shared pool Library cache misses No misses 
　　Data buffer cache Hit ratio < 90% Hit ratio > 95% 
　　PGA aggregate high multi-pass executions 100% optimal executions　

　

查看SGA内存区域的负载

Oracle9i使用了一些新的内部视图或者在现有的视图中加入新的列来帮助查看Oracle9i中的内部内存分配情况。以下的新V$视图可以帮助监视Oracle9i连接的内存使用。

V$PROCESS -在Oracle9i中加入了三个新的列以监视PGA内存的使用，新列的名字是pga_used_mem, pga_alloc_mem 和pga_max_mem。

V$SYSSTAT -加入了很多新的统计行，包括有area statistics for optimal, one-pass和multi-pass。

V$PGASTAT -该新视图展示了全部后台进程和专用连接的PGA内存使用。

V$SQL_PLAN--这个新视图包含了全部当前执行的SQL的执行计划信息。对于需要最优化的SQL语句的性能调整专家来说这是非常吸引人的。

V$WORKAREA -这个新视图提供了Oracle9i连接的累积内存统计的详细信息。

V$WORKAREA_ACTIVE - 这个新的视图提供了当前全部正在执行的SQL语句的内部内存使用信息。

它们的目的是通过这些V$视图来监视SGA中的内存使用，然后根据Oracle实例的处理要求，通过ALTER SYSTEM命令重新分配内存。我们以下来看一些这些新的Oracle9i特性和脚本，它可以帮助我们查看详细的内存使用。

当然我们不可能在这里详述所有的技术，以下就让我们看一个简单的例子，它通过使用V$SYSSTAT视图来决定何时重新设置PGA_AGGREGATE_TARGET参数。

以下的查询可以得到自数据库实例启动后work areas被执行的全部数目和百分比。

　　work_area.sql
　　
　　select
　　name profile, 
　　cnt, 
　　decode(total, 0, 0, round(cnt*100/total)) percentage
　　from 
　　(
　　select 
　　name, 
　　value cnt, 
　　(sum(value) over ()) total
　　from
　　v$sysstat 
　　where
　　name like 'workarea exec%'
　　);

　　

这个查询的输出可能如下：

　　PROFILE CNT PERCENTAGE
　　----------------------------------- ---------- ----------
　　workarea executions - optimal 5395 95
　　workarea executions - onepass 284 5
　　workarea executions - multipass 0 0

　　

这个查询的输出是用来告诉DBA何时动态调整PGA_AGGREGATE_TARGET参数。在通常的情况下，如果multi-pass的执行大于0，就需要增加PGA_AGGREGATE_TARGET的值，并且在optimal executions是100%时减少它的值。