Re: Ora 1652: Unable to extend temp segment by 128 in temp ts

On Jan 4, 7:59 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jan 4, 7:14 am, dbagtcol <cx4gt..._at_gmail.com> wrote:
>
>
>
>
>
> > Hi all,
>
> > 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
>
> 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.- Hide quoted text -
>
> - 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 -- Received on Fri Jan 04 2008 - 09:07:23 CST