Oracle SGA 自动管理特性(sga_target参数)

    最近有网友对Oracle SGA内存自动管理特性不是很清楚，可能是由于当时翻译的 Oracle 10g SGA 的自动化管理 比较生涩，下面依旧是针对这个问题给出在Oracle 10g环境中描述，并给出示例以便更好的理解。

 

1、相关参数描述
a、参数SHARED_POOL_SIZE
SHARED_POOL_SIZE = integer [K | M | G]

Default value
   If SGA_TARGET is set: If the parameter is not specified, then the default is 0 (internally determined by the Oracle Database).
   If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.
   If SGA_TARGET is not set (32-bit platforms): 32 M, rounded up to the nearest granule size.
   If SGA_TARGET is not set (64-bit platforms): 84 M, rounded up to the nearest granule size.
Range of values Minimum: 
   the granule size
Maximum:
   operating system-dependent
 
b、参数SGA_TARGET
SGA_TARGET = integer [K | M | G]
Default value 0 (SGA autotuning is disabled)

SGA_TARGET specifies the total size of all SGA components.
If SGA_TARGET is specified, then the following memory pools are automatically sized:
    Buffer cache (DB_CACHE_SIZE)
    Shared pool (SHARED_POOL_SIZE)
    Large pool (LARGE_POOL_SIZE)
    Java pool (JAVA_POOL_SIZE)
    Streams pool (STREAMS_POOL_SIZE)

If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic
Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:
  Log buffer
  Other buffer caches, such as KEEP, RECYCLE, and other block sizes
  Fixed SGA and other internal allocations

The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management
computes the values of the automatically tuned memory pools.

 

2、参数sga_target为零值的情形

--#编辑一个临时的参数文件,并设置sga_target=0,以及设定几个pool池的size,db_cache_size,如下robin@SZDB:/u02/database/SYBO2SZ> grep size SYBO2SZ.ora.tmp   *.db_block_size=8192*.db_cache_size=285212672*.db_recovery_file_dest_size=1G*.java_pool_size=4194304*.large_pool_size=4194304*.shared_pool_size=293601280*.streams_pool_size=4194304robin@SZDB:/u02/database/SYBO2SZ> grep target SYBO2SZ.ora.tmp*.pga_aggregate_target=199229440*.sga_target=0--#使用临时的参数文件启动数据库robin@SZDB:/u02/database/SYBO2SZ> sqlplus / as sysdbaidle> startup pfile=/u02/database/SYBO2SZ/SYBO2SZ.ora.tmpORACLE instance started.---可以看到此时sga_target为0idle> show parameter sga_tarNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------sga_target                           big integer 0-->查看此时内存分配的情况idle> SELECT name, VALUE  2    FROM v$parameter  3   WHERE name IN  4            ('shared_pool_size',  5             'java_pool_size',  6             'streams_pool_size',  7             'log_buffer',  8             'db_cache_size',  9             'db_2k_cache_size', 10             'db_4k_cache_size', 11             'db_8k_cache_size', 12             'db_16k_cache_size', 13             'db_32k_cache_size', 14             'db_keep_cache_size', 15             'db_recycle_cache_size', 16             'large_pool_size');NAME                           VALUE------------------------------ --------------------shared_pool_size               293601280large_pool_size                4194304java_pool_size                 4194304streams_pool_size              4194304db_cache_size                  285212672db_2k_cache_size               0db_4k_cache_size               0db_8k_cache_size               0db_16k_cache_size              0db_32k_cache_size              0db_keep_cache_size             0db_recycle_cache_size          0log_buffer                     612044813 rows selected.-- Author ：Robinson-- Blog   ：http://blog.csdn.net/robinson_0612--使用临时的pfile来创建spfileidle>  create spfile from pfile='/u02/database/SYBO2SZ/SYBO2SZ.ora.tmp';File created.

3、参数sga_target非零值的情形

