Re: separate data/inidex

I just want to state for the record that this has, at least for me, been the single most valuable thread in this group all year.

Thank you.

Daniel Morgan

"Howard J. Rogers" wrote:

> "Daniel A. Morgan" <damorgan_at_exesolutions.com> wrote in message
> news:3CC30E18.F2461842_at_exesolutions.com...
> > Please excuse me if I beat this one to near death. But I thought the point
> of
> > separation was that with multiple CPUs and multiple drives, on multiple
> controllers
> > Oracle could simultaneously read tables and indexes. Apparently this is
> not the
> > case.
> >
> > So when I look at parameters such as multi_block_read_count the idea that
> putting
> > table in a single extent, meaning a single physical area,
>
> Ah! But it doesn't. It's this same idea we came up against a while back.
> Extents are not contiguous on disk. Even an Oracle block does not need to be
> contiguous on disk. On disk are file system blocks (for want of a better
> term), and they can be scattered all over the place, yet you (and Oracle)
> treat them as a single block or a single extent.
>
> So a single extent does *not* mean "a single physical area", but a single
> logical one.
>
> As to why m_b_r_c speeds up full table scans, it has nothing to do with
> positioning the heads once and then vaccuuming up the blocks as they waltz
> pass. It has rather more to do with how many O/S blocks can be read with one
> read request being issued by the process doing the reading before second and
> subsequent requests must be issued.
>
> >will allow the heads to
> > read large sections without the heads having to move is also not the case?
>
> Definitely not the case.
>
> >If not,
> > what is the point of a multi_block_read.
>
> You cut down on the number of requests a process has to issue and the number
> of responses it has to deal with.
>
> >My assumption based on what is being
> > written in this thread is that the ideal situation is for the heads on a
> hard disk
> > to bounce all over the place. One block here, one block there.
> >
>
> But that's going to happen anyway, because an extent is not contiguous on
> disk.
>
> > If you don't like the old rule. And I am hardly wedded to it. What would
> you replace
> > it with? Chaotic access?
> >
>
> Even I/O across all disks, regardless of what segment types they happen to
> contain.
>
> Easy.
>
> Regards
> HJR
>
> > Daniel Morgan
> >
> >
> >
> > Thomas Kyte wrote:
> >
> > > In article <ug01opndy.fsf_at_rcn.com>, Galen says...
> > > >
> > > >On 21 Apr 2002, tkyte_at_oracle.com wrote:
> > > >
> > > >> The odds of the heads "remaining" in place is sort of silly as well.
> > > >
> > > >Can you explain how it is that parallel helps performance? I thought
> > > >parallel meant that a particular set of rowids where given to a query
> > > >slave while another set to another query slave and those slaves went
> and
> > > >got all the data between there respective ids. If they can do so
> > > >without the head jumping around from request to request (ie, on
> separate
> > > >disks and no other read requests), then that will be most optimal. But
> > > >if they can't, which is on every multi-user system and/or disk array
> out
> > > >there, then how does parallel speed things up? Is it based mainly on
> the
> > > >fact that there are more cpus being utilized in going after a
> particular
> > > >set of data? More instructions for the parallel read are being sent
> and
> > > >therefore executed?
> > >
> > > er? this followup appears to have been made to my post but there was
> nothing
> > > but nothing about parallel in there?
> > >
> > > I'll guess as to what you might mean. In another posting in this thre
> someone
> > > (believe it was HJR) said words to the effect of "the placement of index
> > > separate from table might make sense IF index and table data were
> accessed in
> > > parallel. Since they are not, they are accessed serially and since they
> are
> > > accessed serially having them separated matters not".
> > >
> > > If that is what you are referring to, then it is straight forward. If
> the index
> > > were accessed concurrently with the table, then it would make sense from
> a LOAD
> > > BALANCING perspective to have them on separate devices. They are not
> so -- it
> > > doesn't.
> > >
> > > Our goal is to achieve even distribution across all devices. If in your
> case,
> > > the best you can do is put index on one -- table on another -- thats
> great. In
> > > general, putting index separate from data won't do that (achieve a
> balance).
> > > Striping and other technologies will.
> > >
> > > >
> > > >PQ1 asks CPU1 which asks arrayA for some data, PQ2 asks CPU2 which asks
> > > >arrayA for some data, back and forth ... If it wasn't in parallel,
> then
> > > >only process1 would ask CPU1 which would be asking arrayA for some data
> > > >while other processes would be asking CPU2 which would ask arrayA for
> > > >some data?
> > > >
> > > >--
> > > >Galen deForest Boyer
> > > >Sweet dreams and flying machines in pieces on the ground.
> > >
> > > --
> > > 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
> >
Received on Mon Apr 22 2002 - 11:18:48 CDT