Home » RDBMS Server » Performance Tuning » ora-1652 (11g,solaris 9)
ora-1652 [message #445344] Mon, 01 March 2010 00:35 Go to next message
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 #445345 is a reply to message #445344] Mon, 01 March 2010 00:43 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oracle
ORA-01652: unable to extend temp segment by string in tablespace string

Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
Re: ora-1652 [message #445349 is a reply to message #445345] Mon, 01 March 2010 00:56 Go to previous messageGo to next message
orafacjublu
Messages: 95
Registered: May 2006
Location: KOLKATA
Member
The TEMP Datafile is of 46G, Free 46G and next extent 640kb.
How to solve it ?
Re: ora-1652 [message #445352 is a reply to message #445349] Mon, 01 March 2010 01:02 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Documentation suggests thatQuote:
The standard block size is specified by the initialization parameter DB_BLOCK_SIZE.
Re: ora-1652 [message #445355 is a reply to message #445344] Mon, 01 March 2010 01:56 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post EXPLAIN.

Probably you JOIN perform some sort (either SORT-MERGE join or Cartesian). Try enforcing HASH join and using NO_PARALLEL hint.

HTH.

[Updated on: Mon, 01 March 2010 01:56]

Report message to a moderator

Re: ora-1652 [message #445359 is a reply to message #445355] Mon, 01 March 2010 03:03 Go to previous messageGo to next message
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 #445378 is a reply to message #445359] Mon, 01 March 2010 05:42 Go to previous messageGo to next message
orafacjublu
Messages: 95
Registered: May 2006
Location: KOLKATA
Member
It is very urgent . Can anyone guide me
Re: ora-1652 [message #445380 is a reply to message #445344] Mon, 01 March 2010 05:49 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: ora-1652 [message #445458 is a reply to message #445436] Tue, 02 March 2010 00:02 Go to previous message
orafacjublu
Messages: 95
Registered: May 2006
Location: KOLKATA
Member
yeah, group by columns are from fact.
Infact we are populating the fact table.
Previous Topic: question about stats
Next Topic: TEMP TABLESPACE
Goto Forum:
  


Current Time: Wed Dec 07 06:56:09 CST 2016

Total time taken to generate the page: 0.18729 seconds