Oracle Managing Memory - AMM与ASMM

最新推荐文章于 2025-10-14 16:17:51 发布

原创最新推荐文章于 2025-10-14 16:17:51 发布 · 1.9k 阅读

0 ·

CC 4.0 BY-SA版权

Oracle Administration 专栏收录该内容

47 篇文章

订阅专栏

内存管理-AMM与ASMM

参见官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e25494/memory.htm#ADMIN00207

Oralce内存的自动管理- AMM与ASMM

AMM表示SGA，PGA等都会自动管理。
MMM表示SGA，PGA需要手动设置，但SGA又分为MSMM和ASMM。

自动内存管理（AMM），包含SGA，PGA。
自动共享内存管理（ASMM），包含SGA中的1 cache和4 pool。

注意以下几个关键字的参数的构成。
SGA_TARGET SGA_MAX_SIZE
MEMORY_TARGET MEMORY_MAX_SIZE
pga_aggregate_target
db_cache_size
shared_pool_size java_pool_size large_pool_size streams_pool_size

另外注意这几个参数
LOG_BUFFER
DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE
DB_nK_CACHE_SIZE

ASMM指的是SGA中的1个cache，4和pool（db_cache_size，shared_pool_size java_pool_size large_pool_size streams_pool_size）的管理。
SGA中的下面3项不接受ASMM管理，即LOG_BUFFER，DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE，DB_nK_CACHE_SIZE。

修改前内存参数配置为AMM
SQL> select * from v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 2228440 No
Redo Buffers 3637248 No
Buffer Cache Size 171966464 Yes
Shared Pool Size 146800640 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 559517696 No
Startup overhead in Shared Pool 67108864 No
NAME BYTES RES
-------------------------------- ---------- ---
Free SGA Memory Available 226492416

12 rows selected.

SQL> set linesize 200
SQL> set pagesize 100
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 536M
sga_target big integer 0
SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 536M
memory_target big integer 536M
shared_memory_address integer 0
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SQL> show parameter buffer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep string
buffer_pool_recycle string
db_block_buffers integer 0
log_buffer integer 3424256
use_indirect_data_buffers boolean FALSE
SQL> show parameter pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 0
large_pool_size big integer 0
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 7M
shared_pool_size big integer 0
streams_pool_size big integer 0
SQL> show parameter cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string ON
db_cache_size big integer 0
db_flash_cache_file string
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
result_cache_max_result integer 5
result_cache_max_size big integer 1376K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
session_cached_cursors integer 50
SQL> select * from v$sga_target_advice;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
160 .5 51 1.0536 8732
240 .75 48 1 8676
320 1 48 1 8676
400 1.25 48 1 8676
480 1.5 48 1 8676
560 1.75 48 1 8676
640 2 48 1 8676

7 rows selected.

AMM->MMM+ASMM参数修改（修改先memory_target的参数为0，再给sga_target设一个值，如果不设置，系统会把当前自动的sga值设置为sga_target）
SQL> select * from v$sga_target_advice;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
160 .5 73 1.45 13760
240 .75 50 1 10098
320 1 50 1 10084
400 1.25 50 1 10084
480 1.5 50 1 10084
560 1.75 50 1 10084
640 2 50 1 10084

7 rows selected.

SQL> alter system set memory_target=0;

System altered.

SQL> alter system set sga_target=320M scope=both;

System altered.

SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 536M
sga_target big integer 320M
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 536M
memory_target big integer 0
shared_memory_address integer 0
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 216M

SQL> shutdown immediate
SQL> startup
ORACLE instance started.
Total System Global Area 334036992 bytes
Fixed Size 2226272 bytes
Variable Size 155191200 bytes
Database Buffers 171966464 bytes
Redo Buffers 4653056 bytes
Database mounted.
Database opened.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 320M
sga_target big integer 320M
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 216M

MMM+ASMM->AMM修改参数
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 320M
sga_target big integer 320M
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 216M
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0

SQL> alter system set memory_target=536M scope=spfile;
SQL> alter system set sga_target=0;--如果不改为0，如果改为大于320M的值，启动时会memory_target报错，建议改为0。
SQL> shutdown immediate;
SQL> startup;