在网上经常看见一些对sga_max_size和sga_target的对比、分析,总感觉很复杂,看了反而感觉头脑混沌、不分伯仲。
在此,我也抠一下sga,顿时感觉清爽许多。
先看定义:
SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.
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)
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
-
Streams pool
-
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.
由此可见:
sga_max_size指定了实例一生中可以使用的sga的最大值。(也就是说实例只要不死,就只能用这么多内存。想增加?那不好意思,死一次吧,重启实例)
sga_target指定了所有sga组件的总大小。其实也是sga的最大值,只不过不是一生中最大值,而是当前可使用的sga的最大值。因为sga_target是可以动态修改的,你随时可以调大调小,但是不可能超过sga_max_size,除非又死掉一次(重启))。
也就是说当sga_target<sga_max_size的时候,是sga_target控制sga的大小。
这也就解决了很多人对于sga_target设定大小的纠结,在我看来,很简单,sga_target 应该甚至必须等于sga_max_size,只有这样你所设定的内存大小才能被充分利用。
人家官方文档写的很严谨,一切奥秘尽在lifetime一个单词而已。
另外,t欢迎大家对sga_targe的设定问题提出疑问,以免小生我被一叶障目啊
################################################################################################
下面佐证一下:
SQL> show sga Total System Global Area 209715200 bytes Fixed Size 1218580 bytes Variable Size 130025452 bytes Database Buffers 71303168 bytes Redo Buffers 7168000 bytes SQL> show parameter sga; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 200M sga_target big integer 200M
SQL> select sum(bytes) from v$sgastat; SUM(BYTES) ---------- 209717864
SQL> select sum(bytes) ,pool from v$sgastat group by pool; SUM(BYTES) POOL ---------- ------------ 79689748 4194304 java pool 121639508 shared pool 4194304 large pool SQL> select * from v$sgastat where pool is null; POOL NAME BYTES ------------ -------------------------- ---------- fixed_sga 1218580 buffer_cache 71303168 log_buffer 7168000
下面我把sga_target调成150M:
SQL> alter system set sga_target=150m; 系统已更改。 SQL> select sum(bytes) from v$sgastat; SUM(BYTES) ----------
159386216 SQL> select sum(bytes) ,pool from v$sgastat group by pool; SUM(BYTES) POOL ---------- ------------ 29358100 4194304 java pool 121639508 shared pool 4194304 large pool SQL> select * from v$sgastat where pool is null; POOL NAME BYTES ------------ -------------------------- ---------- fixed_sga 1218580 buffer_cache 20971520 log_buffer 7168000
很明显buffer_cache跟着减少了50M,由原来的71303168减小为20971520。