Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!news-FFM2.ecrc.net!news.m-online.net!newsfeed.stueberl.de!newsgate.cistron.nl!skynet.be!skynet.be!newspost001!tjb!not-for-mail Reply-To: "Matthias Hoys" From: "Matthias Hoys" Newsgroups: comp.databases.oracle.server References: <1126686836.529573.152920@z14g2000cwz.googlegroups.com> <43287dd0$0$29280$ba620e4c@news.skynet.be> <1126755935.504606@yasure> Subject: Re: TEMP tablespace: a big one or 10 small ones? Date: Thu, 15 Sep 2005 20:38:23 +0200 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2670 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670 X-RFC2646: Format=Flowed; Response Lines: 45 Message-ID: <4329bf9f$0$19492$ba620e4c@news.skynet.be> Organization: -= Belgacom Usenet Service =- NNTP-Posting-Host: 836dfb2e.news.skynet.be X-Trace: 1126809503 news.skynet.be 19492 81.244.110.144:1101 X-Complaints-To: usenet-abuse@skynet.be Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:251638 "DA Morgan" wrote in message news:1126755935.504606@yasure... > Matthias Hoys wrote: >> wrote in message >> news:1126686836.529573.152920@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@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