Re: Why does Oracle buffer the result from the select for a parallel INSERT INTO SELECT FROM?

On 22 Nov 2006 07:03:39 -0800, "EscVector" <Junk_at_webthere.com> wrote:

>
>Jaap W. van Dijk wrote:
>> Hi,
>>
>> I'm on Oracle 9.2.0.5, Open VMS.
>>
>> I perform an
>>
>> INSERT INTO target
>> SELECT * FROM source
>>
>> If I do this noparallel, the result from the select is immediately
>> inserted into the target.
>>
>> If I do this in parallel, the result of the selects from a slave set
>> are first buffered in TEMP. After this is done, a slave set starts to
>> insert the contents from the buffers into the target.
>>
>> Why this timeconsuming buffering? Can anything be done?
>>
>> While I am writing this, I wonder: I let Oracle decide which degree of
>> parallellization to use. Maybe buffering only takes places if the
>> degree differs between SELECT and INSERT? (I should test myself, but
>> don't have the opportunity right now).
>>
>> Regards, Jaap.
>
>The second time around is always faster if you've run the query and not
>flushed sga. Parallel always reads from disk. No parallel uses buffer
>cache. Parallel needs to sort hence the temp. Johnathan Lewis
>Cost-based Oracle has some good info on this. I like tkyes stuff too,
>but sometimes Kytes stuff is not as Warehouse as I like. Parallel is
>great if data is huge and the entire machine can be devoted to a single
>session. Otherwise, it can cause pain. Why is it an issue if you can
>do it fast w/o parallel?
>

Because it needs to be faster.

noparallel ==> slow select , slow insert, no buffering

parallel ==> fast select, fast insert, but buffering. The fast select and insert more than outweigh the buffering, so this is faster than noparallel.

But it would be really fast if I could do the select and insert parallel without the buffering.

Regards, Jaap. Received on Wed Nov 22 2006 - 15:27:52 CST