Re: Instance tuning

On Jun 22, 1:06 pm, "Ana C. Dent" <anaced..._at_hotmail.com> wrote:
> sd2611 <sd2..._at_gmail.com> wrote in news:1182444501.818580.220700
> @x35g2000prf.googlegroups.com:
>
> > Where does the instance tuning fit if we use the below-mentioned order
> > implementation.
> > 1. Tune the design.
> > 2.Tune the application.
> > 3. Tune memory.
> > 4. Tune I/O.
> > 5. Tune contention.
> > 6. Tune the operating system.
>
> IMO, #3 & #4 are the only 2 which can be tuned by throwing money at the
> problem; more hardware, more better.
>
> How does one "tune contention" WITHOUT tuning 1 of the other 5 listed?
>
> STUPID LIST!
It is actually the order of events described and promoted in Oracle University's Performance Tuning course (at least the 9i version... I don't recall the 10g version off the top of my head).

Whilst everyone's busy rubbishing it, I would point out that it contains important truths: getting the application and database designs right is paramount (normalisation, etc). And you have to get those designs right before starting to tinker with SHARED_POOL_SIZE and such instance/memory parameters (incidentally: note to the original poster... the answer to your question is at step 3 of your list. Tuning memory means tuning the SGA, and that means you're tuning the instance).. And it is probably going to be more productive tuning things like your shared pool, buffer cache and large pool before you start worrying about I/O (on the grounds that if you get the buffer cache right, for example, you won't be doing much by way of physical I/ O in the first place). And once you've got all those big ticket items out of the way, then it makes sense to look at contention-induced issues (deadlocking and long uncommitted transactions, non-indexed foreign keys, sufficient rollback segments (largely made redundant with automatic undo, of course), redo/archives (LGWR contending with ARCH) and so on).

A specific answer to your first question, therefore, is "perfectly well, provided you understand what is included in step 5 and what's included in the first 4 steps". It always helps to have a context for these things.

The real point that Oracle's own list makes, I think, is that if you dive in trying to tune I/O when your design consists of non-normalised tables with 506 columns of CHAR(2000), you're wasting your time. Take things in the 'DAMICOS' order, however, and you stand a good chance of doing 80% of your tuning once the first two items are dealt with. Whether you agree with the specific items listed is a different matter: that it makes sense to normalise appropriately before worrying about lock contention is, I think, undoubtedly true.

Of course, back in the real world, we inherit our designs and application code and have to start work at Step 3. And quite often, you'll find managers expecting the DBA to work miracles at step 3 to correct the deficiencies introduced at steps 1 and 2 -and getting frustrated when they can't.

Anyway, before everyone dives onto the 'stupid list' bandwagon, understand where it came from and what it's actually trying to say: the order of event in tuning is important. That's a pretty sensible message, actually. Received on Mon Jun 25 2007 - 23:57:45 CDT