Oracle-L: RE: direct distributed insert causes massive sorting on target, why?

From: Naveen Nahata <naveen_nahata_at_mindtree.com>
Date: Wed, 25 Dec 2002 21:48:42 -0800
Message-ID: <F001.005220DE.20021225214842@fatcity.com>

Is it because it was sorting on the partition key to enable each parallel DML worker take care of a particular partition? when u serialized the transaction, it didn't have to sort because there was only one particular worker associated with the insert.

Just a guess.

Regards
Naveen

-----Original Message-----
Sent: Thursday, December 26, 2002 10:29 AM To: Multiple recipients of list ORACLE-L why?

All,

In our 8.1.7.4 warehouse, we are attempting to copy records from a partitioned table in one domain to a partitioned table in another domain (via a database link) like so:

alter session enable parallel dml;

insert /*+ append parallel(a,12) */ into tablea a select /*+ full(b) parallel(b,12) */ * from tableb_at_dblnk b;

However, unexpectedly, this causes two sets of parallel processes to be spawned on the target. One of these sets goes to work immediately, building huge sort segments in their temporary tablespace, and the others sit idle. Since we are extracting 250m+ records, we are blowing out temp tablespace. (The sort segments being created are not the same as the temporary segments that are written by CTAS or index creation statements, instead, they are true sort segments.)

I would expect this behavior from a large query that needed to sort (since these types of queries can spawn two sets of PQ processes - one for record retrieval, one for sorting) however, this is just an insert of all the records with no criteria in the select statement.

Why would a direct distributed parallel DML insert as select (I guess we could call this a DDPDMLIAS? :>)) cause an initial run of sorting?

My working hypothesis is that Oracle is following its usual rules of distributed transactions and bringing the source resultset to the target for manipulation, building it into temp segments and then using that as a source for inserts, much as it does in a typical distributed query. Perhaps a driving_site hint would help here.

However, that does not explain why I was able to cause it to stop it from doing the massive sorts by serializing the target insert.

Oracle support has been able to provide no technical information on this topic.

Any input appreciated.

Thanks,

Jack Silvey

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jack Silvey
INET: oracle-l_at_warehousedba.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Naveen Nahata
INET: naveen_nahata_at_mindtree.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------