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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 4 Jan 2008 07:49:48 -0800 (PST)
Message-ID: <ec744002-b61f-4c59-9eb6-2554ae6366fb@e6g2000prf.googlegroups.com>

On Jan 4, 10:07 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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.
>
>
> 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

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")

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jan 04 2008 - 09:49:48 CST