Re: Query Running out of Temp Tablespace

¿fooguy? wrote:
>
> For reasons which make me nuts, were are still running a production
> app on 7.3. We started a migration to 8i, but we've put it on hold
> while we consider a different platform (it's DEC/Compaq/HPaq Alpha).
>
> Anyway, on 7.3, I had a nasty query ala Crystal Reports. I killed it
> after 1h20m. I rewrote all the inner joins to use subqueries, and then
> added 3 or 4 indexes. Now, it runs out of temp tablespace. It was 200M
> with 120 extents:
>
> SQL> /
> ERROR:
> ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
>
> no rows selected
>
> ELAPSED: 0 00:02:31.60 CPU: 0:00:00.00 BUFIO: 19 DIRIO: 0
> FAULTS: 0
>
> I upped it to 400M with 120 extents, then 400M autoextend:
>
> SQL> /
> ERROR:
> ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
> ORA-07869: sfsfs: $WRITE failure
>
> no rows selected
>
> ELAPSED: 0 00:35:38.06 CPU: 0:00:00.00 BUFIO: 20 DIRIO: 0
> FAULTS: 0
>
> It still dies. For kicks, I ran the same query on 8i. The first one
> with all the inner joins and no indexes (that I killed after 1h20m)
> took 27 minutes to finish. The rewritten query took 2.7 seconds under
> 8i.
>
> ::SIGH::
>
> Anyway, I guess I need to make this work under 7.3, so can someone
> offer an explanation, and/or possible solution why the optimized query
> runs out of temp tablespace?
>
> Thanks,
> John

Well, the second "death" looks like the disk filled up.

The use of TEMP space is (I know you know this) is for sorting and agregate functions. Are these being used? What is the load of the system when this is bombing?

Anyway, can you run this query and get some tkprof information (both 7.3 and 8i)? This would be the best way to tune the query and give some insight why it finished in 8i.

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.