通过修改SHARED_POOL_SIZE=10240M 后,系统自动调整后的状态:
shared pool 收缩了236次,DEFAULT buffer cache 自动增大了238次
SQL> @mem10
List Mem Usage
COMPONENT CURRENT_SIZE_GB MIN_SIZE_GB MAX_SIZE_GB USER_SPECIFIED_SIZE_GB OPER_COUNT LAST_OPER_TYPE
------------------------------ --------------- ----------- ----------- ---------------------- ---------- --------------
shared pool 10.609375 10.609375 29.046875 10 236 SHRINK
large pool .078125 .078125 .078125 0 0 STATIC
java pool .015625 .015625 .015625 0 0 STATIC
streams pool .015625 .015625 .03125 0 2 SHRINK
DEFAULT buffer cache 20.015625 1.5625 20.015625 0 238 GROW
KEEP buffer cache 1 1 1 1 0 STATIC
RECYCLE buffer cache 0 0 0 0 0 STATIC
DEFAULT 2K buffer cache 0 0 0 0 0 STATIC
DEFAULT 4K buffer cache 0 0 0 0 0 STATIC
DEFAULT 8K buffer cache 0 0 0 0 0 STATIC
DEFAULT 16K buffer cache 0 0 0 0 0 STATIC
DEFAULT 32K buffer cache 0 0 0 0 0 STATIC
ASM Buffer Cache 0 0 0 0 0 STATIC
13 rows selected.
INST_ID NAME SIZE_MB RESIZEABLE
---------- -------------------------------- ---------- ---------------
1 Maximum SGA Size 32768 No
Buffer Cache Size 21520 Yes
Shared Pool Size 10864 Yes
Startup overhead in Shared Pool 448 No
Redo Buffers 269 No
Large Pool Size 80 Yes
Streams Pool Size 16 Yes
Granule Size 16 No
Java Pool Size 16 Yes
Fixed SGA Size 2 No
Free SGA Memory Available 0
2 Maximum SGA Size 32768 No
Shared Pool Size 22352 Yes
Buffer Cache Size 10032 Yes
Startup overhead in Shared Pool 1408 No
Redo Buffers 269 No
Large Pool Size 80 Yes
Granule Size 16 No
Streams Pool Size 16 Yes
Java Pool Size 16 Yes
Fixed SGA Size 2 No
Free SGA Memory Available 0
22 rows selected.
依据上面的方法,再次分析,SHARED_POOL_SIZE=5120M 就可以满足系统要求。
继续调整:
node1:
alter system set SHARED_POOL_SIZE=6144M sid='yyjc1'
node2:
alter system set SHARED_POOL_SIZE=6144M sid='yyjc2'
现在可以调整DB_CACHE_SIZE 大小:
alter system set DB_CACHE_SIZE=20480M sid='yyjc1'
注意上面查询结果,node2还没有调整到位,因为node2基本没什么业务操作,不会频繁引起SGA调整
所以还是会报错:
SQL> alter system set DB_CACHE_SIZE=20480M sid='yyjc2';
alter system set DB_CACHE_SIZE=20480M sid='yyjc2'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache
等待有足够的剩余内存时再做调整,或者等待下次重启。
通过case学到了什么?
通过这个case让我们明白AMM,ASMM自动管理有时候也是不可靠的,需要定期查询v$sga_dynamic_components/
v$memory_dynamic_components(11G)视图,分析是否存在大量的内存动态调整,确定内存分配是否合理,是否需要
人为调整。
系统经过一段时间运行优化,基本稳定后,需要依据oracle内存各部件统计数据和建议值,
调整SHARED_POOL_SIZE和DB_CACHE_SIZE 参数。合理分配内存主要部件的大小,避免内存
频繁调整带来的隐患。
最后看一个稳定的系统:
下面这个系统是稳定的,是经过调整的,实例启动后没有发生内存自动调整,这个是我们期待的效果:
(这个库原来报4031错误,和上面这个case正好相反shared pool 太小),调整后运行平稳
sho parameter shared_pool_size
shared_pool_size big integer 8G
COMPONENT CURRENT_SIZE_GB MIN_SIZE_GB MAX_SIZE_GB USER_SPECIFIED_SIZE_GB OPER_COUNT LAST_OPER_TYPE
------------------------------ --------------- ----------- ----------- ---------------------- ---------- --------------
shared pool 8 8 0 8 0 STATIC
large pool .015625 0 0 0 1 GROW
java pool .015625 .015625 0 0 0 STATIC
streams pool 0 0 0 0 0 STATIC
DEFAULT buffer cache 23.828125 23.828125 0 0 1 INITIALIZING
KEEP buffer cache .125 .125 0 .125 0 STATIC
RECYCLE buffer cache 0 0 0 0 0 STATIC
DEFAULT 2K buffer cache 0 0 0 0 0 STATIC
DEFAULT 4K buffer cache 0 0 0 0 0 STATIC
DEFAULT 8K buffer cache 0 0 0 0 0 STATIC
DEFAULT 16K buffer cache 0 0 0 0 0 STATIC
DEFAULT 32K buffer cache 0 0 0 0 0 STATIC
ASM Buffer Cache 0 0 0 0 0 STATIC
13 rows selected.