| ora-1652 [message #445344] |
Mon, 01 March 2010 00:35  |
orafacjublu
Messages: 95
Registered: May 2006
Location: KOLKATA
|
Member |
|
|
dear all,
I am inserting data in a 39G table using join with two tables.
each time I am getting error
ORA-12801: error signaled in parallel query server P001
ORA-01652: unable to extend temp segment by 64 in tablespace TEM
Now can anyone tell me what unit is the 64 signify bytes or MB or GB
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ora-1652 [message #445359 is a reply to message #445355] |
Mon, 01 March 2010 03:03  |
orafacjublu
Messages: 95
Registered: May 2006
Location: KOLKATA
|
Member |
|
|
INSERT /*+ append parallel (target,5) */INTO fact1
SELECT /*+ parallel (t,5) */ <select list,.....>
FROM fact1 f INNER JOIN t1 p
although there are indexes on c1 from both the tables
Size of fact1 - 39G
Size of t1 - 2G
Here is the explain plan.
PLAN_TABLE_OUTPUT
Plan hash value: 1181332823
------------------------------------------------------------------------------------------------------------------------------------- ---
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------- ---
| 0 | INSERT STATEMENT | | 153M| 31G| | 1398K (1)| 05:26:16 | | | |
| 1 | LOAD AS SELECT |fact1 | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 153M| 31G| | 1398K (1)| 05:26:16 | Q1,02 | P->S | QC (RAND) |
| 4 | HASH GROUP BY | | 153M| 31G| 41G| 1398K (1)| 05:26:16 | Q1,02 | PCWP | |
|* 5 | HASH JOIN | | 153M| 31G| 78M| 489K (2)| 01:54:08 | Q1,02 | PCWP | |
| 6 | BUFFER SORT | | | | | | | Q1,02 | PCWC | |
| 7 | PX RECEIVE | | 6334K| 320M| | 47871 (1)| 00:11:11 | Q1,02 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 6334K| 320M| | 47871 (1)| 00:11:11 | | S->P | HASH |
| 9 | TABLE ACCESS FULL| t1 | 6334K| 320M| | 47871 (1)| 00:11:11 | | | |
| 10 | PX RECEIVE | | 153M| 23G| | 216K (3)| 00:50:35 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 153M| 23G| | 216K (3)| 00:50:35 | Q1,01 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 153M| 23G| | 216K (3)| 00:50:35 | Q1,01 | PCWC | |
| 13 | TABLE ACCESS FULL| fact1 | 153M| 23G| | 216K (3)| 00:50:35 | Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------- ---
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("F"."c1"="P"."c1")
CM: Added code tags, please do so yourself next time, explain plans are almost impossible to read without them - see the orafaq forum guide if you're not sure how.
[Updated on: Mon, 01 March 2010 04:00] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: ora-1652 [message #445380 is a reply to message #445344] |
Mon, 01 March 2010 05:49 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
1. Why do you need to insert the same data to the same table (from fact1 to fact1)?
2. Is C1 column is unique identifier in T1 table?
3. How many columns you need from T1 table?
4. Try to rewrite the statement without PARALLEL hint
ALTER SESSION SET workarea_size_policy = manual;
ALTER SESSION SET HASH_AREA_SIZE = 2048000000; /* 2 Gb */
INSERT /*+ append */ INTO fact1
SELECT /*+ USE_HASH(t1) */ ...
FROM T1
JOIN FACT1 ON T1.C1 = FACT.C1;
HTH.
|
|
|
|
| Re: ora-1652 [message #445436 is a reply to message #445380] |
Mon, 01 March 2010 20:59 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Clearly that is not the actual SQL. The plan shows you have a GROUP BY.
What column are you grouping on? Is it from the FACT or from the dimension (T1)?
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