--重启dbidle> startup force;ORACLE instance started.idle> select distinct isspecified from v$spparameter;ISSPEC------TRUE    -->为true表名此时使用了spfile启动数据库FALSE--此时sga_max的值为572midle> show parameter sga_maxNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------sga_max_size                         big integer 572M--修改sga_target到非零值idle> alter system set sga_target=572m;      System altered.--此时sga_target变成了576m,这个是由于分配粒度和当前已分配内存之和造成的，自动向上取整idle> show parameter sga_targetNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------sga_target                           big integer 576M--参数文件指定的值分配的总大小为564midle> select (285212672+4194304+4194304+293601280+4194304)/1024/1024 from dual;(285212672+4194304+4194304+293601280+4194304)/1024/1024-------------------------------------------------------                                                    564                                                    --来看一下内存的分配粒度，此时为4mbidle> @mem_granuleNAME                                VALUE                          DESCBTION----------------------------------- ------------------------------ ----------------------------_ksmg_granule_size                  4194304                        granule size in bytes_ksmg_granule_locking_status        1                              granule locking status--值572m也是4的整数倍，这个应该是有一部分固定或其他方面内存耗用所致当前被分配了576m--如果参数文件中总大小缩小的话，我们可以指定sga_target<=sga_max_sizeidle> select 576/4 from dual;     576/4----------       144       --从spfile生成pfile来观察sga_target非零值发生了什么变化idle> create pfile='/tmp/tmp.ora' from spfile;File created.--可以看到增加了以dbname开头的且带下划线的和sga_target内存分配相关的项idle> ho grep size /tmp/tmp.oraSYBO2SZ.__db_cache_size=285212672SYBO2SZ.__java_pool_size=4194304SYBO2SZ.__large_pool_size=4194304SYBO2SZ.__shared_pool_size=297795584SYBO2SZ.__streams_pool_size=4194304*.db_block_size=8192*.db_cache_size=285212672*.db_recovery_file_dest_size=1G*.java_pool_size=4194304*.large_pool_size=4194304*.shared_pool_size=293601280*.streams_pool_size=4194304--/tmp/tmp.ora文件中的sga_target变成了603979776=576midle> ho grep sga_target /tmp/tmp.ora*.sga_target=603979776--下面编辑/tmp/tmp.ora，移除原来的有关内存的配值，用带下划线的值来启动数据库idle> vi /tmp/tmp.oraidle> ho grep size /tmp/tmp.oraSYBO2SZ.__db_cache_size=285212672SYBO2SZ.__java_pool_size=4194304SYBO2SZ.__large_pool_size=4194304SYBO2SZ.__shared_pool_size=297795584SYBO2SZ.__streams_pool_size=4194304*.db_block_size=8192#*.db_cache_size=285212672*.db_recovery_file_dest_size=1G#*.java_pool_size=4194304#*.large_pool_size=4194304#*.shared_pool_size=293601280#*.streams_pool_size=4194304--首先关闭数据库idle> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.--使用临时的pfile启动idle> startup pfile=/tmp/tmp.oraORACLE instance started.--下面的查询可以看到所有和sga_target相关的内存分配全部变成了0idle> SELECT name, VALUE  2    FROM v$parameter  3   WHERE name IN  4            ('shared_pool_size',  5             'java_pool_size',  6             'streams_pool_size',  7             'log_buffer',  8             'db_cache_size',  9             'db_2k_cache_size', 10             'db_4k_cache_size', 11             'db_8k_cache_size', 12             'db_16k_cache_size', 13             'db_32k_cache_size', 14             'db_keep_cache_size', 15             'db_recycle_cache_size', 16             'large_pool_size');NAME                                VALUE----------------------------------- ------------------------------shared_pool_size                    0large_pool_size                     0java_pool_size                      0streams_pool_size                   0db_cache_size                       0db_2k_cache_size                    0db_4k_cache_size                    0db_8k_cache_size                    0db_16k_cache_size                   0db_32k_cache_size                   0db_keep_cache_size                  0db_recycle_cache_size               0log_buffer                          612044813 rows selected.--此时sga_max_size也变成了576m,这是因为sga_target必须小于等于sga_max_sizeidle> show parameter sga_maxNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------sga_max_size                         big integer 576M

4、小结
a、当设置sga_target为非零值时下列参数受到sga_targe的控制，但其总值不超过sga_target。
   Buffer cache (DB_CACHE_SIZE)
   Shared pool (SHARED_POOL_SIZE)
   Large pool (LARGE_POOL_SIZE)
   Java pool (JAVA_POOL_SIZE)
   Streams pool (STREAMS_POOL_SIZE)
b、非零值的sga_target时，几个参数之间的大小可以动态分配，且根据需要来动态调整各个部分的大小。
c、如果在sga_target非零值时，也设定了DB_CACHE_SIZE等上述几个参数，则sga_targe分配不小于所设置的值作为初始值。
d、当设置sga_target为零值时，SGA相关参数内存的分配由参数控制，且无法根据系统状况实施动态调整大小。
f、Oracle 10g之后建议将SGA的内存管理设置为由sga_target动态管理。

 

更多参考

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

Oracle ROWID

NULL 值与索引(一)

NULL 值与索引(二)

启用 AUTOTRACE 功能

函数使得索引列失效

Oracle 绑定变量窥探

Oracle 自适应共享游标

Oracle 表空间与数据文件
Oracle 密码文件
Oracle 参数文件
Oracle 联机重做日志文件(ONLINE LOG FILE)
Oracle 控制文件(CONTROLFILE)
Oracle 归档日志
Oracle 回滚(ROLLBACK)和撤销(UNDO)
Oracle 数据库实例启动关闭过程
Oracle 10g SGA 的自动化管理
Oracle 实例和Oracle数据库(Oracle体系结构)