Re: separate data/inidex

Jonathan Lewis wrote:

> Comments in-line.
>
> --
> 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 <3CC5B90A.7F56053B_at_exesolutions.com>...
> >
> >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.
> >
>
> I wouldn't claim to be able to guess the CBO-calculated cost - but in lots
> of the more straightforward cases I make it my business to estimate
> reasonably accurately the real cost in logical and physical I/O of
> high-profile
> statements in the very early stages of design. That may be why I can appear
> to be so casual about the issue of deciding on balancing I/O.
>
> >
> >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.
>
> But did you ask them every single time if they were doing it because
> it improved performance or if they were doing it because it was an
> administrative benefit for monitoring activity and trapping errors.
> Perhaps if you had asked, some of them would have explained that it
> had little impact on performance.
>
> >
> >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.
>
> Stop here, and you're doing okay.
>
> >One method of doing this is to separate
> >system, table, index, temp, and rollback files onto separate 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.
>
> Another method is to stick files anywhere you fancy, then if
> performance problems exist it may be ..... ;)
>
> More significantly: SAME (as previously mentioned) is the
> best 'low-skill', 'low-risk' option around.
>
> If you want a simple starting approach for unexceptional
> database systems, and you absolutely cannot stripe
> your filesystems :-
>
> Reserve drives for redo
> Reserve drives for archived redo
> Create one rollback tablespace per device
> Create a couple of files per drive for a TEMP tablespace
> Allow about two to four tablespaces per drive for tables
> Allow about two to four tablespaces per drive of indexes
> (The separation of indexes and tables is for admin
> NOT performance reasons)
> Allocate segments to tablespaces in a way that you feel
> will balance physical I/O, and map objects of about the
> same size to the same tablespace.
> Start looking for special cases, e.g. a very large table
> with highly random I/O requirements, which may need
> its own tablespace spread at one file per device.
>
> And before you do all that, try to work out when you
> are going to need to grow into some new disks and
> figure out how you can fit them into the pattern
> without rebuilding the entire database.

Thank you. Exactly what I ahve been looking for. The ribbon has been tied, the card signed, and the envelope sealed.

I really appreciate the effort. In three and one-half hours this thead will become "material" in Mary Gates Hall. Yep I teach tonight in a building built by Bill Gates and named after his mother.

Daniel Morgan Received on Tue Apr 23 2002 - 16:31:27 CDT