Re: shared pool and SGA issues

DA Morgan wrote:
> EdStevens wrote:
> > Oracle 10.2.0.1.0 on Solaris 10
> >
> > Somewhat lengthy background:
> >
> > Earlier this week we started getting reports of ora-04031. It occurred
> > when a particular PL/SQL procedure was submitted. The procedure itself
> > is rather small and simple, with just a straight line processing of
> > about 4 queries and insert statements. It is run once a day. When the
> > procedure was re-submitted several minutes after failure, it ran with
> > no problems. This occcured on two succesive days.
> >
> > As we dug into it we found that we had SGA_MAX_SIZE=4gb ,
> > SGA_TARGET=4gb and no specific settings for any of the auto-tuned sga
> > parms. We thought 4gb was awfully large for a max sga, but we were
> > under a lot of pressure to get it fixed -- NOW!!! We also looked at
> > the SGA allocation history report (in OEM) and saw it had been steady
> > at about 3gb for the buffer pool and 1gb for the shared pool, until the
> > time we got the first ora-03041, when the buffer pool increased
> > slightly at the expense of the shared pool.
> >
> > So, for a quick fix we decided to set the shared pool to 1gb. My
> > understanding is that, with AMM, this means the shared pool will never
> > be allocated LESS than the specified. When we tried ALTER SYSTEM SET
> > .., the instance crashed. Turns out there is a bug that hits when you
> > try to set one of the auto-tuned parms and have SGA_TARGET to an exact
> > multiple of 4g. Go figure.
> >
> > So now we are under management pressure to bump the SGA_MAX and
> > SGA_TARGET up a bit to get them off the 4gb boundary. I have no
> > problem with that, but am wondering if there might be any other gotchas
> > I don't yet see. Perhaps exeeding some OS kernal settings?
> >
> > We've also wondered if perhaps the 4gb boundary might cause another bug
> > preventing AMM from being able to fully rebalance SGA allocations --
> > like allowing the buffer pool to grow at the expense of the shared
> > pool, but not vice versa.
> >
> > And I'm already looking at how the consultant who set this up arrived
> > at that 4gb in the first place. Strike me as awfully large --
> > certainly bigger than any I've ever seen.
>
> How much RAM is in the machine? How many sessions simultaneously
> connected when things go sideways? Strikes me that your suspicions are
> justified.
>
> If this has never happened before and has now happened two days in a row
> something has changed: What?
>
> I'd suggest taking StatsPack snapshots every 10 minutes for the next 48
> hours unless you have licensed the ability to use AWR.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

Thanks, Daniel. You've given me some more food for thought. A couple of things we just discovered ..

First, the OEM tuning adviser indicated that the sweet spot for SGA size was at 3gb, while we are set at 4gb. Second, I see that shmsys:shminfo_shmmax is set at 4294967295. Looks to me that if I try to increase max sga I could exceed what the OS will give me and end up with a ora-04030 at or shortly after startup. Received on Fri Aug 04 2006 - 14:37:33 CDT