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

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1126755935.504606_at_yasure...
> Matthias Hoys wrote:
>> <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.
>>>
>>
>>
>> Mmm ... I don't quite understand why the performance of the small
>> tablespaces was better than using 1 big tablespace - were all tablespaces
>> created on the same disks ? I guess you could see performance
>> improvements if you have different temp tablespaces on different disks ?
>
> With any large system who has one disk? Who doesn't stripe? Who
> with a large system would ever do something so counterproductive?
> Why not put it all on one LUN while they're at it?
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (replace x with u to respond)

I just wanted to know from the OP if there was a disk subsystem difference between the large temp tablespace and the 30 little ones - just trying to give an explanation for the performance difference between the 2 configs.

Matthias Received on Thu Sep 15 2005 - 13:38:23 CDT