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.

There's been a few bugs with sga_target - maybe revert back to manual settings and see if you can reproduce.

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------