Re: separate data/inidex

Jonathan Lewis wrote:

> As I understand it, everyone is saying to you
> the world is full of colour
> and your reply is
> I want it in black or white please.
>
> You've told us several times that you are a teacher -
> you should be teaching people to think and
> understand; not scrabbling about for mantras
> and simple tables and tick boxes.
>
> Does the Loney book explain the meaning of
> 'weight' ? Does it justify the numbers that is
> has ascribed to the default install database ?
> Do the arguments make sense, can you extrapolate
> them to more complex cases ?
>
> Does the book consider the fact that an awful lot
> of small systems consist of nothing but a little
> black box with 2 CPUs and an on-board hardware
> RAID-5 with 45GB in 5 discs; and that not many
> small systems have 9 discs, and the systems that
> do have 9 discs probably have more than one data
> tablespace and one index tablespace ?
>
> I understand your request for a better (performance)
> guideline. But the trivial, mindless, yet precisely
> detailed mantra you seem to want does not exist.
>
> However, try this as something to present to
> your students:
> a) Be aware of what your database has to do
> b) Understand how indexes work and what they cost
> c) Understand how UNDO and redo work
> d) Understand the possible data access mechanisms that Oracle users
> e) Identify the parts of your database that are necessarily
> read-intensive
> f) Identify the parts of your database that are necessarily
> write-intensive
> g1) For administrative reasons separate parts of your system with
> different
> I/O characteristics into separate modules. (One consequence of this
> is that in general no tablespace will hold tables and indexes
> because
> in most cases table access behaviour can be different from index
> access behaviour)
> g2) For administrative reasons (sub)section your system into units
> that can be categorised by size.
> g3) For administrative reasons (sub)section your system into units
> that can be categorised by backup/restore requirements.
> g4) For administrative reasons consider reducing the number of units
> generated by g1, g2, g3 above.
> h) Allocate estimates of physical I/O costs to each unit specified
> in g4
> i) Spread the units from g4 across the available devices with a
> view to balancing the I/O evenly whilst avoiding significant contention
> between units of significantly contradictory characteristic behaviour.
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
> Next Seminar - Australia - July/August
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Daniel Morgan wrote in message <3CC59B1B.57B5715C_at_exesolutions.com>...
> >I don't disagree. But I put the question out here again. Does
> anyone have anything
> >better than the table Loney and Theriault created? Or is all of the
> discussion here
> >just off-the-cuff remarks not based on actual testing?
> >

I'm not asking for black and white. Obviously that does not exist here any more than I can look at a SQL statement and guess in advance its cost.

But that does not mean that every single time someone goes into a development cycle for an application they should start by putting everything into the SYSTEM tablespace. Then add a single tablespace for data and indexes. Then break that up. Then add more, etc. and test every single possible permutation.

I have worked numerous times with consultants from Oracle's own consulting division and not once have I ever seen one of them question the "rule" about separating tables and indexes. And that goes for consultants from lots of other name companies.

Let me try proposing a rule and I'll let you agree or disagree.

The rule:
The goal is to balance I/O across as many separate physical devices, hard disks and controller channels, as possible. One method of doing this is to separate system, table, index, temp, and rollback files onto seprate physical devices. If performance problems exist it may be due to the way the data is being utilized. Verify the nature of the performance issue and if it relates to I/O balancing you may need to look at modifying where objects and datafiles are placed in order to optimize performance.

Does that work?

I feel like a great deal of effort is being expended to drag out a simple general statement. But I definitely appreciate you sticking with me on this. I can't just tell students: "The books are all wrong, the experts are all wrong, and no one in the Oracle development community has a clue where to start." Nor do I think it appropriate to tell them that one group of experts is calling another group of experts amateurs.

Thanks.

Daniel Morgan Received on Tue Apr 23 2002 - 14:42:18 CDT