Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: TEMP Tablespace

RE: TEMP Tablespace

From: Koivu, Lisa <lkoivu_at_qode.com>
Date: Thu, 30 Nov 2000 07:42:02 -0500
Message-Id: <10696.123311@fatcity.com>


This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C05ACA.F023C5C6
Content-Type: text/plain;

        charset="iso-8859-1"

Hi Djordje,

Thanks for sending this to the list. The result is in db blocks, isn't it?

Lisa Rutland Koivu
Oracle Database Administrator
Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL 33319

V: 954.484.3191, x174
F: 954.484.2933 
C: 954.658.5849

http://www.qode.com

"The information contained herein does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com."

-----Original Message-----
From: djordjej [mailto:djordjej_at_home.com] Sent: Wednesday, November 29, 2000 6:50 PM To: Multiple recipients of list ORACLE-L Subject: Re: TEMP Tablespace

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

> 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
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: djordjej
  INET: djordjej_at_home.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).

------_=_NextPart_001_01C05ACA.F023C5C6
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2650.12">
<TITLE>RE: TEMP Tablespace</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>Hi Djordje, </FONT>
</P>

<P><FONT SIZE=3D2>Thanks for sending this to the list.&nbsp; The result =
is in db blocks, isn't it? </FONT>
</P>

<P><FONT SIZE=3D2>Lisa Rutland Koivu</FONT>
<BR><FONT SIZE=3D2>Oracle Database Administrator</FONT>
<BR><FONT SIZE=3D2>Qode.com</FONT>
<BR><FONT SIZE=3D2>4850 North State Road 7</FONT>
<BR><FONT SIZE=3D2>Suite G104</FONT>
<BR><FONT SIZE=3D2>Fort Lauderdale, FL&nbsp; 33319</FONT>
</P>

<P><FONT SIZE=3D2>V: 954.484.3191, x174</FONT>
<BR><FONT SIZE=3D2>F: 954.484.2933 </FONT>
<BR><FONT SIZE=3D2>C: 954.658.5849</FONT>
<BR><FONT SIZE=3D2><A HREF=3D"http://www.qode.com" =
TARGET=3D"_blank">http://www.qode.com</A></FONT>
</P>

<P><FONT SIZE=3D2>&quot;The information contained herein does not =
express the opinion or position of Qode.com and cannot be attributed to =
or made binding upon Qode.com.&quot;</FONT></P>
<BR>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: djordjej [<A =
HREF=3D"mailto:djordjej_at_home.com">mailto:djordjej_at_home.com</A>]</FONT>
<BR><FONT SIZE=3D2>Sent: Wednesday, November 29, 2000 6:50 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Re: TEMP Tablespace</FONT>
</P>
<BR>

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

<P><FONT SIZE=3D2>The size of the average sort you can find from the =
query:</FONT>
</P>

<P><FONT SIZE=3D2>select sum(fs.PHYBLKWRT)*p.value/s.value</FONT>
<BR><FONT SIZE=3D2>&nbsp; from v$filestat fs</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , v$datafile =
f</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , v$tablespace =
t</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , v$parameter =
p</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , v$sysstat =
s</FONT>
<BR><FONT SIZE=3D2>&nbsp;where f.file# =3D fs.file#</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp; and f.ts# =3D t.ts#</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp; and t.name =3D 'TEMP'</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp; and p.name=3D'db_block_size'</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp; and s.name =3D 'sorts (disk)'</FONT>
<BR><FONT SIZE=3D2>&nbsp;group by p.value, s.value;</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>HTH</FONT>
</P>

<P><FONT SIZE=3D2>Djordje</FONT>
</P>

<P><FONT SIZE=3D2>----- Original Message -----</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L =
&lt;ORACLE-L_at_fatcity.com&gt;</FONT>
<BR><FONT SIZE=3D2>Sent: Wednesday, November 29, 2000 5:09 PM</FONT>
</P>
<BR>

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

<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: djordjej</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: djordjej_at_home.com</FONT>
</P>

<P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) =
538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =
Received on Thu Nov 30 2000 - 06:42:02 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US