Re: TEMP Tablespace

From: djordjej <djordjej_at_home.com>
Date: Wed, 29 Nov 2000 18:48:24 -0500
Message-Id: <10695.123270@fatcity.com>

The size on INITIAL and NEXT for the TEMP tablespace should be the same, and should be N*SORT_AREA_SIZE+DB_BLOCK_SIZE, and PCTINCRESE should be 0. The N from above is usually 3 (to be able to accomodate three contents of the sort memory area), but it depends on the average number of sort runs (merge phases) in your sorts. So if you have huge sorts with a lot of sort runs you would like to have N larger but if you have a large number of sorts that run concurrently are each not that large, you would like to go with larger number of smaller sort segments.

The size of the average sort you can find from the query:

select sum(fs.PHYBLKWRT)*p.value/s.value from v$filestat fs

       , v$datafile f
       , v$tablespace t
       , v$parameter p
       , v$sysstat s
 where f.file# = fs.file#
   and f.ts# = t.ts#
   and t.name = 'TEMP'

and p.name='db_block_size'
and s.name = 'sorts (disk)'
group by p.value, s.value;

HTH Djordje

Original Message ----- From: Emine ATES <emineates_at_postmaster.co.uk> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Wednesday, November 29, 2000 5:09 PM Subject: Re: TEMP Tablespace

> Really, I didn't work with such a big temp tablespace, but
> I recommend you creating it as a temporary tablespace because oracle
behaves different for allocating sort extents at temporary tablespaces.
> Good luck.
>
>
>
> On Wed, 29 Nov 2000 07:30:32 -0800 "Charlie Mengler" <charliem_at_mwh.com>
wrote:
> > Oracle V7.3.4.3 on Solaris V2.6
> >
> > The volume of data I'm required to support has just increased
significantly.
> > I've been force to increase the size of TEMP to around 16GB to support
> > index creation on a new LARGE table. TEMP is/was configured as -
> >
> >
> > SQL> select * from dba_tablespaces where tablespace_name = 'TEMP';
> >
> > TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
MAX_EXTENTS PCT_INCREASE STATUS CONTENTS
>
> ------------------------------ -------------- ----------- ----------- ----
------- ------------ --------- ---------
> > TEMP 1048576 2097152 1
505 1 ONLINE PERMANENT
> >
> > Now I'm getting errors involving hitting the 505 MAXEXTENT limit.
> > I know I can increase this limit, but I suspect that the values
> > INITIAL & NEXT should also be increased.
> >
> > What values do you have for TEMP where it is sized in the 10 - 40 GB
range.
> >
> >
> > --
> > Charlie Mengler Maintenance Warehouse
> > charliem_at_mwh.com 10641 Scripps Summit Ct
> > 858-831-2229 San Diego, CA 92131
> > You don't know what you don't know. Think about it.
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Charlie Mengler
> > INET: charliem_at_mwh.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Emine ATES
> INET: emineates_at_postmaster.co.uk
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Wed Nov 29 2000 - 17:48:24 CST