Re: Nosort parallel dml uses TEMP tablespace

[Tanel Poder]

Title: RE: Nosort parallel dml uses TEMP tablespace

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.   ----- Original Message ----- From: Turner, Adrian A SITI-ITPSIE To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 17, 2003 11:39 AM Subject: RE: Nosort parallel dml uses TEMP tablespace 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