Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!g14g2000cwa.googlegroups.com!not-for-mail
From: schonlinner@yahoo.com
Newsgroups: comp.databases.oracle.server
Subject: Re: TEMP tablespace: a big one or 10 small ones?
Date: 14 Sep 2005 05:51:24 -0700
Organization: http://groups.google.com
Lines: 53
Message-ID: <1126702284.526011.231980@g14g2000cwa.googlegroups.com>
References: <1126686836.529573.152920@z14g2000cwz.googlegroups.com>
   <1126687899.727118.43200@f14g2000cwb.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 1126702289 15829 127.0.0.1 (14 Sep 2005 12:51:29 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 14 Sep 2005 12:51:29 +0000 (UTC)
In-Reply-To: <1126687899.727118.43200@f14g2000cwb.googlegroups.com>
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: g14g2000cwa.googlegroups.com; posting-host=213.191.83.2;
   posting-account=_bIRawwAAABAX0lKDp9v7ynlUPXA_kgu
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:251476

Hi,

> schonlinner@yahoo.com wrote:
>
> > 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.
>
> Besides the very valid point that Sybrand raised, there is a
> sfundamental client-server principle applicable here.
>
> ** A large shared resource is more capable than a series of smaller
> dedicated resources. **

Yes, that's exactly my point (problem). A large temp tablespace is
capable of handling even the biggest sort.

But let's clear a topic from you and Sybrand: The users don't know
anything about databases. They use a GUI tool to clicker around and
thus to create "reports": The result of their clickering is sent to a
server component (Java) (written by our team) which creates the SQL
select statement. This statement is executed against the database using
only a single technical user (the same database user for all
application users).

So if there are 10 different application users online making queries,
that means that the same database user is executing 10 different
queries. I saw that by using tablespace groups you get a slight
performance increase because Oracle can assign a whole temp tablespace
from the tablespace group to a single query (because *I think* each
time a query gets executed, Oracle chooses a "free" temp tablespace
from the tablespace group, but I may be wrong on this topic), so less
conflicts appear during the sorts done by the queries.

My main question was whether a tablespace group with 10 small
tablespaces are capable of handling even a very big sort (which appears
during monthly production where tables of 20GB in size must be sorted).

And my current answer is:
Use a tablespace group with 10 temp tablespaces with autoextend on and
simply ensure that there is enough space in the file system...

That way whatever will happen the temp tablespaces may increase to
whatever they like until the FS is full and as soon as the sort is
done, the temp tablespaces fall down to their initial size.

Or is something wrong in my thoughts?

Regards,
  Alex