Re: separate data/inidex

"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 - 03:27:46 CDT