Re: separate data/inidex

In article <3CC30E18.F2461842_at_exesolutions.com>, "Daniel says...
>
>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.
>

for a single user, this is not possible. The access plan would be something like:

table access by rowid
  index range scan

In order to do the table access by rowid - you need the rowid. to get the rowid you need to scan the index. Hence, they are serial operations in this case -- one after the other.

Read index, then read table.

Now, if you have 2 users -- one of them could be reading the table -- the other the index at the same time. Nothing stopping that.

Then again, they could both be reading the index and then both be reading the table at the same time (contending with eachother for this shared resource)....

even distribution of IO, thats the goal. whatever gets you there is good.

>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 files were contigous, Norton Speed Disk never would have been invented. files are not contigous in general (we wouldn't have to defrag if they were). At least not since you had to use the CCF tool in v5 to create a file (create contigous file).

multi-block-read count lets us efficiently ask the OS to get us N number of blocks as fast as possible (as good as it can). Analogy: instead of going to the library and asking for page 1 of a book, then page 2 and so on -- we ask for the book. There is much less context switching going on. Even if the book must be assembled from its various chapters -- it'll be faster to ask for the book then the component pieces.

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

evenly distributed IO is what I would replace it with. I love these disks these days where I am not allowed to pick the placement of files since its all virtualized for me. One big mount point, 10's or 100's of disks underneath with lots of stuff going on that I quite simply "cannot care about" anymore -- the hardware guys, they care -- they move things around to keep the IO even, I don't.

In the olden days, I used to use many files/tablespace spread out across many devices. Put objects into many extents to get them spread out. A "poor mans stripe". Today -- I let the hardware do it.

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

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