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


Oracle SGA自动管理特性(sga_target参数)
 
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为零值的情形
[sql] 
--#编辑一个临时的参数文件,并设置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.  
3、参数sga_target非零值的情形
[sql] 
--重启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 intege