RE: 9i - Dynamic SGA - SGA_MAX_SIZE

Just think, Nirvana is only one bullet away!

> -----Original Message-----
> From: Mladen Gogala [mailto:mladen_at_wangtrading.com]
> Sent: Tuesday, August 05, 2003 11:49 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: 9i - Dynamic SGA - SGA_MAX_SIZE
>
>
> To continue down the Stephen's path, "shutdown abort" will
> complete very
> quickly
> and will drastically decrease the size of SGA. What is more,
> the "very busy
> environment"
> Will not be very busy any more. Perhaps very loud, but not very busy.
>
> --
> Mladen Gogala
> Oracle DBA
>
>
>
> -----Original Message-----
> Kirtikumar Deshpande
> Sent: Tuesday, August 05, 2003 12:35 PM
> To: Multiple recipients of list ORACLE-L
>
>
> U R Welcome.
> Although, reducing SGA size is technically possible, it can be a very
> expensive operation in a busy environemt and can take a long time to
> complete. I have not run any specific tests to see how long
> it takes to
> reduce SGA by a certain amount, but you can imagine what must
> be done (find
> least used blocks/age out blocks, keep them from re-use, and finally
> 'detach' them etc..)
>
> - Kirti
>
> --- Mohammed Shakir <mshakir08816_at_yahoo.com> wrote:
> > Kirti
> >
> > Thanks for the info.
> >
> > I could not raise dynamically the size of db_cache_size and did not
> > know why, until I noticed the new parameter sga_max_size. Anyway, I
> > tried to lower the size of db_cache_size dynamically and I did not
> > have a problem. So sgma_max_size does play its role in
> > db_cache_sizing.
> >
> > I have not tested where I increase the size of sga_max_size using
> > init.ora and then try to increase the size of db_cache_size
> > dynamically by the same size as the increase in
> sga_max_size. That is
> > the test for coming weekend.
> >
> >
> > --- Kirtikumar Deshpande <kirtikumar_deshpande_at_yahoo.com> wrote:
> > > 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
> > >
> > >
>
> __________________________________
> 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).
>
>
> --------------------------------------------------------
> Note:
> This message is for the named person's use only. It may
> contain confidential, proprietary or legally privileged
> information. No confidentiality or privilege is waived
> or lost by any mistransmission. If you receive this
> message in error, please immediately delete it and all
> copies of it from your system, destroy any hard copies
> of it and notify the sender. You must not, directly or
> indirectly, use, disclose, distribute, print, or copy
> any part of this message if you are not the intended
> recipient. Wang Trading LLC and any of its subsidiaries
> each reserve the right to monitor all e-mail
> communications through its networks.
>
> Any views expressed in this message are those of the
> individual sender, except where the message states
> otherwise and the sender is authorized to state them
> to be the views of any such entity.
> ---------------------------------------------------------
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mladen_at_wangtrading.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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephen Lee
  INET: Stephen.Lee_at_DTAG.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).