| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: 9i - Dynamic SGA - SGA_MAX_SIZE
Stephen,
The documentation is pretty wooly regarding this issue but the way it seems
to be intended to work is this:
At startup Oracle will allocate an SGA sized as specified in the
sga_max_size parameter. This is to ensure that the system has enough memory
accomodate what you see as a maximum requirement for the SGA.
After it's allocated this and started the database it should deallocate any
memory it holds over and above that required to store the components of the
SGA. In some platforms/versions this deallocation doesn't occur. Solaris for
example behaves like this unless you move to version 8.
It's possible that your version of Tru64 has a similar limitation or that
you're seeing a bug.
To my mind though, Oracle Support's claim that this is expected behaviour is
a bit of a cop out. This is certainly not the way it was supposed to work.
The concept guide states the following:
"The SGA can grow in response to a database administrator statement, up to an operating system specified maximum and the SGA_MAX_SIZE specification."
and
"Oracle can start instances underconfigured and allow the instance to use more memory by growing the SGA components, up to a maximum of SGA_MAX_SIZE"
Both of these statements imply that the unused memory is supposed to be
released back to the operating system.
The way that this feature operates on your system it allows you to juggle
storage backwards and forwards between caches which is still useful but not
'what it says on the box'.
I'd ask Oracle under what cirtcumstances this is normal behaviour. It's not the way the software is intended to work so maybe it's a platform limitation.
In order to give you a better idea of what Oracle thinks it's SGA is using you can query the following views :
Hope this helps,
Mike Hately
-----Original Message-----
Sent: 01 August 2003 11:59
To: Multiple recipients of list ORACLE-L
Tanel,
I have done this.
ipcs -m would not give me the size of the memory acquired on my system so I used ipcs -a
I have also looked into the ps vax
Which gives more detail:
Currently this shows 199M reserved to the SGA
EM9I /oracle/app/oracle/product/9.2.0/dbs > ps avx|head -1
PID TTY S TIME SL PAGEIN VSZ RSS %CPU %MEM
COMMAND
OEM9I /oracle/app/oracle/product/9.2.0/dbs > ps avx|grep OEM9I
472016 ?? S 0:00.25 2 3 199M 6.2M 0.0 0.4
ora_dbw0_OEM9I
481099 ?? I 0:00.20 32 2 198M 5.2M 0.0 0.3
ora_d000_OEM9I
481163 ?? S 0:00.29 2 33 196M 3.8M 0.0 0.2
ora_pmon_OEM9I
481141 ?? S 0:00.18 2 8 196M 3.6M 0.0 0.2
ora_s000_OEM9I
480555 ?? S 0:01.49 19 125 195M 2.9M 0.0 0.2
ora_qmn0_OEM9I
481126 ?? I 0:00.66 184 41 195M 2.9M 0.0 0.2
ora_smon_OEM9I
481159 ?? S 0:00.25 3 34 199M 2.8M 0.0 0.2
ora_lgwr_OEM9I
480962 ?? S 0:00.60 1 5 195M 2.7M 0.0 0.2
ora_cjq0_OEM9I
481151 ?? I 0:00.20 748 2 195M 2.7M 0.0 0.2
ora_reco_OEM9I
481148 ?? S 0:00.43 2 2 195M 2.5M 0.0 0.2
ora_ckpt_OEM9I
481909 pts/22 S + 0:00.01 0 0 2.28M 232K 0.0 0.0 grep
OEM9I
I reduce the SGA_MAX_SIZE by 30M (keep all other parameters the same ) and
the memory acquired on the OS drops by 30M
481979 ?? S 0:00.25 2 0 167M 6.4M 0.0 0.4
ora_dbw0_OEM9I
481988 ?? S 0:00.19 19 0 166M 5.2M 0.0 0.3
ora_d000_OEM9I
482000 ?? S 0:00.19 1 0 164M 3.8M 0.0 0.2
ora_pmon_OEM9I
482040 ?? S 0:00.17 19 0 164M 3.6M 0.0 0.2
ora_s000_OEM9I
481976 ?? S 0:00.66 7 0 163M 2.9M 0.3 0.2
ora_smon_OEM9I
482004 ?? S 0:00.22 2 1 167M 2.8M 0.0 0.2
ora_lgwr_OEM9I
481946 ?? S 0:00.20 2 0 163M 2.7M 0.1 0.2
ora_cjq0_OEM9I
482024 ?? S 0:00.17 2 0 163M 2.5M 0.0 0.2
ora_ckpt_OEM9I
481998 ?? S 0:00.16 17 0 163M 2.4M 0.0 0.2
ora_reco_OEM9I
482030 ?? S 0:00.16 19 0 163M 2.4M 0.0 0.2
ora_qmn0_OEM9I
Oracle says this is what you would expect. It will grab the entire value of SGA_MAX_SIZE. This certainly seems to be correct I just think it is stupid. Why grab the memory from the os but never use it.
stephen.
stephen.hodgkinson_at_eaguk.com
>
>
> Hi,
>
> does anybody have any experience with setting the SGA_MAX_SIZE in 9i.
>
> I assumed the purpose of this parameter was that SGA would grow as
> requested to that limit.
>
> Example:
> You could configure your SGA to be 80M
> Set the SGA_MAX_SIZE to be 250M.
>
> I would have expected oracle to acquire 80M of memory from the UNIX
> machine.
>
> In fact using ipcs you can see that oracle will always acquire the value
> of SGA_MAX_SIZE.
>
> It acquires the extra space in the Variable Size of the SGA
>
< Figures snipped for brevity - Mike Hately>
>
>
> I have raised a lengthy call on Metalink and the consultants are
convinced
> this is normal behaviour and what you would expect.
>
> Do people agree with the metalink consultants?
>
> Maybe my expectations were to high but I thought a dynamic sga would mean
I
> could change the amount of memory acquired by the UNIX box.
>
> All opinions welcome. I am on tru64 platform - 9.2.0.3.0
>
> Thanks, Stephen
You agree that you have read and understood this disclaimer and you agree to be bound by its terms.
The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator.
This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on.
No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it.
CE Electric UK Funding Company
Lloyds Court
78 Grey Street
Newcastle upon Tyne
NE1 6AF
Registered in England and Wales: Number 3476201
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) INET: mike.hately_at_nedl.co.uk Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Aug 01 2003 - 07:54:29 CDT
 |
 |