| 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
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: Pathania.Birinder_at_uktransco.com; Multiple recipients of list
ORACLE-L; Vohra.Vishal_at_uktransco.com
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
Vohra Vishal
17/09/03 09:04 To: Pathania Birinder/PB251/Solihull/Transco_at_UKTransco, Praveen
Shetty/PKS02/Solihull/Transco_at_UKTransco
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"
<Adrian.Turner_at_sh To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
ell.com> cc:
Sent by: Subject: RE: Nosort parallel dml uses TEMP tablespace
ml-errors_at_fatcity
.com
17/09/03 09:39
Please respond to
ORACLE-L
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
Unless expressly stated to the contrary, the views expressed in this
email are not necessarily the views of National Grid Transco plc or
any of its subsidiaries or affiliates (Group Companies), and the
Group Companies, their directors, officers and employees make no
representation and accept no liability for its accuracy or
completeness.
This e-mail, and any attachments are strictly confidential and
intended for the addressee(s) only. The content may also contain
legal, professional or other privileged information. If you are not
the intended recipient, please notify the sender immediately and then
delete the e-mail and any attachments. You should not disclose, copy
or take any action in reliance on this transmission.
You may report the matter by calling us on + 44(0) 1455 230999
Please ensure you have adequate virus protection before you open or
detach any documents from this transmission. The Group Companies do
not accept any liability for viruses. An e-mail reply to this address
may be subject to monitoring for operational reasons or lawful
business practices.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Turner, Adrian A SITI-ITPSIE INET: Adrian.Turner_at_shell.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesReceived on Wed Sep 17 2003 - 04:44:44 CDT
---------------------------------------------------------------------
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).
 |
 |