Re: the wonders of automatic sga management

Mark D Powell wrote:
> On Dec 15, 9:29 am, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> > hpuxrac wrote:
> > > Here's an interesting blog topic from pythian:
> >
> > >http://www.pythian.com/blogs/291/pitfalls-of-oracle-auto-sga-management
> >
> > > Nothing like growing the shared pool to 5 gig to hold lots of SQL not
> > > using bind variables eh?
> >
> > > Maybe we will see something in 11g so that we can set limits ... let
> > > shared pool vary between a min and a max?I wonder if the author of this blog page has not read the Oracle
> > documentation, or well written Oracle 10g books?
> >
> > http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/c...
> > "In addition to setting SGA_TARGET to a non-zero value, you must set
> > the value of all automatically sized SGA components to zero to enable
> > full automatic tuning of these components.
> > Alternatively, you can set one or more of the automatically sized SGA
> > components to a non-zero value, which is then used as the minimum
> > setting for that component during SGA tuning."
> >
> > "You can exercise some control over the size of the automatically sized
> > SGA components by specifying minimum values for the parameters
> > corresponding to these components. Doing so can be useful if you know
> > that an application cannot function properly without a minimum amount
> > of memory in specific components. You specify the minimum amount of SGA
> > space for a component by setting a value for its corresponding
> > initialization parameter. Here is an example configuration:
> > SGA_TARGET = 256M
> > SHARED_POOL_SIZE = 32M
> > DB_CACHE_SIZE = 100M"
> >
> > Even with automatic SGA tuning enabled, it is possible to specify
> > minimum values for various SGA components. For instance, if one were
> > to specify a large value for db_cache_size, that would automatically
> > reduce the amount of memory available for the other components of the
> > SGA, thus setting an upper limit of the shared pool size.
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> I always feel better when I see that other people working with Oracle
> do not know what they are talking about. It makes me feel a little
> better about my chances of finding a job if I ever have to leave my
> current one.

I'm not sure I follow who doesn't know what they are talking about (seriously, I'm not being sarcastic or denigrating anyone here). The pithy fellow stated up front the code is bad, not using binds, and simply demonstrated decent code is a necessary prerequisite for automatic SGA tuning. A max value (even if an undoc parameter) would seem to be a necessary sanity check for any auto-tuning, and the lack of one would seem a design flaw to me, this specifying a large value for db_cache_size to imply a small setting for other settings seems brutish at best.

As far as bugs, I'm with noons.

jg

--
@home.com is bogus.  [The following has sat unused in my rotating sig
file for a very long time:]
"Oracle recommends that the value of pga_aggregate_target be set to the
amount of remaining memory (less a 20% overhead for other UNIX tasks)
on the UNIX server after the instance has been started" - Don Burleson
"When setting this parameter, you should examine the total memory on
your system that is available to the Oracle instance and subtract the
SGA. You can assign the remaining memory to PGA_AGGREGATE_TARGET" -
Oracle docs.  I don't think either is correct.