Re: TEMP tablespace: a big one or 10 small ones?

schonlinner_at_yahoo.com wrote:

> I first created a single 30GB temp tablespace for the database and
> measured the performance of queries. After that I created 10
> 3-GB-temp-tablespaces and assigned them to a tablespace group, and
> assigned the tablespace group as a temporary tablespace to the user
> executing the queries. The performance then was slightly better.

Besides the very valid point that Sybrand raised, there is a sfundamental client-server principle applicable here.

• A large shared resource is more capable than a series of smaller dedicated resources. **

Virtual memory and swap space clearly illustrates how a shared resource can exceed the capability of the underlaying hardware (or software) components. In Oracle - so does MTS, so does the Buffer Cache, so does the Shared Pool.. to name just a few.

TEMP space in Oracle is no different. I will be very hesitant to create a bunch of small TEMP spaces and use that, instead of using a single larger TEMP space for the entire instance.

Resource management at physical level (i.e. creating dedicated TEMP spaces for resource management)... I do not see the logic in that. There are better ways to deal with resource management in Oracle.

--
Billy