TEMP tablespace: a big one or 10 small ones?

Hi,

we have an Oracle 10g database on a HP/UX machine (parallel queries enabled). Users access the database and perform selects on the tables, sometimes very large selects. Additionally during monthly production huge amounts of data need to be put into the database. Currently the database is not productive, we are only testing...

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.

Now my question is: If there is a huge query during monthly production which - say - would need 20 GB of temp space in order to do some sorting and is forced to use only a single processor, can Oracle then use several of the small tablespaces to suffice the space need of 20GB or does the query break due to non sufficient temp tablespace?

Best regards,
  Alex Received on Wed Sep 14 2005 - 03:33:56 CDT