Parallel Query with disk arrays (WAS) Re: separate data/inidex

On 21 Apr 2002, tkyte_at_oracle.com wrote:
> In article <ug01opndy.fsf_at_rcn.com>, Galen says...

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

Oops. I should have renamed the subject. Your description of the placement of indexes and tables not affecting performance just triggered a question I've had for awhile on PQ. I'm not asking about indexes and tables here. I'm asking about parallel scans of a particular table. (Indexes and tables are quite similar to partitions of tables)

AFAIU, PQ, in a most simple setup, would work best if a table were to be split across multiple disks and with only one process running. When the PQ is issued, Oracle can notice what rowids exist on what disk, assign a PQ slave those particular rowid sets and those disks can all be scanned in parallel. The disk head can start at one section of the disk, and without jumping around, just straight scan the disk. Now, this makes sense to me. But, then introduce Disk Arrays, RAID, and other hardware configurations. Now, how does Oracle use PQ to speed up scans of tables? How does Oracle assign the correct rowid sets so that the multiple cpus assigned to the scanning task don't make the disk head jump all over the place as new read requests come in?

Sorry for the redirection without the notification.

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