Re: ASMM experiences

On Oct 10, 6:20 pm, hjr.pyth..._at_gmail.com wrote:
> On Oct 11, 7:47 am, JAW <jwill..._at_aglresources.com> wrote:
>
> > I am running most of my databases at 10.2.0.3 level on Solaris and
> > Windows.
>
> > My question is how do you like ASMM?
>
> > I am getting ready to test it and would like comments.
>
> > Subject: How To Use Automatic Shared Memory Management (ASMM) In
> > Oracle10g
> > Doc ID: Note:295626.1 Type: BULLETIN
> > Last Revision Date: 28-AUG-2007 Status: PUBLISHED
>
> I don't, and I switched it off all the databases I have here (10 of
> them).
>
> The problem is I have developers who, in the past, wrote some
> attrocious SQL: everything is an 'execute immediate' string built up
> with lots of unique values hard-coded, and not a bind variable in
> sight. My library cache hit ratio hovers around the 0.2% mark for most
> of my databases: we have, in short, zero SQL reuse. It's very painful,
> it needs sorting and (before anyone asks), no I can't switch on
> CURSOR_SHARING because that causes execution plans to go wildly wrong.
>
> Anyway: guess what ASMM does when confronted with a 0.2% library cache
> hit ratio? It naturally thinks (as novices always did) that the shared
> pool is too small. So it started shovelling memory at the shared pool
> in an attempt to improve things. What that actually did was: sod-all
> for my library cache hit ratio, which is a function of crap SQL not
> insufficient memory; and it really screwed my buffer cache, which was
> reduced in size to accomodate the Shared Pool growth. The number of
> physical reads was shooting ever-skywards, because hardly any data was
> being cached because the buffer cache was made so small.
>
> Fair enough: the algorithm, even when applied mentally by DBAs doing
> the job themselves, has always been: "A miss on the shared pool is
> much more expensive than a miss on the buffer cache". The ASMM
> approach was entirely appropriate, given that. But it was in fact
> entirely inappropriate because it missed the subtlety that code, not
> size, determines the library cache hit ratio, at least in the first
> instance.
>
> When I took over this database, it had a shared pool of (I think) over
> 2GB and a buffer cache of about 1.7GB. It now operates rather
> effectively with a shared pool of 512MB and a shared pool of 4GB. But
> it only does so because I set SGA_TARGET=0.
>
> Now, that's a worse-case scenario (and trust me, the code I have to
> work with in this database is amongst the worst of its kind so far as
> 'being kind to Oracle' goes. In an earlier incarnation, I enabled ASMM
> on some huge 600GB+ data warehouses, and the results were more than
> acceptable and very good for everyone concerned.
>
> But it's horses for courses. I can't ever recommend "just switch on
> ASMM", because I have first-hand experience of just how wrong it can
> get it when confronted with spectacularly weird circumstances. If you
> are in the middle of the bell curve of weirdness, however, I suspect
> ASMM will do very nicely. If you're out to the sides, though, forget
> it!

Fair enough but there's a middle ground that many people are considering. Allow oracle "some degree" of moving memory around but set minimum sizes for shared_pool, buffer pools etc so that oracle cannot force certain memory areas to go below those minimum sizes.

In effect you constrain ASMM so that it only has a "reasonable amount" ( whatever that means in your environment ) of memory to add or subtract pool definitions with.

In the configuration you describe though it would not probably be worthwhile to implement like that since the shared pool is likely to get all of that "reasonable amount" anyway. Received on Thu Oct 11 2007 - 08:20:31 CDT