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

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? Received on Wed Nov 22 2006 - 09:03:39 CST