Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Instance tuning

Re: Instance tuning

From: hpuxrac <>
Date: Tue, 26 Jun 2007 07:00:01 -0700
Message-ID: <>

On Jun 26, 12:57 am, wrote:

> > sd2611 <> wrote in news:1182444501.818580.220700
> >
> > > 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.

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

There was not a 10g version of that course until recently. Oracle finally decided that too many people were asking about a 10g course in that area that they could not turn that money down. The 10g course is heavily slanted to basic tasks chiefly oriented around working the OEM GUI. ( The message from oracle seems to be "we can build a better machine if you just know how to click around on the GUI that's all you need to know" ).

The 9i course was just pathetic IMHO. Perhaps marginally better than earlier versions as oracle finally admitted that wait events and understanding what was contributing to response time might be useful.

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

Personally I think an approach as documented and advocated by Cary Millsap in "Optimizing Oracle Performance" is the way to go.

Database design is crucial as well as the knowledge of how to write scalable applications. If that doesn't happen from the get go then Cary's methodology is about all we have left to work with.

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

The original list as presented without any additional context was not worthwhile.

The original question as asked yes had an obvious answer. I asked the OP which one they would choose and hever heard back.

It did appear as is someone was just trying to do some homework using cdos IMHO. Received on Tue Jun 26 2007 - 09:00:01 CDT

Original text of this message