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

From: R. Schierbeek <byteNospam_at_gmail.com>
Date: Wed, 22 Nov 2006 19:31:22 +0100
Message-ID: <45649741$0$85170$dbd4d001@news.wanadoo.nl>

"EscVector" <Junk_at_webthere.com> wrote
> R. Schierbeek wrote:
>> Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl> schreef in bericht
>> > 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.
>>
>> Well Jaap,
>> If you insert less then 100.000 rows a parallel insert might not be faster then
>> a normal insert /*+APPEND */. Check out the APPEND hint.
>>
>> If you still want to do the parallel insert without TEMP usage then
>> drop the indexes on the target table. Oracle first inserts the table data;
>> then does some sorting on the new index data and rebuilds them.
>> No index > no sorts needed.
>>
>> Cheers,
>> Roelof Schierbeek; DBAb
>
> So if I offline my tempspace and run a parallel query it will work as
> long as the table being queried has not indexes?

Yes it will work as long as the table being inserted into has not indexes. If you have indexes - well go ahead and make my day !

And take a look at temp/sort table while u're inserting, eg v$sort_usage or v$sort_segment: col current_users for 999 head 'curr|users'

col FREED_EXTENTS for 999 head 'FREED|EXTENTS'

col MAX_USED_SIZE head 'MAX_USED|SIZE'

select current_users,total_extents

   , (USED_BLOCKS * &&block_size)/1024  used_temp
   , (MAX_USED_BLOCKS *&&block_size)/1024 max_used
   ,freed_extents, extent_hits
   ,max_size, max_used_size

from v$sort_segment
Cheers

   Roelof Schierbeek; DBA Received on Wed Nov 22 2006 - 12:31:22 CST