granule size oracle,_ksmg_granule_size oracle内存分配粒度

_ksmg_granule_size:

内存分配粒度大小

oracle内存分配的最小单位，最终分配的内存数量都是这个参数的整数倍

10g中这个参数的大小一般遵循如下原则：

– sga_max_size < = 1024M then _ksmg_granule_size = 4M

- sga_max_size > 1024M then _ksmg_granule_size = 16M

SQL> show parameter sga

NAME                                 TYPE        VALUE

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

sga_max_size                         big integer 164M

sga_target                           big integer 0

SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc descbtion

2  from x$ksppi x,x$ksppcv y

3  where x.inst_id=userenv('Instance')

4  and y.inst_id=userenv('Instance')

5  and x.indx=y.indx

6  and x.ksppinm like '%_ksmg_granule%';

rows will be truncated

NAME                             VALUE

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

_ksmg_granule_size               4194304

SQL> show parameter sga

NAME                                 TYPE        VALUE

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

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 18000M

sga_target                           big integer 18000M

SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc descbtion

2  from x$ksppi x,x$ksppcv y

3  where x.inst_id=userenv('Instance')

4  and y.inst_id=userenv('Instance')

5  and x.indx=y.indx

6  and x.ksppinm like '%_ksmg_granule%';

NAME                           VALUE

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

_ksmg_granule_size             16777216

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

这个参数调整并不是任意的，还受到sga总量的限制，如果sga不够，即使调整参数也不会生效，只能调整到系统能够认到的最大值

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

System altered.

SQL>

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  176160768 bytes

Fixed Size                   787588 bytes

Variable Size             149683068 bytes

Database Buffers           25165824 bytes

Redo Buffers                 524288 bytes

Database mounted.

Database opened.

SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc descbtion

2  from x$ksppi x,x$ksppcv y

3  where x.inst_id=userenv('Instance')

4  and y.inst_id=userenv('Instance')

5  and x.indx=y.indx

6  and x.ksppinm like '%_ksmg_granule%';

rows will be truncated

NAME                                                                             VALUE

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

_ksmg_granule_size                                                               8388608

因为本地内存太小，并没有调整到16m，而是8m