Re: SMALLFILE TEMP / Temporary Tablespace Groups
From: Greg Rahn <greg_at_structureddata.org>
Date: Tue, 4 Oct 2011 19:54:36 -0700
Message-ID: <CAGXkmivo6Y1p0MXXeXwe12unPfjjywd_pXbV6vs1qHVoDXWRSA_at_mail.gmail.com>
The main reason that you would want to use a smallfile tablespace (consisting of many smallfiles vs one bigfile) is when tablespaces are allocating extents at a very fast pace. Since there is only one file with a bigfile, there is only one file header block, so that could become a point of contention if extent allocation is frequent. If you have several in-flight queries using parallel execution all allocating lots of temp, this may become an issue. Generally if your temp tablespace uniform extent size is large enough, it shouldn't be an issue, but it could be at the extreme end of the performance scale.
Date: Tue, 4 Oct 2011 19:54:36 -0700
Message-ID: <CAGXkmivo6Y1p0MXXeXwe12unPfjjywd_pXbV6vs1qHVoDXWRSA_at_mail.gmail.com>
The main reason that you would want to use a smallfile tablespace (consisting of many smallfiles vs one bigfile) is when tablespaces are allocating extents at a very fast pace. Since there is only one file with a bigfile, there is only one file header block, so that could become a point of contention if extent allocation is frequent. If you have several in-flight queries using parallel execution all allocating lots of temp, this may become an issue. Generally if your temp tablespace uniform extent size is large enough, it shouldn't be an issue, but it could be at the extreme end of the performance scale.
I've never used temp tablespace groups with Exadata and have seen no performance reason to (and I've done workloads with 32TB of temp space allocated, yes terabytes).
On Tue, Oct 4, 2011 at 12:25 PM, Wolfson Larry - lwolfs
<lawrence.wolfson_at_acxiom.com> wrote:
> TIA guys.
> At an EXADATA install we're using BIGFILES except for TEMP. Where are using SMALLFILE (X-Team recommended many smallfiles for TEMP).
> I didn't get why. That's because?
>
> We are not using Temporary Tablespace Groups. The reason we are not using them is that a single query is limited to one TEMP tablespace.
> ALTER USER scott TEMPORARY TABLESPACE temp_ts_group; Doesn't specify limitation. Is this limitation accurate?
-- Regards, Greg Rahn http://structureddata.org -- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 04 2011 - 21:54:36 CDT