深入解析10g中SGA_MAX_SIZE和SGA_TARGET参数的区别和作用 - 数据库编程

SGA_MAX_SIZE是从9i以来就有的作为设置SGA大小的一个参数，而SGA_TARGET则是从10g才有的一个新参数，作为配合10g自动管理SGA而出现的，下面以实验的方式，深入解析这2个参数的区别和作用
[oracle@bak ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 13 01:59:11 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
SQL> show parameter sga
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 272M sga_target big integer 272M SQL> desc v$sgainfo Name Null Type ----------------------------------------- -------- ---------------------------- NAME VARCHAR2(32) BYTES NUMBER RESIZEABLE VARCHAR2(3)
SQL> set pages 100 SQL> select name,bytes/1024/1024 "size(MB)",resizeable from v$sgainfo;
NAME size(MB) RES -------------------------------- ---------- --- Fixed SGA Size 1.16254425 No Redo Buffers 2.8359375 No Buffer Cache Size 172 Yes Shared Pool Size 80 Yes Large Pool Size 4 Yes Java Pool Size 4 Yes Streams Pool Size 8 Yes Granule Size 4 No Maximum SGA Size 272 No --SGA_MAX_SIZE对应的值 Startup overhead in Shared Pool 36 No Free SGA Memory Available 0
11 rows selected.
注意，resizeable值为NO的，都是不可动态调整的值，并且由于SGA_TARGET和SGA_MAX_SIZE的大小一致，因此Free SGA Memory Available=0，而所有可动态调整的几个内存组件再加上一个4M Granule Size，则正好等于SGA_TARGET的值
SQL> select (172 + 80 + 4 + 4 + 8 + 4) "sga_target(MB)" from dual;
sga_target(MB) -------------- 272
我们也可以从v$sga_dynamic_components这个视图了解各内存组件的内存分配情况，和v$sgainfo内容基本是一致的，除了没有展示Gradual Size这一项
SQL> select component,current_size/1024/1024 "size(MB)" from v$sga_dynamic_components;

COMPONENT size(MB) ---------------------------------------------------------------- ---------- shared pool 80 large pool 4 java pool 4 streams pool 8 DEFAULT buffer cache 172 KEEP buffer cache 0 RECYCLE buffer cache 0 DEFAULT 2K buffer cache 0 DEFAULT 4K buffer cache 0 DEFAULT 8K buffer cache 0 DEFAULT 16K buffer cache 0 DEFAULT 32K buffer cache 0
ASM Buffer Cache 0
13 rows selected.
SGA = shared pool + large pool + java pool + streams pool + DEFAULT buffer cache = 80 +4 + 4 + 8 + 172 =268M
这个算出来的SGA值比SGA_TARGET的值少了4M，其实就是那个Gradual Size的值
SQL> col name for a35 SQL> select name,issys_modifiable from v$parameter where name like '%sga%';

NAME ISSYS_MOD ----------------------------------- --------- sga_max_size FALSE pre_page_sga FALSE lock_sga FALSE sga_target IMMEDIATE
ISSYS_MODIFIABLE列的值为FALSE的，就是不能动态修改的参数，需重启生效，为IMMEDIATE的，即可以动态修改
SQL> alter system set sga_target=350m; alter system set sga_target=350m * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00823: Specified value of sga_target greater than sga_max_size
如果不带scope参数，默认为both，但要在内存中生效，必须满足SGA_TARGET SQL> alter system set sga_target=350m scope=spfile;
System altered.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.
Total System Global Area 369098752 bytes Fixed Size 1219472 bytes Variable Size 100664432 bytes Database Buffers 264241152 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. SQL> show parameter sga
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE

深入解析10g中SGA_MAX_SIZE和SGA_TARGET参数的区别和作用(一)