USING Auotomatic Shared Memory Management
USING Auotomatic Shared Memory Management
前段时间学习了amm，今天开始学习一下ASMM 。
asmm启动非常简单就是设置，sga_target参数即可，只要设置了asmm，那么sga中的如buffer cache、large pool ，java pool、streams pool 、shared pool，都会根据work进行动态调整，但是像log buffer，db keep 和db recycle buffer以及非标准块，都不能进行自动调整大小了，另外在sag中存在一种叫做granules的单元。
eg：
SQL> select name,bytes/1024/1024||'M' M from v$sgainfo; NAME M -------------------------------- ----------------------------------------- Fixed SGA Size 2.1118316650390625M Redo Buffers 3.875M Buffer Cache Size 124M Shared Pool Size 148M Large Pool Size 4M Java Pool Size 4M Streams Pool Size 4M Shared IO Pool Size 0M Granule Size 4M Maximum SGA Size 445.98828125M Startup overhead in Shared Pool 64M NAME M -------------------------------- ----------------------------------------- Free SGA Memory Available 156M 12 rows selected. SQL>
可以看出所有pool都是granule的整数倍，如果当设置一个参数不是granule的整数倍的时候，取相邻的一个值。例如，我设置shared pool为150M，那么实际为152M。（除了redo buffer之外）
另外asmm有一个sga_max_size，该值和amm的memory_max_target参数的性质类似。
注意：如果你不指定一个sga_max_size值，在数据库初始化的时候会选择一个所有components的sum值，或是一个默认值进行启动实例，演示如下：
首先我确定一下sga_target大小，然后关闭amm。
SQL> SELECT (SELECT SUM(VALUE)/1024/1024 FROM V$SGA)-(SELECT CURRENT_SIZE/1024/1024 FROM V$SGA_DYNAMIC_FREE_MEMORY) AS SGA_TARGET FROM DUAL;
SGA_TARGET
----------
289.988281
SQL>
SQL> SHOW PARAMETER MEMORY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 448M
memory_target big integer 448M
shared_memory_address integer 0
SQL> SHOW PARAMETER PGA_AGGREGATE_TARGET
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SQL> SELECT * FROM V$PGA_TARGET_ADVICE;
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_TIME ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- --- --------------- ---------- ------------------- ----------------------------- --------------------
20447232 .125 ON 477455360 229211 29921280 94 6
40894464 .25 ON 477455360 229211 29921280 94 6
81788928 .5 ON 477455360 215694 0 100 0
122683392 .75 ON 477455360 215694 0 100 0
163577856 1 ON 477455360 215694 0 100 0
196292608 1.2 ON 477455360 215694 0 100 0
229008384 1.4 ON 477455360 215694 0 100 0
261724160 1.6 ON 477455360 215694 0 100 0
294439936 1.8 ON 477455360 215694 0 100 0
327155712 2 ON 477455360 215694 0 100 0
490733568 3 ON 477455360 215694 0 100 0
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_TIME ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- --- --------------- ---------- ------------------- ----------------------------- --------------------
654311424 4 ON 477455360 215694 0 100 0
981467136 6 ON 477455360 215694 0 100 0
1308622848 8 ON 477455360 215694 0 100 0
14 rows selected.
SQL> SELECT 81788928/1024/1024 m FROM DUAL;
M
----------
78
SQL>
SQL>
SQL> alter system reset memory_max_target scope=spfile;
System altered.
SQL> alter system reset memory_target scope=spfile;
System altered.
SQL> alter system set sga_target=310M scope=spfile;
System altered.
SQL> alter system set pga_aggregate_target=78M scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 405020672 bytes
Fixed Size 2213816 bytes
Variable Size 251660360 bytes
Database Buffers 146800640 bytes
Redo Buffers 4345856 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 388M
sga_target big integer 312M
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_aggregate
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 78M
SQL>
好，开始做实验。首先，我不指定sga_max_size参数。
eg：
SQL> alter system reset sga_max_size;
System altered.
SQL>
If you do not specify SGA_MAX_SIZE, then Oracle Database selects a default value that is the sum of all components specified or defaulted at initialization
SQL> alter system reset sga_max_size;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 325685248 bytes
Fixed Size 2213056 bytes
Variable Size 167775040 bytes
Database Buffers 150994944 bytes
Redo Buffers 4702208 bytes
Database mounted.
Database opened.
SQL>
SQL> select sum(value) from v$sga;
SUM(VALUE)
----------
325685248
SQL>
If you do specify SGA_MAX_SIZE, and at the time the database is initialized the value is less than the sum of the memory allocated for all components, either explicitly in the parameter file or by default, then the database ignores the setting for SGA_MAX_SIZE and chooses a correct value for this parameter.
Note:
The STATISTICS_LEVEL initialization parameter must be set to TYPICAL (the default) or ALL for automatic shared memory management to function.<上一个：oracle数据库审计功能
下一个：oracle监听配置问题
