Oracle-L: RE: 9i - Dynamic SGA

From: Kirtikumar Deshpande <kirtikumar_deshpande_at_yahoo.com>
Date: Sat, 02 Aug 2003 20:09:23 -0800
Message-ID: <F001.005C8812.20030802200923@fatcity.com>

SGA_MAX_SIZE was introduced in 9i to allow dynamic sizing of SGA (Dynamic Sizing feature) components such as, shared pool, large pool, buffer cache etc. In versions up to 8i, such changes required bouncing the instance.
This parameter assumes the value of the SGA at instance startup. Various components of the SGA can then be increased/reduced as and when needed. The total SGA, thus, can reach a maximum value set by SGA_MAX_SIZE (if set in the init.ora file). That's the idea. However, the implementation is different on various platforms. With ISM, and DISM, on Solaris, there are other issues when it comes to using Dynamic SGA. You may want to search Metalink for specific notes/articles for Solaris.
On AIX 5L as I found out, Oracle uses SGA_MAX_SIZE, if set in init.ora, at the instanace startup, and allocates the excess (difference in computed SGA value and set SGA_MAX_SIZE) to 'variable size'. Hence there is no room for any dynamic sizing (upward) of any SGA component. I did not try to downsize shared pool first, and 'upsize' buffer cache later. May be that would work, but that is not the intention of using this parameter.

PGA_AGRREGATE_TARGET is completely different from this parameter. It sets an instance-wide upper limit for the memory used by sorting, hashing processes.

Hope this helps..

Kirti
- Mohammed Shakir <mshakir08816_at_yahoo.com> wrote:
  > I am little confused about this issue to. I am working on Oracle
  > 9.2.0.3 on Solaris 9 (64 bits) platform. I did not set sga_max_size
  > parameter and I see it set. I am not sure what it means and what kind
  > of problem it will cause me.
  >
  > I have pga_aggregate_target is set for 512MB and it seems it is not
  > counted in this count. I know it is a separate space in the memory.
  > Since this is a new system for me, I am little concerned that Oracle
  > does not chock on me.
  >
  > --- Kirtikumar Deshpande <kirtikumar_deshpande_at_yahoo.com> wrote:
  > > It does not work as advertised, in AIX either... I played with this
  > > in AIX 5L.
  > >
  > >
  > > - Kirti
  > >
  > >
  > >

Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Kirtikumar Deshpande
  INET: kirtikumar_deshpande_at_yahoo.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 Sat Aug 02 2003 - 23:09:23 CDT

RE: 9i - Dynamic SGA - SGA_MAX_SIZE