| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Nosort parallel dml uses TEMP tablespace
RE: Nosort parallel dml uses TEMP tablespaceYou're welcome, please let me know the results.
It was news to me as well, that so much temp space would be used in your type of parallel direct load insert...
Btw, you can use nologging hint as well in your DML if you haven't already altered the target table to nologging.
Tanel.
Thanks for having a look Tanel; that makes great sense.
I'll have a quick play to convince myself but I'd say its a lesson learned.
BTW did you too experience more temp allocation than you would have expected?
Thanks again,
Adrian
-----Original Message-----
From: Tanel Poder [mailto:tanel.poder.003_at_mail.ee]
Sent: 17 September 2003 14:00
To: Multiple recipients of list ORACLE-L
Subject: Re: Nosort parallel dml uses TEMP tablespace
Hi!
Btw, I did a little testing with PARALLEL 2, and yes it seems to be a parallel slave communications issue, despite the segments show type "SORT". When i traced sorting with 10032 & 10033, I saw sort segment allocation, but no real sorting occurring. When I compared first rows in source table with target one, I saw that when in source table I had rows (1,2,3,4,5,6,7), then in beginning of target table, I had (1,3,5,7,etc..). That means I had only one session reading data (query coordinator), which then evenly distributed data to it's slaves.
You should include PARALLEL hint to your select statement as well, with same number of slaves (in addition to insert +append one), that way PX can work parallel_to_parallel way, thus for each producing (query) slave there is a consuming slave and data doesn't have to be distributed or splitted from one source to several queues (like with parallel_from_serial). That way no data is intermediately stored to temporary segments. This worked for me, but since PX is a complex mechanism, you might see different results.
Tanel.
Hi!
It could be that parallel slaves are passing intermediate results using TEMP tablespace for some reason. (When table queues are full in some cases or smth like that). It can depend on parallel execution plan as well (whether it's parallel_to_parallel or parallel_to_serial data distribution).
If you used only /*+ APPEND */ without parallel, then operations were done serially by one session only, thus no intermediate result passing between slaves (using table queues) were needed.
You might want to post your question to comp.databases.oracle.server newsgroup, I've seen Jonathan Lewis answering more advanced performance related questions there.
Tanel.
I'm even more confused now.
TEMP ran out of space at 20GB - I'm only inserting 12.2GB!
So I kicked it off again with the hint just as /*+ APPEND */ and it completed successfully in 1/6th the time to failure of the parallel insert.
-----Original Message-----
From: Turner, Adrian A SITI-ITPSIE
Sent: 16 September 2003 15:16
To: Multiple recipients of list ORACLE-L
Subject: Nosort parallel dml uses TEMP tablespace
Apologies if this has been covered before but metalink is not clear on the reasons behind it....
The database is running version 9204 EE on WinNT Sp6; and the statement is a parallel direct load into partitioned table selecting from a 12.2GB source table.
ALTER SESSION ENABLE PARALLEL DML ;
INSERT /*+ APPEND PARALLEL(TRANSACTION_NEW,4) */ INTO TRANSACTION_NEW
(select * from TRANSACTIONS);
I'm seeing segments created in the temp tablespace (from v$sort_usage)
Sess# User Name TABLESPACE CONTENTS ext BLOCKS SZ_MB
----- --------- ------------------------------- --------- -------- ---------- ----------
9 SYS TEMP2 TEMPORARY 1629 208512 1629
11 SYS TEMP2 TEMPORARY 1629 208512 1629
12 SYS TEMP2 TEMPORARY 1629 208512 1629
13 SYS TEMP2 TEMPORARY 1629 208512 1629
I've enough space and it'll finish by tomorrow morning which is the deadline but does anyone know why TEMP is required and what the end to end process is?
I would have expected to have seen temporary segments created, but in the partitions own tablespace. It doesnt seem optimal to me.
Thanks in advance for your help,
Regards,
Adrian
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tanel Poder
INET: tanel.poder.003_at_mail.ee
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). Received on Wed Sep 17 2003 - 09:24:40 CDT
 |
 |