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 : - V$SGA_CURRENT_RESIZE_OPS: Information about SGA resize operations that are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component. - V$SGA_RESIZE_OPS: Information about the last 100 completed SGA resize operations. This does not include any operations currently in progress. - V$SGA_DYNAMIC_COMPONENTS: Information about the dynamic components in SGA. This view summarizes information based on all completed SGA resize operations since startup. - V$SGA_DYNAMIC_FREE_MEMORY: Information about the amount of SGA memory available for future dynamic SGA resize operations. 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. [EMAIL PROTECTED] ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, August 01, 2003 12:54 PM > > > 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 ******************************************************************************************** E mail Disclaimer 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
