Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!z14g2000cwz.googlegroups.com!not-for-mail
From: schonlinner@yahoo.com
Newsgroups: comp.databases.oracle.server
Subject: TEMP tablespace: a big one or 10 small ones?
Date: 14 Sep 2005 01:33:56 -0700
Organization: http://groups.google.com
Lines: 23
Message-ID: <1126686836.529573.152920@z14g2000cwz.googlegroups.com>
NNTP-Posting-Host: 213.191.83.2
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1126686842 23912 127.0.0.1 (14 Sep 2005 08:34:02 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 14 Sep 2005 08:34:02 +0000 (UTC)
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0),gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.0 proxy.lsyas.de:8080 (squid/2.5.STABLE3)
Complaints-To: groups-abuse@google.com
Injection-Info: z14g2000cwz.googlegroups.com; posting-host=213.191.83.2;
   posting-account=_bIRawwAAABAX0lKDp9v7ynlUPXA_kgu
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:251468

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