Re: separate data/inidex

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:aa463k$5pd$1_at_lust.ihug.co.nz...
> > I don't have it here at the office. But someone had the 8.0 version of
> their
> > book and they use the following as an example in that one:
> >
> > Disk Weight Contents
> > 1 Oracle Software
> > 2 35 System, Control file 1
> > 3 40 RBS
> > 4 100 DATA
> > 5 33 INDEXES
> > 6 9 TEMP
> > 7 3 TOOLS
> > 8 40+ REDO LOGS
> > 9 40+ APPLICATION & ARCHIVED REDO
> >
> > It is not hard, from the above, for a reasonable person to say ... I'm
not
> > putting anything on the same physical disks holding my indexes.
>
> Why? Can't you add 35+33+9+3 and still come in at much less than 100?
>
> So what I'm really saying is: what do those weights mean? I'm sure they're
> not kilogrammes, but apart from that, they seem pretty meaningless. Are
they
> trying to say that writes and reads from DATA are 3 times more frequent
than
> from INDEXES? Or 3 times as expensive? Some seem peculiarly precise: 9 for
> TEMP, for example, and not, say, 5 or 10.
>
> >Is the above
> > table reasonably accurate and you disagree with the conclusion?
>
> I can't disagree with it until I know what it actually means. That means
> knowing how they arrived at those numbers, and what significance they are
> invested with in their text. For which I have to get hold of a copy of
their
> book.
>

The relevant section of the book is headed IO contentention among datafiles (p83 in my copy v8 but Loney alone listed as author). The 'weight' is the estimated relative IO load on the datafiles. Thus the procedure that the author starts off following is based on the , somewhat familiar, goal of distributing IO among available devices. My reading of the section is that the procedure being suggested is not at all unreasonable since it is pressing upon us as DBAs the idea that we need to understand the IO requirements of our databases. Unfortunately at the end of the section we get the statement that "These weightings serve to illustrate several cardinal rules about ... file placement. : the DATA tablespaces should be seperated from their INDEXES.." This is a case of drawing an unjustified general conclusion from a given example - I guess we've all done that from time to time. It should also be noted that the suggested practice is also to monitor the actual IO as compared to the estimated IO (and change locations of datafiles accordingly).

My commentary on this section would be

1. Yes the weights given are too precise (unless you are moving an existing db to new hardware in which case you can measure the values over a period).
2. The goal is still IO balancing not seperating index and data per se (though as jonathan states they may have a different IO profile and so need to be seperated for that reason).
3. None of this received wisdom deals hugely satisfactory with heavily cached raid arrays and logical volume managers. It is all predicated on individual disks.
4. It always pays to read the argument as well as the conclusion and consider whether the one follows from the other and even if it does wether the argument is valid.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************