Oracle-L: 9i - Dynamic SGA

From: <Stephen.HODGKINSON_at_eaguk.com>
Date: Fri, 01 Aug 2003 01:54:23 -0800
Message-ID: <F001.005C820F.20030801015423@fatcity.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

                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
 Sga_max_size=75m.                                                          
                                                                            
 Show sga:                                                                  
 Total System Global Area 77041728 bytes                                    
 Fixed Size 733248 bytes                                                    
 Variable Size 41943040 bytes                                               
 Database Buffers 33554432 bytes                                            
 Redo Buffers 811008 bytes                                                  
                                                                            
                                                                            
 sga_max_size=110M and keep everything  else the same.                      
                                                                            
 show sga:                                                                  
 Total System Global Area 114790680 bytes                                   
 Fixed Size 733464 bytes                                                    
 Variable Size 79691776 bytes                                               
 Database Buffers 33554432 bytes                                            
 Redo Buffers 811008 bytes                                                  
 Database mounted.                                                          
 Database opened.

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

This email contains information which is confidential. It is for the exclusive use of the addressee. If you are not an addressee please contact us immediately on 01737 275500. Please note that any distribution, copying or use of this communication or its contents is prohibited.

This footnote also confirms that this email message and all associated attachments have been swept by Network Associates VirusScan for the presence of computer viruses.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
INET: Stephen.HODGKINSON_at_eaguk.com

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 - 04:54:23 CDT

9i - Dynamic SGA - SGA_MAX_SIZE