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

On Nov 22, 11:23 am, j.w.vandijk.removet..._at_hetnet.nl (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.

IMHO parallelization is pretty useless if the affected tables aren't striped over several disks.
I remember Tom Kyte stating somewhere you should use parallelization only if you can't get rid of full table scans at all. Also parallelization always will use parallel server slaves and a query coordinator (the original session), so I bet this is what you are looking at right now: data is retrieved by slaves and inserted by the coordinator.
And yes, this is a 'feature', while you seem to qualify it as a bug.

-- 
Sybrand Bakker
Senior Oracle DBA