Re: separate data/inidex

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, will allow the heads to read large sections without the heads having to move is also not the case? If not, what is the point of a multi_block_read. 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.

If you don't like the old rule. And I am hardly wedded to it. What would you replace it with? Chaotic access?

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 Sun Apr 21 2002 - 14:08:09 CDT