Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: ASMM experiences

Re: ASMM experiences

From: joel garry <>
Date: Thu, 11 Oct 2007 11:47:42 -0700
Message-ID: <>

On Oct 11, 6:20 am, hpuxrac <> wrote:
> On Oct 10, 6:20 pm, wrote:
> > On Oct 11, 7:47 am, JAW <> wrote:
> > > I am running most of my databases at 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

I thought Mike made a couple of good points in this presentation (or one I saw like it): (no trollishness intended by posting this).

Searching for MMAN in the metalink bug database is a bit scary.

I kinda have the idea that it needs an experienced DBA around to evaluate whether it's working, and if you have someone that experienced... so I'm not disagreeing with anyone's advice, just taking a more critical view, as someone watching people ask questions about it messing up their systems, but not personally using it.


-- is bogus.
"I've been programming long enough to know that when you're more
concerned about what the right comment character is than about writing
good comments you're not on the level of the important any longer." -
Perry E. Metzger, many years before pointed out code
changing because of comment characters chosen.
Received on Thu Oct 11 2007 - 13:47:42 CDT

Original text of this message