Re: ASMM experiences

On Oct 11, 2:47 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Oct 11, 6:20 am, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
>
>
>
>
>
> > 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
>
> I thought Mike made a couple of good points in this presentation (or
> one I saw like it): http://www.seouc.com/Presentations/Beefing_Up_Ault.pdf
> (no trollishness intended by posting this).

I agree this seems like a balanced pdf. No silver bullets even. Wow.

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

No argument there. Guess I should have perhaps emphasized the word "considering" a little more in my first post in this topic.

I don't actually have it turned on except in test environments.

>
> 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.
>

Well you can always work with oracle support eh? We all know how well that works I think.

> jg
> --
> @home.com 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 awads.net pointed out code
> changing because of comment characters chosen.- Hide quoted text -
>
Received on Thu Oct 11 2007 - 18:30:27 CDT