在ORACLE9I中,各个SGA组件必须手动设置大小,从ORACLE10G开始多了另外一种选择:自动SGA内存管理。当ORACLE使用自动SGA内存管理时,只需设置SGA_TARGET,其他与SGA相关的参数都不用管,此时数据库实例会根据需要为各个池动态地分配内存。
不论是使用自动内存管理还是手动内存管理,都会发现各个池的内存以称之为颗粒(GRANULE,也叫区组)的单位来分配。一个颗粒大小是4M,8M或16M的内存区。各池大小是颗粒的倍数。颗粒的大小有SGA大小来确定,通过查看V$SGA_DYNAMIC_COMPONENTS可以看到SGA的总大小影响颗粒的大小
SQL> show parameter sga_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 1020M
SQL> select component, granule_size from v$sga_dynamic_components;
COMPONENT GRANULE_SIZE
---------------------------------------------------------------- ------------
shared pool 4194304
large pool 4194304
java pool 4194304
streams pool 4194304
DEFAULT buffer cache 4194304
KEEP buffer cache 4194304
RECYCLE buffer cache 4194304
DEFAULT 2K buffer cache 4194304
DEFAULT 4K buffer cache 4194304
DEFAULT 8K buffer cache 4194304
DEFAULT 16K buffer cache 4194304
COMPONENT GRANULE_SIZE
---------------------------------------------------------------- ------------
DEFAULT 32K buffer cache 4194304
ASM Buffer Cache 4194304
13 rows selected.
SQL> alter system set sga_target=1035m scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1090519040 bytes
Fixed Size 2083304 bytes
Variable Size 301991448 bytes
Database Buffers 771751936 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 1040M
SQL> select component,granule_size from v$sga_dynamic_components;
COMPONENT GRANULE_SIZE
---------------------------------------------------------------- ------------
shared pool 16777216
large pool 16777216
java pool 16777216
streams pool 16777216
DEFAULT buffer cache 16777216
KEEP buffer cache 16777216
RECYCLE buffer cache 16777216
DEFAULT 2K buffer cache 16777216
DEFAULT 4K buffer cache 16777216
DEFAULT 8K buffer cache 16777216
DEFAULT 16K buffer cache 16777216
COMPONENT GRANULE_SIZE
---------------------------------------------------------------- ------------
DEFAULT 32K buffer cache 16777216
ASM Buffer Cache 16777216
13 rows selected.
以上的例子使用的是自动SGA管理,由以上可以知道,当SGA小于或等于1GB时,颗粒为4M,当SGA大小查过阀值1G时(对于不同的操作系统甚至不停的版本,这个阀值可能稍有变化),可以看到颗粒大小由4M变为16M。