Re: separate data/inidex

Thomas Kyte <tkyte_at_oracle.com> wrote in message news:<aaablj0vvk_at_drn.newsguy.com>...
> In article <f369a0eb.0204251308.6c241085_at_posting.google.com>, dyou98_at_aol.com
> says...
> >
> >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.
>
>
> not to beat a dead horse but...
>
> it *never* did that.
>
> Let's say you have a plan like:
>
> index range scan
> table access by rowid
>
>
> Oracle would goto the data dictionary to figure out where block 1 in the index
> was. Let's say that is file 5, block 55. We would seek there, read that block.
> That block is generally not a "leaf block" so that block would tell us to goto
> another block -- say file 5 block 12345. Now, we seek there and read that
> block. Supposing that block is a leaf block -- we find the row(s) in question.
> We get a rowid. That rowid tells us "file 72, block 532" has the data you want.
> Fine, we goto file 72 -- seek to block 532 and read it.
>
> Whether file 5 and file 72 are on different physical devices *never made a
> difference*. It is still 3 serial "seek/read" pairs. The heads jumped all over
> the place.

No arguments there. These are scattered single block I/Os. The seek length is non-zero even if you stay in the same index segment. Now if you have table and index on the same disk, what would the seek length be between the two segments? Possibly 50 or 100 cylinders. I am not claiming we should always consider these factors. In a typical application the disk head rarely stays on the same table/index. It normally jumps around because different sessions have different requests, so location of table/index segments doesn't matter that much. My point is we shouldn't generalize it so much to say there is nothing we can do to control disk I/O regardless of the application. The simple fact is, seek time = avg(seek length)/(speed of disk) (well, a little more than that). If we have a special situation where we can reduce the average seek length, we should take advantage of it by all means.

>
> > 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?
> >
>
> yup, 100% -- always. In that case, what I would like is for my table to be in
> MANY extents and my INDEX to be in MANY extents and these extents are spread
> across disk 1 and disk 2 evenly (we do that -- when allocating an extent for a
> segment we go from file to file). Now I have an even distribution of data
> across the two devices.
>
> If the index is cached -- i don't get a hot disk (from all reads going to DATA)
>
> If the index is not cached -- i don't get a hot disk -- the IO is even and since
> the reads are *random* reads anyway -- it matters NOT whether they are on 1 or
> 1,000 disks.
>

That'd be a perfectly good configuration for most of applications I've seen.

> >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 Fri Apr 26 2002 - 08:48:11 CDT