Re: Query Running out of Temp Tablespace

Several things to check.
Compare the explain plans on the 7.3 to the 8i, they are probably different.
Are the tables ananlyzed in 7.3 and 8i? Even so the access paths may differ.

You mayneed to use hints in the 7.3 database to try and getthe same access path in 8i.

Good Luck

Stephen C. Ashmore
Brainbench MVP for Oracle Administration http://www.brainbench.com
"¿fooguy?" <johns_spam_address_at_yahoo.com> wrote in message news:a50fcf68.0110161148.6f1db855_at_posting.google.com...
> 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
Received on Tue Oct 16 2001 - 11:11:36 CDT