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

Hi,

> schonlinner_at_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 Received on Wed Sep 14 2005 - 07:51:24 CDT