Ora 1652: Unable to extend temp segment by 128 in temp ts
dbagtcol
INSERT INTO co_ClmLvl2
(
..146 Columns..
)
SELECT
..146 Columns.. {all from H_I.HI_CLMS}
FROM
H_I.HI_CLMS a,
H_RULES.HR_175_EmpGrp b,
zzz_ClmLvl2 x,
zzz_MemType z
WHERE
UPPER(a.MEMBER_GROUP_CODE) = b.GroupNumber
AND CASE WHEN a.PPar_Flag = '1' THEN 'Y_' ELSE 'N_' END ||
REPLACE(a.CLAIM_ID,'-','') || '+' || TO_CHAR(a.LINE_NUMBER) = x.ClmNum
AND COALESCE(z.LVL2ID, b.GroupNumber,'ALL_OTHERS_' || CASE
WHEN SUBSTR(a.MEMBER_GROUP_CODE,1,1) = 'S' THEN 'SUPERMAX' WHEN
SUBSTR(a.MEMBER_GROUP_CODE,1,1) = 'B' THEN 'BLUEADV' ELSE 'NA' END)
= x.LVL2ID;
COMMIT;
/
Sql above gives me Ora-1652 even when I added two more tempfiles each
30gig to temp ts. Something peculiar is that when I do a simple select
count(*) from H_I, it takes hours to complete. I checked dba_objects
and it shows H_I as valid. And the sql is not even listed in v
$session_longops. The under mentioned sql doesn't sum up to more than
4 gig at any point of time:
Code:
SELECT
(Sum(vss.Value)/1024/1024/1024) GB
FROM
v$session vs,
v$sesstat vss,
v$statname vsn
WHERE
(vss.statistic#=vsn.statistic#)
AND (vs.sid = vss.sid)
AND (vsn.name like '%sort%')
AND vss.Value>0;
Also, when i check V$TEMP_SPACE_HEADER, BYTES_FREE is always 0. I am
just confused is it an issue with temp files, does the sql need
tuning, or is it a problem with H_I table? Expected number of result
rows is 3 million, avg_row_len is 700.
Looking forward for you advices.
Thanks in advance,
gtcol
dbagtcol
Charles Hooper
It appears that you have a Cartesian join with the zzz_MemType table:
"AND COALESCE(z.LVL2ID, b.GroupNumber,'ALL_OTHERS_' ... = x.LVL2ID"
Take a close look at the WHERE clause to see if is possible to
eliminate the Cartesian join.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Mark D Powell
>
> - Show quoted text -
Yes, look at the explain plan to see how the CBO is solving this
query. Update the statistics and re-check the plan to see if you get
a change. My guess is the sort space is not being used to hold a sort
but instead is hold a hash table. You can query v$sort_usage while
the query is running to verify this but the plan alone should be
enought to let you determine if this is true.
Determine how the query should be driving (order tables visited) and
how each join operation should be done (hash, nested loops, merge
join). Work to get the explain plan to show your desired path and
test that version of the query.
HTH -- Mark D Powell --
Charles Hooper
> query. Update the statistics and re-check the plan to see if you get
> a change. My guess is the sort space is not being used to hold a sort
> but instead is hold a hash table. You can query v$sort_usage while
> the query is running to verify this but the plan alone should be
> enought to let you determine if this is true.
>
> Determine how the query should be driving (order tables visited) and
> how each join operation should be done (hash, nested loops, merge
> join). Work to get the explain plan to show your desired path and
> test that version of the query.
>
> HTH -- Mark D Powell
Mark,
Do you suspect that the OP might find a "MERGE JOIN CARTESIAN" (sort
merge join) in the explain plan? The current WHERE clause seems to
imply that it might be used. A simplified example:
CREATE TABLE T1 (C1 NUMBER(10));
CREATE TABLE T2 (C1 NUMBER(10));
CREATE TABLE T3 (C1 NUMBER(10));
INSERT INTO
T1
SELECT
ROWNUM
FROM
DUAL
CONNECT BY
LEVEL<=10000;
INSERT INTO
T1
SELECT
ROWNUM*2
FROM
DUAL
CONNECT BY
LEVEL<=10000;
INSERT INTO
T1
SELECT
ROWNUM*3
FROM
DUAL
CONNECT BY
LEVEL<=10000;
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1');
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2');
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3');
And now a WHERE clause that does not directly relate one table to
another:
SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1,
T2,
T3
WHERE
(T1.C1*T2.C1)=T3.C1;
SELECT
*
FROM
TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-
Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 0 |
00:00:00.01 | 3 | | | |
| 2 | MERGE JOIN CARTESIAN| | 1 | 1 | 0 |
00:00:00.01 | 3 | | | |
| 3 | TABLE ACCESS FULL | T2 | 1 | 1 | 0 |
00:00:00.01 | 3 | | | |
| 4 | BUFFER SORT | | 0 | 1 | 0 |
00:00:00.01 | 0 | 73728 | 73728 | |
| 5 | TABLE ACCESS FULL | T3 | 0 | 1 | 0 |
00:00:00.01 | 0 | | | |
|* 6 | TABLE ACCESS FULL | T1 | 0 | 300 | 0 |
00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("T3"."C1"="T1"."C1"*"T2"."C1")
dbagtcol
logic and do a select from another table(equivalent) due to time
constraints. But I still have my query, does the outputs really make
the temp buffer overflow even if we don't have any sorts ? And could
there be any other reasons to cause Ora-01652 other than cartesian
product in this case?
Thanking all,
gtcol
joel garry
See this: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:374218170986
Also, show us how you defined the temp ts. Maybe you didn't do what
you thought, easy mistake to make (search for temporary tablespace on
metalink for lots of info).
Since you didn't post platform info, it could be something silly like
using a DMT with 2k blocks...
Also, sort and aggregate init.ora parameters can make a difference, as
well as autoextend (and some other obscure things can make temp usage
more flagrant, I don't have time to remember right now, and there is
misinformation floating about).
jg
--
@home.com is bogus.
"Tracers work both ways." - U.S. Army Ordnance