SGA_MAX_SIZE Oracle9i

[sanjay92]

Oracle 9.0.1.3.0 OS : WinNT4.0
Can anybody explain me SGA_MAX_SIZE in Oracle9i. According to my understanding, this parameter defines the Maximum size for SGA that you could ever allocate.
I have seen very strange behaviour by setting this parameter.

First of all, I took out SGA_MAX_SIZE parameter from my init.ora and here is the result of show sga command

SYSTEM@SAN9I.US.ORACLE.COM> startup
ORACLE instance started.

Total System Global Area 63730452 bytes
Fixed Size 283412 bytes
Variable Size 50331648 bytes
Database Buffers 12582912 bytes
Redo Buffers 532480 bytes
Database mounted.
Database opened.

Then I shutdown database and change init.ora file with SGA_MAX_SIZE=200MB

SYSTEM@SAN9I.US.ORACLE.COM> startup
ORACLE instance started.

Total System Global Area 210531792 bytes
Fixed Size 284112 bytes
Variable Size 197132288 bytes
Database Buffers 12582912 bytes
Redo Buffers 532480 bytes
Database mounted.
Database opened.

Now the size of SGA has been changed. How Come ? Why The SGA_SIZE_MAX is changing the size of SGA ?

Thanks
Sanjay

[aarroyob]

I read in oraes.com (spanish) that is for the granularity. If value of SGA_MAX_SIZE <= 128M then the granularity is 4M, if the value is > 128M the granularity is 16M.

Hope that help you to understand this

Angel

[sanjay92]

Thanks for your response but I don't agree with this. I know about granularity. I have read in Metalink about this and it does not works as expected. e.g. if you define SGA_MAX_SIZE=500M and suppose, the actual size of SGA is 128MB , but still Oracle will reserve 500M and when you say show sga, it is going to show you 500M.
I have read in Metalink, that in Solaris 8 when you enable DISM (some os feature), then SGA_MAX_SIZE works as expected.

Sanjay