_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

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13177610/viewspace-682663/，如需轉載，請註明出處，否則將追究法律責任。