Re: Temp Space performance

+1 what Lothar mentioned - rather than trying to tune Temp Tablespaces I would focus more on trying to avoid / minimize temp space usage.

In case of parallel operations there are two common scenarios why you see increased temp space usage: The required buffering (mostly HASH JOIN BUFFERED but could also be additional BUFFER SORTs) and inappropriate distribution methods, like BROADCASTing a row source of significantly underestimated size, or HASH distribution of a row source of significantly overestimated size, leading to additional BUFFERing requirements (see e.g.
http://oracle-randolf.blogspot.com/2012/12/hash-join-buffered.html).

Therefore I would check what causes the temp space usage - if it's the buffering (most likely) the most efficient way to address this is making use of full or at least partial partition wise joins, but this of course means the data needs to be partitioned in suitable way, at least some of the tables, so you need the partitioning option, and you need the corresponding physical design, which isn't something that usually can be done as a "tuning exercise".

You can also try to increase available PGA and/or using BROADCAST instead of HASH distribution, but for large joins this is rather limited.

I've talked about the impact of buffering and the dramatic differences the distribution / partitioning method can make, especially when it comes to Exadata / In-Memory, in a webinar I did for AllThingsOracle/RedGate some time ago:

http://allthingsoracle.com/oracle-exadata-in-memory-real-world-performance/

Randolf

> We are running queries with parallel 8 and outer joining 10 tables.
> Quite a
> few of the table are a billion rows and are using HCC query high
> compression. I have tuned the queries and we good there. The slowness we
> are seeing is in the reading and writing to temp.

--
http://www.freelists.org/webpage/oracle-l