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

<schonlinner_at_yahoo.com> wrote in message news:1126686836.529573.152920_at_z14g2000cwz.googlegroups.com...
> 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
>

Have a look at this item, whilst the bit about music might be irrelevant, the bit about TEMP could have been written with you in mind.

        http://www.dizwell.com/2005/09/music-and-temp.html

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005