关于_ksmg_granule_size与SGA大小的关系

SQL> show parameter large
.Z]"RV*G9y-~N2r,W0
_+My9r)dVDG.C+c0NAME                                 TYPE        VALUEITPUB个人空间2z7lS
lk
------------------------------------ ----------- ------------------------------ITPUB个人空间)gm vtL#oh
large_pool_size                      big integer 0
:iMU)o y9e#oT0SQL> alter system set large_pool_size=2m;
W-zO+CGDD0ITPUB个人空间fm8A:d
A
System altered.ITPUB个人空间$l r#U5A+qVk
ITPUB个人空间:^1aU6L0hW;w
SQL> show parameter large_pool
X2lmZ9Gf"N0ITPUB个人空间v9ik{)Nw}3I)Y*[Hd
NAME                                 TYPE        VALUEITPUB个人空间 ? tR2D;IB
------------------------------------ ----------- ------------------------------ITPUB个人空间3rR%x|Nm*j
large_pool_size                      big integer 4MITPUB个人空间/q@m}-j+O@2O$g
SQL> alter system set large_pool_size=5m;
.ka1iVT)g5J0ITPUB个人空间9| Y mj"~+K2YA k]
System altered.ITPUB个人空间yb\G3M&h
ITPUB个人空间JGG ]JU]+Hu9x&@6Q
SQL> show parameter largeITPUB个人空间:V
Ar{.G?

-| R-P1W,SD }0NAME                                 TYPE        VALUE
K/wq qD0------------------------------------ ----------- ------------------------------
cT1`vaeDs R X0f0large_pool_size                      big integer 8M
&q v q0O|"t&Y0SQL> show sgaITPUB个人空间*^D i\3kJY6R
ITPUB个人空间5S*N'JH-n2D C,N
Total System Global Area  197132288 bytesITPUB个人空间+FmK[+g,?u2Yq
Fixed Size                  1218484 bytesITPUB个人空间G'y p4e-p|9s.g$[^
Variable Size              83888204 bytes
G@XC o6X0Database Buffers          109051904 bytes
~|We
z'qMP4S0Redo Buffers                2973696 bytes
9ri3f+h4L3W0SQL> show parameter sga_
1E?4E"Q*v1X0
*ln%e+L ysfQ7G0NAME                                 TYPE        VALUEITPUB个人空间U?*_~|
------------------------------------ ----------- ------------------------------
$?XWT]:v0sga_max_size                         big integer 188MITPUB个人空间)@H.LE.rm&APK
sga_target                           big integer 188M
mrow{^0

在9i SG中写到,SGA<128M时候,granule大小是4M,但是我们看到在10G的时候,

这个增长是按4M增长的.按eygle查询参数的语法看了一下ITPUB个人空间Ba-\#~^8y:eFB

pf0lxlr`N0SQL> col value for a20ITPUB个人空间%h [-EgRl"Df ]
SQL> lITPUB个人空间#Jq b%Q2}@9Ca_6r
Mi
  1  select x.ksppinm name,y.ksppstvl value,x.ksppdesc descbtionITPUB个人空间Dq{U-n
  2  from x$ksppi x,x$ksppcv y
&n,m*G*d"x7H|0  3  where x.inst_id=userenv('Instance')
H O&DqV8J;@0  4   and y.inst_id=userenv('Instance')
s#e]Zs*NB%^e0  5   and x.indx=y.indxITPUB个人空间e;vi&\'F\\
  6*  and x.ksppinm like '%_ksmg_granule%'ITPUB个人空间%s[&To an'h
SQL> /ITPUB个人空间.j0jEoXWpU:u

@p&_ NiZd QP1h0NAME                 VALUE                DESCBTIONITPUB个人空间'?:r&C6^k
-------------------- -------------------- --------------------------------------
i.i _ Ug0_ksmg_granule_size   4194304              granule size in bytes
Ft r&|d0I\P:k0_ksmg_granule_lockin 1                    granule locking statusITPUB个人空间i,PG)fWY

这个大小是4M.

查了一下metalink:

In this Document
Rcs_R:I0t0  SymptomsITPUB个人空间wL.Al{;Joc
  ChangesITPUB个人空间J;Si,Jr"b
  Cause
/kvZ']F0  SolutionITPUB个人空间Y Vv*L]e
  ReferencesITPUB个人空间
G,s7mgV4}4up'}6e

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.2
"j7an#OxroO0This problem can occur on any platform.
@,gu7`,nd0

Symptoms

Customer is trying to upgrade to 10.2.0.2 from 9.2.0.7 using DBUA.ITPUB个人空间 q4W-y Zi)@ `n
ITPUB个人空间 }&X2y_,J&T5Jw
When attempting to start upgrade using DBUA,
'gxL&Fiv_yg6B o$g0the following error occurs.
;bEa.]cv0This error could be seen through 10.2.0.2 SQLPLUS.
i2B,g4j/g,M4Rg0
$TJ+OK0nT0SQL> startup upgradeITPUB个人空间opd!As$@:YM3cN
ITPUB个人空间;oN1Z
h|
ORA-00064: object is too large to allocate on this O/S (1,7614720)
9N.aR/S2h*aM0
#e'V1jqC*DT0
)p+sJ Rl/GK0
@%G(? ^fX[5G0

Changes

Startup database with 9.2.0.7 parameter file to upgrade database to 10.2.0.2 using DBUA.ITPUB个人空间$z p[U{U

_l/R gY-sC)W.l0ITPUB个人空间 AM:}w$Z/P{Z8{
ITPUB个人空间bZ'~,M*_(G]!Y Uqx
I-y6\

Cause

1. High value of oracle parameter PROCESSES
HWk7]1`+^Z0
h%c;RSr&HP2O9q0 The oracle parameter PROCESSES bigger than 1500 requests the allocation of big granule size.
Rx fj
J0ITPUB个人空间]4M2X_:P$`:p7b#M
2.  Low value of granule size.
5T
Q'^8H\*\TD0
{_2Kp'l0nE*T b [0The oracle hidden parameter "_ksmg_granule_size" is set based on the oracle sga size.ITPUB个人空间6p&H:G4Rg*i&V1qw
ITPUB个人空间E&RY9lzl!I
  -  sga_max_size <= 1024M then _ksmg_granule_size = 4M
!x6J]U}_"P^2Q0  - sga_max_size > 1024M then _ksmg_granule_size = 16MITPUB个人空间lS@,L;}

4K*DV]t6Tz0If you set a parameter that exceeds the value of the granule size that was
)uZH_!|k;V0calculated based on the size of the sga.
)D%W{|+r o0C+^0
"vr3GV1^Hnsr0You will manually have to set the parameter _ksmg_granule_size.ITPUB个人空间*qZ;]
RN
ITPUB个人空间Ff SwCf
  - default _ksmg_granule_size is 4MITPUB个人空间+}"DYI v,rs$z5[T
