最近有网友对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=4194304
robin@SZDB:/u02/database/SYBO2SZ> grep target SYBO2SZ.ora.tmp
*.pga_aggregate_target=199229440
*.sga_target=0
--#使用临时的参数文件启动数据库
robin@SZDB:/u02/database/SYBO2SZ> sqlplus / as sysdba
idle> startup pfile=/u02/database/SYBO2SZ/SYBO2SZ.ora.tmp
ORACLE instance started.
---可以看到此时sga_target为0
idle> show parameter sga_tar
NAME 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 293601280
large_pool_size 4194304
java_pool_size 4194304
streams_pool_size 4194304
db_cache_size 285212672
db_2k_cache_size 0
db_4k_cache_size 0
db_8k_cache_size 0
db_16k_cache_size 0
db_32k_cache_size 0
db_keep_cache_size 0
db_recycle_cache_size 0
log_buffer 6120448
13 rows selected.
-- Author ：Robinson
-- Blog ：http://blog.csdn.net/robinson_0612
--使用临时的pfile来创建spfile
idle> create spfile from pfile='/u02/database/SYBO2SZ/SYBO2SZ.ora.tmp';
File created.
3、参数sga_target非零值的情形
--重启db
idle> startup force;
ORACLE instance started.
idle> select distinct isspecified from v$spparameter;
ISSPEC
------
TRUE -->为true表名此时使用了spfile启动数据库
FALSE
--此时sga_max的值为572m
idle> show parameter sga_max
NAME 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_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 576M
--参数文件指定的值分配的总大小为564m
idle> select (285212672+4194304+4194304+293601280+4194304)/1024/1024 from dual;
(285212672+4194304+4194304+293601280+4194304)/1024/1024
-------------------------------------------------------
564
--来看一下内存的分配粒度，此时为4mb
idle> @mem_granule
NAME 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_size
idle> 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.ora
SYBO2SZ.__db_cache_size=285212672
SYBO2SZ.__java_pool_size=4194304
SYBO2SZ.__large_pool_size=4194304
SYBO2SZ.__shared_pool_size=297795584
SYBO2SZ.__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=576m
idle> ho grep sga_target /tmp/tmp.ora
*.sga_target=603979776
--下面编辑/tmp/tmp.ora，移除原来的有关内存的配值，用带下划线的值来启动数据库
idle> vi /tmp/tmp.ora
idle> ho grep size /tmp/tmp.ora
SYBO2SZ.__db_cache_size=285212672
SYBO2SZ.__java_pool_size=4194304
SYBO2SZ.__large_pool_size=4194304
SYBO2SZ.__shared_pool_size=297795584
SYBO2SZ.__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.ora
ORACLE instance started.
--下面的查询可以看到所有和sga_target相关的内存分配全部变成了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 0
large_pool_size 0
java_pool_size 0
streams_pool_size 0
db_cache_size 0
db_2k_cache_size 0
db_4k_cache_size 0
db_8k_cache_size 0
db_16k_cache_size 0
db_32k_cache_size 0
db_keep_cache_size 0
db_recycle_cache_size 0
log_buffer 6120448
13 rows selected.
--此时sga_max_size也变成了576m,这是因为sga_target必须小于等于sga_max_size
idle> show parameter sga_max
NAME 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 变长数组
PL/SQL --> PL/SQL记录
dbms_xplan之display_cursor函数的使用
Oracle 表空间与数据文件
Oracle 密码文件
Oracle 参数文件
Oracle 联机重做日志文件(ONLINE LOG FILE)
Oracle 控制文件(CONTROLFILE)
Oracle 归档日志
Oracle 回滚(ROLLBACK)和撤销(UNDO)
Oracle 数据库实例启动关闭过程
Oracle 10g SGA 的自动化管理
Oracle 实例和Oracle数据库(Oracle体系结构)
