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

最近有网友对Oracle SGA内存自动管理特性不是很清楚，可能是由于当时翻译的 Oracle 10g SGA 的自动化管理 比较生涩，下面依旧是针

最近有网友对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.

--使用临时的pfile来创建spfile
idle>  create spfile from pfile='/u02/database/SYBO2SZ/SYBO2SZ.ora.tmp';

File created.