|
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.
----- Original Message -----
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 -----
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
|
