Re: the wonders of automatic sga management

Brian Peasland 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?
> >
>
> This exercise also shows why throwing as much memory as possible to
> Oracle is not always a desirable solution. There is much we do not know
> about the system to say for sure, but on the surface, I might surmise
> that setting SGA_TARGET to 7.5GB on this server is setting it too high.
> A lower setting might achieve better results. In this case, the high
> setting appears to let Oracle overallocate the Shared Pool, which we
> know can lead to performance problems.
>

Maybe there are systems that can benefit from having a shared pool larger than a couple hundred meg but nothing I have worked on in 10 years has been like that.

The oracle algorithms that would let it grow to that size must be subject to some interesting components in oracle's quality assurance testing ( or lack thereof ) process. As Nuno and others have pointed out there seems to be no shortage of bugs in high load systems that are attempting to use some of these features in certain circumstances. You would think that it would be possible to build and exercise reliable test systems that would push and find these types of bugs before rolling them out to oracle customers. Received on Mon Dec 18 2006 - 07:08:13 CST