Re: ASMM experiences

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! Received on Wed Oct 10 2007 - 17:20:14 CDT