RE: Nosort parallel dml uses TEMP tablespace
Title: 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
RE: Nosort parallel dml uses TEMP tablespace
Thanks for the input Praveen, if only it were that easy. The sql is a straight insert into tab1 select * from tab2 but with a parallel dml hint. Remove the parallel and it doesnt use TEMP. Theres no distinct, group by, order by or indexes on the target table (there are 12 or so on the source table). As I said I've been reading metalink notes like mad.. Note:50592.1 says Parallel Insert SYNTAX: ALTER session enable parallel dml; INSERT /*+ APPEND PARALLEL(d2 4) */ into d2 SELECT ...HOW IT WORKS: Each slave creates a TEMPORARY segment in target tablespace with INITIAL=NEXT from the tables storage clause. Each slave then populates its own segment in a similar manner to parallel SQLLOAD except that the rows are taken from the SELECT row source. On completion of the INSERT segments are MERGED one TEMPORARY segment with all trailing extents being trimmed - Note: we DO trim the temporary segment that we are merging into. So apparently Each slave creates a TEMPORARY segment in target tablespace but I am getting them appearing in the TEMP tablespace, not the tablespaces containing the partitions. And the volume of TEMP required does not compute either. BTW there are 27 partitions in the target table, which does not match the number parallel slaves Cheers, Adrian -Original Message- Sent: 17 September 2003 09:26 To: Turner, Adrian A SITI-ITPSIE Cc: [EMAIL PROTECTED]; Multiple recipients of list ORACLE-L; [EMAIL PROTECTED] Adrian, Please see the 2nd point. This will always use Temporary tablespace even if you have some other tablespace for the table getting inserted. Operations Requiring Sorting Index creation Parallel insert operation involving index maintenance ORDER BY or GROUP BY clauses DISTINCT values selection UNION, INTERSECT, or MINUS operators Sort-merge joins ANALYZE command execution Please ignore this if you know it already. The Sort Process The Oracle server sorts in memory if the work can be done within an area smaller than the value (in bytes) of the parameter SORT_AREA_SIZE. If the sort needs more space than this value: 1 The data is split into smaller pieces, called sort runs; and each piece is sorted individually. 2 The server process writes pieces to temporary segments on disk; these segments hold intermediate sort runs data while the server works on another sort run. 3 The sorted pieces are merged to produce the final result. If SORT_AREA_SIZE is not large enough to merge all the runs at once, subsets of the runs are merged in a number of merge passes. Regards, Praveen __ Praveen Shetty Oracle DBA Technology Solutions, IS. National Grid Transco, Hinckley. Phone: 715-32703 (Int) ,+44-1455-892703 (Ext) mail 2:[EMAIL PROTECTED] __ Vohra Vishal 17/09/03 09:04 To: Pathania Birinder/PB251/Solihull/[EMAIL PROTECTED], Praveen Shetty/PKS02/Solihull/[EMAIL PROTECTED] cc: Subject: RE: Nosort parallel dml uses TEMP tablespace Gud One ! ! ! !! Regards, Vishal Vohra EBMS 715-32569 Mob:07952883716 - Forwarded by Vohra Vishal/VV011/Solihull/Transco on 17/09/03 09:09 - Turner, Adrian A SITI-ITPSIE [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ell.com cc: Sent by: Subject: RE: Nosort parallel dml uses TEMP tablespace [EMAIL PROTECTED
Re: Nosort parallel dml uses TEMP tablespace
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
Re: Nosort parallel dml uses TEMP tablespace
Title: 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 includePARALLEL 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, youmight see different results. Tanel. - Original Message - From: Tanel Poder To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 17, 2003 2:44 PM Subject: 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
RE: Nosort parallel dml uses TEMP tablespace
Title: RE: Nosort parallel dml uses TEMP tablespace
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:[EMAIL PROTECTED]Sent: 17 September 2003
14:00To: Multiple recipients of list ORACLE-LSubject:
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 includePARALLEL 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, youmight see different results.
Tanel.
- Original Message -
From:
Tanel
Poder
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, September 17, 2003
2:44 PM
Subject: 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 se
Re: Nosort parallel dml uses TEMP tablespace
Title: RE: Nosort parallel dml uses TEMP tablespace
You'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.
- Original Message -
From:
Turner,
Adrian A SITI-ITPSIE
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, September 17, 2003 4:14
PM
Subject: RE: Nosort parallel dml uses
TEMP tablespace
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:[EMAIL PROTECTED]Sent: 17 September 2003
14:00To: Multiple recipients of list ORACLE-LSubject:
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 includePARALLEL 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, youmight see different results.
Tanel.
- Original Message -
From:
Tanel Poder
To: Multiple recipients of list
ORACLE-L
Sent: Wednesday, September 17, 2003
2:44 PM
Subject: 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
Nosort parallel dml uses TEMP tablespace
Title: 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
