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=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.

1 2 下一頁