Oracle9i动态SGA,PGA特性探索

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 管理员根据系统的变化来灵活地重新设置他们的系统。