Re: shared pool and SGA issues

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