Re: separate data/inidex

Didn't read the entire thread. But I like to take this opportunity to clear up some confusions about disk I/O. If you think at the PHYSICAL level, each I/O involves two types of activities: 1) moving electrons from disk head to memory. This is instantaneous if

   you have enough bandwidth, and
2) moving disk head to where your data is. This is mechanical motion and

   is tens of thousands of times slower. How far the disk head has to move    determines how slow your I/O is.
So there is more to tuning disk I/O than simply make your instance read from many disks at the same time. Depending on your application, you may have some control on the speed of an average I/O.

If the data or index you want to read is always next to the disk head then you've achieve the best possible configuration for performance. Well, in a real application this almost never happens. Separation of table and index segments was intended to keep disk heads from jumping between segments. However, the access pattern of multi-user applications is inherently random, and that normally negates whatever performance you hope to gain by separating segments. Let's say your application simply reads index then table, and reads index again then table again, with no interference from other sessions, and you have very limited cache ... Can you still say separating table and index won't make a difference?

So the old rule (I didn't invent it) is not just a myth, it has its merits.

Daniel Morgan <damorgan_at_exesolutions.com> wrote in message news:<3CC5D29F.8E28E201_at_exesolutions.com>...
> 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 Thu Apr 25 2002 - 16:08:36 CDT