oracle 修改sga参数,SGA中granule大小修改

SGA中granule大小修改

我们都知道SGA中的各个组件分配都是以granule作为一个单位来分配的，而并不是一次分配1M或1K这样的单位。

granule大小是以SGA大小由系统设定的，当SGA小于1G时，granule大小为4M,当SGA大于1G的时候granule大小为16M，如图:

SQL> select sum(value) from v$sga;

SUM(VALUE)

----------

176160768

接下来。我们看granule的尺寸大小；

SQL> select component,granule_size from v$sga_dynamic_components;

COMPONENT                                                        GRANULE_SIZE

---------------------------------------------------------------- ------------

shared pool                                                           4194304

large pool                                                            4194304

java pool                                                             4194304

streams pool                                                          4194304

DEFAULT buffer cache                                                  4194304

KEEP buffer cache                                                     4194304

RECYCLE buffer cache                                                  4194304

DEFAULT 2K buffer cache                                               4194304

DEFAULT 4K buffer cache                                               4194304

DEFAULT 8K buffer cache                                               4194304

DEFAULT 16K buffer cache                                              4194304

DEFAULT 32K buffer cache                                              4194304

OSM Buffer Cache                                                      4194304

我们可以通过修改隐含参数"_ksmg_granule_size"来修改granule的大小。

SQL> alter system set "_ksmg_granule_size"=4500000 scope=spfile;

重启oracel以后，我们查询发现granule大小变为了8M,

SQL> select component,granule_size from v$sga_dynamic_components;

COMPONENT                                                        GRANULE_SIZE

---------------------------------------------------------------- ------------

shared pool                                                           8388608

large pool                                                            8388608

java pool                                                             8388608

streams pool                                                          8388608

DEFAULT buffer cache                                                  8388608

KEEP buffer cache                                                     8388608

RECYCLE buffer cache                                                  8388608

DEFAULT 2K buffer cache                                               8388608

DEFAULT 4K buffer cache                                               8388608

DEFAULT 8K buffer cache                                               8388608

DEFAULT 16K buffer cache                                              8388608

DEFAULT 32K buffer cache                                              8388608

OSM Buffer Cache                                                      8388608

现在再改,SQL> alter system set "_ksmg_granule_size"=9000000 scope=spfile;

再来查询granule大小，发现granule大小还是8M,

SQL>  select component,granule_size from v$sga_dynamic_components;

COMPONENT                                                        GRANULE_SIZE

---------------------------------------------------------------- ------------

shared pool                                                           8388608

large pool                                                            8388608

java pool                                                             8388608

streams pool                                                          8388608

DEFAULT buffer cache                                                  8388608

KEEP buffer cache                                                     8388608

RECYCLE buffer cache                                                  8388608

DEFAULT 2K buffer cache                                               8388608

DEFAULT 4K buffer cache                                               8388608

DEFAULT 8K buffer cache                                               8388608

DEFAULT 16K buffer cache                                              8388608

DEFAULT 32K buffer cache                                              8388608

OSM Buffer Cache

后来我分别把"_ksmg_granule_size"又修改为16M和32M得出来的结果都是8M.最后得出结论

在SGA小于1G的配置中，默认的granule大小为4M,可以通过"_ksmg_granule_size"修改granule大小，

当这个参数设为小于4M时，granule大小为4M,  当这个参数设为大于4M时,granule大小为8M.