Oracle9i 动态SGA,PGA特性探索

原作者: [db:作者] 来自: [db:来源] 收藏邀请

“In the latest release 10.2 Oracle changed these default values. The memory allocated to a single SQL operator (_smm_max_size) is limited to:
for P_A_T <= 500MB the parameter _smm_max_size = 20% of P_A_T
for P_A_T between 500MB and 1000MB the parameter _smm_max_size = 100M
for P_A_T betweeen 1001MB and 2560MB (2.5GB) the parameter _smm_max_size = 10% of P_A_T
I have seen even cases when these values were even bigger after the instance was restarted with P_A_T set at 4GB.
The maximum value for parallel operations changed from 30% to 50% PGA_AGGREGATE_TARGET/DOP. Also the DOP changed. When DOP <=5 then _smm_max_size is used, otherwise _smm_px_max_size/DOP limits the maximum memory usage. . .
转载:http://www.1to2.us/ORACLE-a117737.htm
oracle中SGA的设置
关键词： sga
关于SGA设置的一点总结
本总结不针对特例，仅对服务器只存在OS + ORACLE 为例，如果存在其他应用请酌情考虑
写这个也是因为近来这种重复性的问题发生的太多所导致的
首先不要迷信STS,SG,OCP,EXPERT 等给出的任何建议、内存百分比的说法
基本掌握的原则是， data buffer 通常可以尽可能的大，shared_pool_size 要适度，log_buffer 通常大到几百K到1M就差不多了
设置之前，首先要明确2个问题
1：除去OS和一些其他开销，能给ORACLE使用的内存有多大
2：oracle是64bit or 32 bit,32bit 通常 SGA有 1.7G 的限制(某些OS的处理或者WINDOWS上有特定设定可以支持到2G以上甚至达到3.7G，本人无这方面经验)
下面是我的windows2000下的oracle :
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
SQL>
windows上存在32bit的限制，如AIX、HP UNIX 等有明确的64BIT OS and ORACLE的版本，32bit oracle可以装在64bit os 上，64 bit oracle不能装在32 bit OS上
不管oracle是32 bit ORACLE还是 64 bit 的,假定应用存在没有很好的使用bind var 的情况，也不能设置 shared_pool_size 过大，通常应该控制在200M--300M,如果是 ORACLE ERP 一类的使用了很多存储过程函数、包，或者很大的系统，可以考虑增大shared_pool_size ,但是如果超过500M可能是危险的，达到1G可能会造成CPU的严重负担，系统甚至瘫痪。所以shared_pool_size 如果超过300M还命中率不高，那么应该从应用上找原因而不是一味的增加内存，shared_pool_size 过大主要增加了管理负担和latch 的开销。
log_buffer : 128K ---- 1M 之间通常问题不大，不应该太大
large_pool_size :如果不设置MTS，通常在 RMAN 、OPQ 会使用到，但是在10M --- 50M 应该差不多了。假如设置 MTS,则由于 UGA 放到large_pool_size 的缘故，这个时候依据 session最大数量和 sort_ares_size 等参数设置，必须增大large_pool_size 的设置，可以考虑为 session * (sort_area_size + 2M)。这里要提醒一点，不是必须使用MTS，我们都不主张使用MTS，尤其同时在线用户数小于500的情况下。
java_pool_size : 若不使用java，给30M通常就够了
data buffer ,在做了前面的设置后，凡可以提供给oracle的内存，都应该给data buffer = (db_block_size * db_block_buffers)
在9i 中可以是 db_cache_size
还有2个重要参数我们需要注意
sort_area_size and hash_area_size
这两个参数在非MTS下都是属于PGA ，不属于SGA,是为每个session单独分配的，在我们的服务器上除了OS + SGA,一定要考虑这两部分
(****) : OS 使用内存+ SGA + session*(sort_area_size + hash_area_size + 2M) < 总物理RAM 为好
这样归结过来，假定oracle是 32 bit ,服务器RAM大于2G ，注意你的PGA的情况，,则建议
shared_pool_size + data buffer +large_pool_size + java_pool_size < 1.6G
再具体化，注意满足上面(****) 的原则的基础上可以参考如下设置
如果512M RAM
建议 shared_pool_size = 50M, data buffer = 200M
如果1G RAM
shared_pool_size = 100M , data buffer = 500M
如果2G
shared_pool_size = 150M ,data buffer = 1.2G
物理内存再大已经跟参数没有关系了
假定64 bit ORACLE
内存4G
shared_pool_size = 200M , data buffer = 2.5G
内存8G
shared_pool_size = 300M , data buffer = 5G
内存 12G
shared_pool_size = 300M-----800M , data buffer = 8G
以上仅为参考值，不同系统可能差异比较大，需要根据具体情况调整。建议在设置参数的同时，init中使用 lock_sga ，在不同的平台上可能有不同的方式，使得SGA锁定在物理内存中而不被放入 SWAP 中，这样对效率有好处关于内存的设置，要再进行细致的调整，起的作用不大，但可根据statspack信息和v$system_event,v$sysstat,v$sesstat,v$latch 等view信息来考虑微调
pga设置
hash_area_size用于在内存中进行排序的区域
hash_value不会影响PGA，他是系统自动计算HASH运算，以定位内存地址。
增大hash_area_size,可以减少物理磁盘的排序，所以提高排序的速度。
9I管理PGA有两种方式，同时影响到hash_area_size
自动配置PGA
手动配置PGA
如果设置了auto，那么sort area, hash area自动分配大小，pga_aggregate_target参数被使用。
如果设置为 manual，那么参数sort area, hash area参数被使用，对于某些特别耗资源的可以指定manual，然后设置合理的sort area,hash area。
设置10104事件来判断hash_area_size是否要增大，如果Number of rows left to be
iterated over的值为非0，表示读了临时表，要两阶段不能在内存中一次完成
在Oracle9i之前，PGA的计算和控制都是比较复杂的事情，从Oracle9i开始，Oracle提供了一种SQL内存管理的新方法：自动化SQL执行内存管理（Automated SQL Execution Memory Management），使用这个新特性，Oracle可以自动调整S Q L内存区，而不用关闭数据库，这一改进大大简化了DBA的工作，同时也提高了Oracle数据库的性能。
为实现自动的PGA管理，Oracle引入了几个新的初始化参数：
1．PGA_AGGREGATE_TARGET-此参数用来指定所有session总计可以使用最大PGA内存。这个参数可以被动态的更改,取值范围从10M -- （4096G-1 ）bytes。
2。WORKAREA_SIZE_POLICY-此参数用于开关PGA内存自动管理功能，该参数有两个选项：AUTO 和 MANUAL，当设置为AUTO时，数据库使用Oracle9i提供的自动PGA管理功能，当设置为MANUAL时，则仍然使用Oracle9i前手工管理的方式。
缺省的，Oracle9i中WORKAREA_SIZE_POLICY被设置为AUTO。
需要注意的是，在Oracle9i中，PGA_AGGREGATE_TARGET参数仅对专用服务器模式下（Dedicated Server）的专属连接有效，但是对共享服务器（Shared Server）连接无效；从Oracle10g开始PGA_AGGREGATE_TARGET对专用服务器连接和共享服务器连接同时生效。
PGA_AGGREGATE_TARGET 参数同时限制全局PGA分配和私有工作区内存分配：
1．对于串行操作，单个SQL操作能够使用的PGA内存按照以下原则分配：
MIN（5% PGA_AGGREGATE_TARGET，100MB）
2．对于并行操作
30% PGA_AGGREGATE_TARGET /DOP （DOP=Degree Of Parallelism 并行度）
要理解PGA的自动调整，还需要区分可调整内存（TUNABLE MEMORY SIZE）与不可调整内存（UNTUNABLE MEMORY SIZE）。可调整内存是由SQL工作区使用的，其余部分是不可调整内存。
启用了自动PGA调整之后， Oracle仍然需要遵循以下原则：
UNTUNABLE MEMORY SIZE + TUNABLE MEMORY SIZE <= PGA_AGGREGATE_TARGET
数据库系统只能控制可调整部分的内存分配，如果可调整的部分过小，则Oracle永远也不会强制启用这个等式。
另外，PGA_AGGREGATE_TARGET参数在CBO优化器模式下，对于SQL的执行计划会产生影响。Oracle在评估执行计划时会根据PGA_AGGREGATE_TARGET参数评估在Sort，HASH-JOIN或Bitmap操作时能够使用的最大或最小内存，从而选择最优的执行计划。
对于PGA_AGGREGATE_TARGET参数的设置，Oracle提供这样一个建议方案
1．对于OLTP系统
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 20%
2．对于DSS系统
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 50%
也就是说，对于一个单纯的数据库服务器，通常我们需要保留20%的物理内存给操作系统使用，剩余80%可以分配给Oracle使用。Oracle使用的内存分为两部分SGA和PGA，那么PGA可以占用Oracle消耗总内存的20%（OLTP系统）至50%（DSS系统）。
这只是一个建议设置，更进一步的我们应该根据数据库的具体性能指标来调整和优化PGA的使用。
对于PGA_AGGREGATE_TARGET参数的设置，Oracle提供这样一个建议方案
1．对于OLTP系统
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 20%
2．对于DSS系统
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 50%
也就是说，对于一个单纯的数据库服务器，通常我们需要保留20%的物理内存给操作系统使用，剩余80%可以分配给Oracle使用。Oracle使用的内存分为两部分SGA和PGA，那么PGA可以占用Oracle消耗总内存的20%（OLTP系统）至50%（DSS系统）。
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参数来设置的
　　由于全部的内存使用都在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数据库支持的块大小(2K, 4K, 8K, 16K和32K) 而建立的独立数据缓冲池
　　我们可以监视这7个数据缓冲区域的命中率，如果缓冲的命中率保持在百分之九十以上，我们可以减少分配给这些数据缓冲的内存，并且将它们重新分配给其它Oracle实例中需要额外内存的地方。
　　当数据缓冲的命中率（DBHR）下降时，我们可以将内存由一个数据缓冲中分离出来，并且将它重新分配给其它的数据缓冲
　　共享池（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
　　表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 管理员根据系统的变化来灵活地重新设置他们的系统。