ITPUB个人空间?Xo&Uj!S
Z
Low value of _ksmg_granule_size can prohibit the database startup.
,~!~5_hHC)ISP0ITPUB个人空间{s;|-I9rd~2Q\Z

o#O;[k-QQ,p03. The ORA-64 error could be occured even though the big "_ksmg_granule_size" was configured inITPUB个人空间!A;kUo0z1YQf
init<SID>.ora file.ITPUB个人空间k:ngrJ*g
ITPUB个人空间]?k,@ U4a-S
   DBUA in 10.2.x strips out  the oracle hidden parameter while opening oracle database.
URpx5_zD2D0

Solution

< Solution 1 >
"B.|$z XU0ITPUB个人空间%^-z|4p/\~.mm5G
reduce the oracle parameter "PROCESSES" as described in Note 310838.1
:ap;d1a*Go0ITPUB个人空间
m"WV*O'f_c9Ay
# open oracle parameter file with editor and put following line
b.g4CY{3M&X|0  PROCESSES <= 1500ITPUB个人空间&r#s$}|!n0q g

p ]*{S9~CT+~0< Solution 2 >

A5z5g#Q;Lr0ITPUB个人空间\4[ z R8z
Increase Oracle hidden parameter value of  "_ksmg_granule_size"
1p} j0Lds3H$Z0
#lo*d+m*OF0 1.  Increase directly the value of '_ksmg_granule_size' parameter to 16M (16777216) or 32M (33554432)ITPUB个人空间g;M(]Y2nC#g1a&b

*m?d_!F,l(yC0    1.1  open oracle parameter file with editor and put following line
fo s$D&L2B/O wk/m0       _ksmg_granule_size=16777216  or 33554432ITPUB个人空间km/k/^2H H]

l(r,x.T8Y2].Z:qT.z0    1.2 do the manual upgradation
Hai*|$J` m!\&_0ITPUB个人空间R2N#l;s:HqYJ,E
   ORITPUB个人空间AZIQ4hhE;qb&]4p

K:P'NW_Pi0 2. Increase SGA size bigger than 1024M to affect granule size
a
]9j)}U/p ~y0y]V\4d0ITPUB个人空间XlD0dS?y.B.g
   2.1 open oracle parameter file with editor and put following lineITPUB个人空间(S3?Ar#qt
c]r&XS
        sga_max_size = 1025MITPUB个人空间 ^$}+a"F1D*H#v
ITPUB个人空间@)c vKn"L
   2.2 Continue the upgradation using DBUA or manually.
0m9s{5A{.d-K+~6a0
R+S7oi$rf0ITPUB个人空间EST0x| ^+o7XZ7}M

Z3|5Jv a0看来10G已经改了