10g 中的SGA size 和Granule 变化：

在9i 中我们都知道在管理Oracle 中使用动态SGA 时，Granule 的大小是和SGA 的大小有关系。

SGA 分配的最小单元为一个granule.Oracle SGA 的大小总是granule 的整数倍，即分配是以整数个granule 来分配的。

9i 中的规则如下：

linux/unix

SGA <=128 M granule 4M

SGA >128M granule 16M

Windows

SGA <=128 M granule 4M

SGA >128M granule 8M

10g 中的分配规则为

linux/unix

SGA <=1G granule 4M

SGA >1G granule 16M

Windows

SGA <=1G granule 4M

SGA >1G granule 8M

下面是测试结果

10G Windows 平台：

SQL> show sga

Total System Global Area 612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             167775108 bytes
Database Buffers          436207616 bytes
Redo Buffers                7135232 bytes

SQL> column name format a32
SQL> column bytes format 99999999999;
SQL> column rwesizeable format a5
SQL> show sga

SQL>select * from v$sgainfo;

NAME                                    BYTES RESIZE
-------------------------------- ------------ ------
Fixed SGA Size                        1250428 No
Redo Buffers                          7135232 No
Buffer Cache Size                   436207616 Yes
Shared Pool Size                    159383552 Yes
Large Pool Size                       4194304 Yes
Java Pool Size                        4194304 Yes
Streams Pool Size                           0 Yes
Granule Size                          4194304 No
Maximum SGA Size                    612368384 No
Startup overhead in Shared Pool      50331648 No
Free SGA Memory Available                   0

已選取 11 個資料列.

修改SGA_target_Size=1024M

SGA_target_size=1024M

SQL> show sga

Total System Global Area 1073741824 bytes
Fixed Size                  1253072 bytes
Variable Size             268435760 bytes
Database Buffers          796917760 bytes
Redo Buffers                7135232 bytes
SQL> select * from v$sgainfo
2 ;

NAME                                    BYTES RESIZE
-------------------------------- ------------ ------
Fixed SGA Size                        1253072 No
Redo Buffers                          7135232 No
Buffer Cache Size                   796917760 Yes
Shared Pool Size                    260046848 Yes
Large Pool Size                       4194304 Yes
Java Pool Size                        4194304 Yes
Streams Pool Size                           0 Yes
Granule Size                          4194304 No
Maximum SGA Size                   1073741824 No
Startup overhead in Shared Pool      50331648 No
Free SGA Memory Available                   0

已選取 11 個資料列.

SQL>

修改SGA_target_Size=1025M

SGA_TARGET_SIZE=1025M

SQL> show sga

Total System Global Area 1082130432 bytes
Fixed Size                  1250044 bytes
Variable Size             276827396 bytes
Database Buffers          796917760 bytes
Redo Buffers                7135232 bytes
SQL> select * from v$sgainfo;

NAME                                    BYTES RESIZE
-------------------------------- ------------ ------
Fixed SGA Size                        1250044 No
Redo Buffers                          7135232 No
Buffer Cache Size                   788529152 Yes
Shared Pool Size                    268435456 Yes
Large Pool Size                       8388608 Yes
Java Pool Size                        8388608 Yes
Streams Pool Size                           0 Yes
Granule Size                          8388608 No
Maximum SGA Size                   1082130432 No
Startup overhead in Shared Pool      50331648 No
Free SGA Memory Available                   0

已選取 11 個資料列.

SQL>

10g Linux 平台：

修改SGA_target_Size=128M

SQL> show sga

Total System Global Area 134217728 bytes
Fixed Size                  2019032 bytes
Variable Size              88080680 bytes
Database Buffers           37748736 bytes
Redo Buffers                6369280 bytes
SQL> select * from v$sgainfo;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      2019032 No
Redo Buffers                        6369280 No
Buffer Cache Size                  37748736 Yes
Shared Pool Size                   79691776 Yes
Large Pool Size                     4194304 Yes
Java Pool Size                      4194304 Yes
Streams Pool Size                         0 Yes
Granule Size                        4194304 No
Maximum SGA Size                  134217728 No
Startup overhead in Shared Pool    50331648 No
Free SGA Memory Available                 0

11 rows selected.

SQL>

SQL> show sga

Total System Global Area 1342177280 bytes
Fixed Size                  2020608 bytes
Variable Size             335547136 bytes
Database Buffers          989855744 bytes
Redo Buffers               14753792 bytes
SQL> select * from v$sgainfo;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      2020608 No
Redo Buffers                       14753792 No
Buffer Cache Size                 989855744 Yes
Shared Pool Size                  301989888 Yes
Large Pool Size                    16777216 Yes
Java Pool Size                     16777216 Yes
Streams Pool Size                         0 Yes
Granule Size                       16777216 No
Maximum SGA Size                 1342177280 No
Startup overhead in Shared Pool    67108864 No
Free SGA Memory Available                 0

11 rows selected.

SQL>