Re: separate data/inidex

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.

> 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.

>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

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp