Oracle9i的动态SGA特性探索

改变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

　　表1：SGA中的异常条件指示

　　查看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%时减少它的值。

　　我们还可以使用V$PGASTAT视图来决定我们的Oracle实例的内存使用。V$PGASTAT视图提供了PGA使用和自动内存管理的实例级摘要统计信息。以下的脚本提供了全部Oracle9i连接的整体内存使用的统计信息。

　　以下是一个用来检测Oracle9i中PGA内存使用的简单脚本。

check_pga.sql column name format a30 column value format 999,999,999 select name, value from v$pgastat ; The output of this query might look like the following: 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

　　在上面的v$pgastat显示中我们可以看到以下的统计。

　　Aggregate PGA auto target -该列给出了可用于Oracle9i连接的全部内存。我们已经提过，这个值是由PGA_AGGREGATE_TARGET设置的。

　　Global memory bound -该统计表示work area的最大值，Oracle建议在该统计值下降到1M时，你应该增加PGA_AGGREGATE_TARGET的值。

　　Total PGA allocated - 这个统计显示了数据库中全部PGA内存使用的高水位线。当使用增加时，你应该看到这个值接近PGA_AGGREGATE_TARGET的值。

　　Total PGA used for auto workareas - 这个统计监视内存的使用或者全部运行在自动内存模式中的全部连接。要记住的是，并不是全部的内部进程使用自动内存特性。例如，Java和PL/SQL 将分配内存，但是这部分将不会统计到这个值中。因此我们可使用整体PGA的值来减去该值，以得到连接和Java and PL/SQL使用的内存。

　　Estimated PGA memory for optimal/one-pass - 该统计估计optimal模式下执行全部的连接任务所需要的内存。要记住的是，如果Oracle9i遇到内存不足时，它就会调用multi-pass操作。这个统计对于监视Oracle9i中的内存使用是非常重要的，大多数的Oracle DBA将会增加PGA_AGGREGATE_TARGET到这个值。

　　现在我们已经了解了这个概念，以下就让我们来看一下自动重新配置SGA有哪些方法。

　　总述

　　在一个UNIX环境中，在处理需求改变时通过定时任务来修改内存配置是非常简单的。例如，许多Oracle数据库在一般的工作时间以OLTP模式运作，在晚上的时候则运行对内存需求很大的批量报告。

　　我们知道在一个OLTP数据库中应该将DB_CACHE_SIZE设置为一个较大的值，而在需求内存很大的批量任务中则需要给PGA_AGGREGATE_TARGET分配额外的内存。

　　以下的UNIX脚本可以用来重新设置OLTP和DSS的SGA值而无需将实例停下来。在这个例子中，我们假定有一个孤立的带有8GB内存的的Oracle服务器。我们还假定保留20%的内存供UNIX使用，而剩下的6GB内存则用作Oracle和Oracle连接。这些脚本是在HP/UX或者Solaris中使用的，并且接受$ORACLE_SID作为一个参数。

　　DSS_CONFIG.KSH脚本将在每晚的6：00 p.m运行，以重新设置Oracle在晚上运行对内存需求很大的批量任务。

dss_config.ksh #!/bin/ksh # First, we must set the environment . . . . ORACLE_SID=$1 export ORACLE_SID ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'` #ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'` export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH $ORACLE_HOME/bin/sqlplus -s /nologin<connect system/manager as sysdba; ALTER SYSTEM set db_cache_size=1500m; ALTER SYSTEM set shared_pool_size=500m; ALTER SYSTEM set pga_aggregate_target=400m; exit !

　　现在我们已经知道了一个常见的方式来修改Oracle的配置，我们也很容易地看到可以很简单地开发一个技术来连续地监控Oracle的处理需求，并且根据现有的数据库需求来使用ALTER SYSTEM作修改。

　　结论

　　虽然Oracle9i中的内存管理仍然需要很多的手工操作，不过大部分的Oracle管理员可以使用工具来连续地监控Oracle SGA中的内存使用，并且可以根据Oracle instance中现在的使用情况来自动地重新分配内存。这样就可以令Oracle 管理员根据系统的变化来灵活地重新设置他们的系统。

查看本文来源