Re: TEMP Tablespace

From: Nikunj Gupta <good_dba_at_hotmail.com>
Date: Fri, 1 Dec 2000 01:52:54 -0800
Message-Id: <10698.123477@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_002A_01C05B39.6BF5ACA0 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

RE: TEMP TablespaceHi Lisa,

I agree with Djordje.=20

But if you have already created TEMP tablespace with 16Gb as requried.. = and would not like to drop and recreate with all that is suggested...=20

It seems you have TEMP tablespace of the type PERMANENT, suggested and = preferred is TEMPORARY instead of PERMANENT. ( What does the LIST Guru's = say ? )

Now, there can be a workaround for your problem.. You can always.. = change the NEXT of TEMP tablespace..=20

Whenever TEMP tablespace is used.. NEXT extent is used for allocation =
and not INITIAL, whereas in all other tablespaces INITIAL is used for =
allocation.  (I hope that is why, it is told to have both Initial and =

Next of them of the same size).

One advantage you may have, as the TEMP tablespace is of the = PERMANENT... .. SMON will COALESCE freespaces and you will have bigger = chunks. which are free.. .. While if it is TEMPORARY then, the free = pieces are marked as free.. and ORACLE internally uses them.. but DOES = NOT release them and show them to be FREE. So, if you have used 90 % of your TEMP Tablespace with TEMPORARY.. you = will always see it as 90% used.. while if it of the type PERMANENT.. and = you have released 90 % of it.. while you are using only 10%, at some = point of time you will find that your tablespace is approx. 90% free.

In both the cases .. all the free pieces are being used and managed.

I am trying to explain.. be patient..... Is it confusing ??

Why I told you this is.. SIMPLE.. if you had smaller NEXT intially.. and = type TEMPORARY ..with 90 % of TS USED, even if you increase NEXT to be = big enough.. You will not be able to take advantage.. and will end up = getting error messages.. because the largest available chunk could be = maximum 10%.

Being PERMANENT.. just change the NEXT to a sufficiently big value.. = but take care it should be the same.. as told by Djordje ... = N*SORT_AREA_SIZE+DB_BLOCK_SIZE where N can be any number.. Once there = are enough big free pieces you may be able to create the index.

PCTINCREASE would be preferred to be ZERO (0) for temporary tablespace.=20

(Again, it is case to case basis)....

LIST Guru's please respond this with your suggestions / corrections = and experience...

HTH Nikunj

Original Message -----=20 From: Koivu, Lisa=20 To: Multiple recipients of list ORACLE-L=20 Sent: Thursday, November 30, 2000 04:45 AM Subject: RE: TEMP Tablespace

Hi Djordje,=20

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

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

  V: 954.484.3191, x174=20
  F: 954.484.2933=20
  C: 954.658.5849=20

http://www.qode.com=20

"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-----=20
From: djordjej [mailto:djordjej_at_home.com]=20 Sent: Wednesday, November 29, 2000 6:50 PM=20 To: Multiple recipients of list ORACLE-L=20 Subject: Re: TEMP Tablespace=20

  The size on INITIAL and NEXT for the TEMP tablespace should be the = same, and=20
  should be N*SORT_AREA_SIZE+DB_BLOCK_SIZE, and PCTINCRESE should be 0. = The N=20
  from above is usually 3 (to be able to accomodate three contents of = the sort=20
  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=20
  you would like to have N larger but if you have a large number of = sorts that=20
  run concurrently are each not that large, you would like to go with = larger=20
  number of smaller sort segments.=20

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

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

         , v$datafile f=20
         , v$tablespace t=20
         , v$parameter p=20
         , v$sysstat s=20
   where f.file# =3D fs.file#=20
     and f.ts# =3D t.ts#=20
     and t.name =3D 'TEMP'=20
     and p.name=3D'db_block_size'=20
     and s.name =3D 'sorts (disk)'=20

group by p.value, s.value;=20

HTH=20 Djordje=20

Original Message -----=20 To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>=20 Sent: Wednesday, November 29, 2000 5:09 PM=20

> Really, I didn't work with such a big temp tablespace, but=20
> I recommend you creating it as a temporary tablespace because oracle =

behaves different for allocating sort extents at temporary = tablespaces.=20
> Good luck.=20

  >=20
  >=20
  >=20

> On Wed, 29 Nov 2000 07:30:32 -0800 "Charlie Mengler" =
<charliem_at_mwh.com>=20
 wrote:=20
> > Oracle V7.3.4.3 on Solaris V2.6=20
> >=20
> > The volume of data I'm required to support has just increased=20
 significantly.=20
> > I've been force to increase the size of TEMP to around 16GB to =
support=20
> > index creation on a new LARGE table. TEMP is/was configured as -=20
> >=20
> >=20
> > SQL> select * from dba_tablespaces where tablespace_name =3D =
'TEMP';=20
> >=20
> > TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT =
MIN_EXTENTS=20
 MAX_EXTENTS PCT_INCREASE STATUS CONTENTS=20 >=20
> ------------------------------ -------------- ----------- =
----------- ----=20

------------ --------- ---------=20
> > TEMP 1048576 2097152 =
1=20 505 1 ONLINE PERMANENT=20
> >=20
> > Now I'm getting errors involving hitting the 505 MAXEXTENT limit.=20
> > I know I can increase this limit, but I suspect that the values=20
> > INITIAL & NEXT should also be increased.=20
> >=20
> > What values do you have for TEMP where it is sized in the 10 - 40 =
GB=20 range.=20
> >=20
> >=20
> > --=20
> > Charlie Mengler Maintenance Warehouse=20
> > charliem_at_mwh.com 10641 Scripps Summit Ct=20
> > 858-831-2229 San Diego, CA 92131=20
> > You don't know what you don't know. Think about it.=20
> > --=20
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com=20
> > --=20
> > Author: Charlie Mengler=20
> > INET: charliem_at_mwh.com=20
> >=20
> > Fat City Network Services -- (858) 538-5051 FAX: (858) =
538-5051=20
> > San Diego, California -- Public Internet access / Mailing =
Lists=20
> > =
--------------------------------------------------------------------=20
> > To REMOVE yourself from this mailing list, send an E-Mail message=20
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and =
in=20
> > the message BODY, include a line containing: UNSUB ORACLE-L=20
> > (or the name of mailing list you want to be removed from). You =
may=20
> > also send the HELP command for other information (like =
subscribing).=20
> --=20
> Please see the official ORACLE-L FAQ: http://www.orafaq.com=20
> --=20
> Author: Emine ATES=20
> INET: emineates_at_postmaster.co.uk=20
>=20
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051=20
> San Diego, California -- Public Internet access / Mailing =
Lists=20
> -------------------------------------------------------------------- =

> To REMOVE yourself from this mailing list, send an E-Mail message=20
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in=20
> the message BODY, include a line containing: UNSUB ORACLE-L=20
> (or the name of mailing list you want to be removed from). You may=20
> also send the HELP command for other information (like subscribing). =

  --=20
  Please see the official ORACLE-L FAQ: http://www.orafaq.com=20   --=20
  Author: djordjej=20
    INET: djordjej_at_home.com=20

Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051=20 San Diego, California -- Public Internet access / Mailing Lists =

  --------------------------------------------------------------------=20

To REMOVE yourself from this mailing list, send an E-Mail message=20 to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in=20 the message BODY, include a line containing: UNSUB ORACLE-L=20 (or the name of mailing list you want to be removed from). You may=20 also send the HELP command for other information (like subscribing).=20

------=_NextPart_000_002A_01C05B39.6BF5ACA0 Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>RE: TEMP Tablespace</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =

charset=3Diso-8859-1">

<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi Lisa,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I agree with =

Djordje. </DIV>
<DIV> </DIV> <DIV>But if you have already created TEMP = tablespace=20
with 16Gb as requried.. and would not like to drop and recreate with all = that is=20
suggested... </DIV>
<DIV> </DIV> <DIV>It seems you have TEMP tablespace of = the type=20
PERMANENT, suggested and preferred is TEMPORARY instead of PERMANENT. ( = What=20
does the LIST Guru's say ? )</DIV>

<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Now, there can be a workaround for your =

problem..=20
You can always.. change the NEXT of TEMP tablespace.. </DIV> <DIV>Whenever TEMP tablespace is used.. NEXT = extent is=20
used for allocation and not INITIAL, whereas in all other tablespaces = INITIAL is=20
used for allocation.  (I hope = that is why,=20
it is told to have both Initial and Next of them of the same = size).</DIV>
<DIV> </DIV> <DIV>One advantage you may have, as the TEMP = tablespace=20
is of the PERMANENT... .. SMON will COALESCE freespaces and you will = have bigger=20
chunks. which are free.. .. While if it is TEMPORARY then, the free = pieces are=20
marked as free.. and ORACLE internally uses them.. but DOES NOT release = them and=20
show them to be FREE.</DIV>
<DIV>So, if you have used 90 % of your TEMP = Tablespace=20
with TEMPORARY.. you will always see it as 90% used.. while if it of the = type=20
PERMANENT.. and you have released 90 % of it.. while you are using only = 10%, at=20
some point of time you will find that your tablespace is approx. 90%=20

free.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>In both the cases .. all the free =

pieces are being=20
used and managed.</DIV>

<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I am trying to explain..&nbsp; be =
patient..... Is=20

it confusing ??</DIV>
<DIV> </DIV> <DIV>Why I told you this is.. SIMPLE.. if = you had=20
smaller NEXT intially.. and type TEMPORARY ..with 90 % of = TS  USED,=20
 even if you increase NEXT to be big enough.. You will not be able = to take=20
advantage.. and will end up getting error messages.. because the largest available chunk could be maximum = 10%.</DIV>
<DIV> </DIV> <DIV>Being PERMANENT..  just change the = NEXT to a=20
sufficiently big value.. but take care it should be the same.. as told = by <FONT=20
face=3D"Times New Roman">Djordje ...  =20 N*SORT_AREA_SIZE+DB_BLOCK_SIZE  where N can be any number..  = <FONT=20
face=3DArial>Once there are enough big free pieces you may be able = to create=20
the index.</DIV>
<DIV> </DIV> <DIV>PCTINCREASE would be preferred to be ZERO (0) for = temporary=20
tablespace. </DIV>

<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>(Again, it is case to case basis)....</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>LIST Guru's&nbsp; please respond this&nbsp;with your =

suggestions / corrections and experience...</DIV>

<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>HTH</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Nikunj</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<BLOCKQUOTE dir=3Dltr=20

style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; = BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"> <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV> <DIV=20
 style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: = black">From:=20
 <A title=3Dlkoivu_at_qode.com href=3D"mailto:lkoivu_at_qode.com">Koivu, = Lisa</A> </DIV>
 <DIV style=3D"FONT: 10pt arial">To: <A = title=3DORACLE-L_at_fatcity.com=20
 href=3D"mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list = ORACLE-L</A>=20
 </DIV>
 <DIV style=3D"FONT: 10pt arial">Sent: Thursday, November 30, = 2000 04:45=20
 AM</DIV>
 <DIV style=3D"FONT: 10pt arial">Subject: RE: TEMP = Tablespace</DIV>

  <DIV><BR></DIV>
  <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=20
 blocks, isn't it? 
 Lisa Rutland Koivu Oracle = Database=20
 Administrator Qode.com 4850=20
 North State Road 7 Suite G104 = <FONT=20
 size=3D2>Fort Lauderdale, FL  33319 V: 954.484.3191, x174 F: = 954.484.2933=20
 C: 954.658.5849 <A =

target=3D_blank =
href=3D"http://www.qode.com">http://www.qode.com</A> "The information contained herein does not express = the opinion=20
 or position of Qode.com and cannot be attributed to or made binding = upon=20
 Qode.com." 
 -----Original Message----- From:=20
 djordjej [<A=20
 href=3D"mailto:djordjej_at_home.com">mailto:djordjej_at_home.com</A>] = <FONT=20
 size=3D2>Sent: Wednesday, November 29, 2000 6:50 PM To:=20
 Multiple recipients of list ORACLE-L Subject: Re: TEMP=20
 Tablespace 
 The size on INITIAL and NEXT for the TEMP tablespace = should be=20
 the same, and should be=20 N*SORT_AREA_SIZE+DB_BLOCK_SIZE, and PCTINCRESE should be 0.  The = N=20
 from above is usually 3 (to be able to accomodate = three=20
 contents of the sort memory area), but it = depends on=20
 the average number of sort runs (merge phases) in your=20
 sorts.  So if you have huge sorts with a lot of sort runs=20 you would like to have N larger but if you have a = large=20
 number of sorts that run concurrently are = each not=20
 that large, you would like to go with larger number of=20
 smaller sort segments.

  <P><FONT size=3D2>The size of the average sort you can find from the=20
  query:</FONT> </P>
  <P><FONT size=3D2>select sum(fs.PHYBLKWRT)*p.value/s.value</FONT> =

<FONT=20
 size=3D2>  from v$filestat fs <FONT=20 size=3D2>       , v$datafile f = <FONT=20
 size=3D2>       , v$tablespace t = <FONT=20
 size=3D2>       , v$parameter p = <FONT=20
 size=3D2>       , v$sysstat s = <FONT=20
 size=3D2> where f.file# =3D fs.file#    and=20
 f.ts# =3D t.ts#    and t.name =3D = 'TEMP'=20
    and p.name=3D'db_block_size' = <FONT=20
 size=3D2>   and s.name =3D 'sorts (disk)' <FONT=20 size=3D2> group by p.value, s.value;

  <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:=20
 Multiple recipients of list ORACLE-L = <ORACLE-L_at_fatcity.com>=20
 Sent: Wednesday, November 29, 2000 5:09 PM = 
 > Really, I didn't work  with such a big = temp=20
 tablespace, but > I recommend you = creating it as a=20
 temporary tablespace because oracle behaves = different=20
 for allocating sort extents at temporary tablespaces. <FONT =

size=3D2>> Good luck. > = <FONT=20
 size=3D2>> > > On Wed,=20
 29 Nov 2000 07:30:32 -0800 "Charlie Mengler" = <charliem_at_mwh.com>=20
 wrote: > > Oracle = V7.3.4.3 on=20
 Solaris V2.6 > > >=20
 > The volume of data I'm required to support has just = increased=20
 significantly. > > = I've been=20
 force to increase the size of TEMP to around 16GB to support = <FONT=20
 size=3D2>> > index creation on a new LARGE table. TEMP is/was = configured=20
 as - > > >=20
 > > > SQL> select * from = dba_tablespaces=20
 where tablespace_name =3D 'TEMP'; > = >=20
 > >=20
 =
TABLESPACE_NAME         &nbs= p;     =20
 INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS=20
 PCT_INCREASE STATUS    CONTENTS <FONT=20 size=3D2>> > =

------------------------------=20
  -------------- ----------- ----------- ----</FONT> <BR><FONT =

size=3D2>-------=20

--------- --------- > >=20 = TEMP           &nb= sp;           &nbs= p;         =20 1048576    =20 2097152           = 1=20 <FONT=20 = size=3D2>505          &= nbsp; 1=20 ONLINE    PERMANENT > = >=20 > > Now I'm getting errors involving hitting = the 505=20 MAXEXTENT limit. > > I know I can = increase this=20 limit, but I suspect that the values > = > INITIAL=20 & NEXT should also be increased. > = >=20 > > What values do you have for TEMP where it = is sized=20 in the 10 - 40 GB range. >=20 > > > > >=20 -- > > Charlie=20 = Mengler           =    =20 Maintenance Warehouse > >=20 = charliem_at_mwh.com         &nb= sp;   =20 10641 Scripps Summit Ct > >=20 = 858-831-2229          &= nbsp;      =20 San Diego, CA 92131 > > You don't know = what you=20 don't know. Think about it. > > = --=20 > > Please see the official ORACLE-L FAQ: <A=20 target=3D_blank = href=3D"http://www.orafaq.com">http://www.orafaq.com</A>=20 > > -- > > = Author:=20 Charlie Mengler > >   INET:=20 charliem_at_mwh.com > > <FONT=20 size=3D2>> > Fat City Network Services    -- = (858)=20 538-5051  FAX: (858) 538-5051 > > = San=20 Diego, California        -- Public = Internet=20 access / Mailing Lists > >=20 = --------------------------------------------------------------------</FON= T>=20 > > To REMOVE yourself from this mailing = list, send an=20 E-Mail message > > to: = ListGuru_at_fatcity.com=20 (note EXACT spelling of 'ListGuru') and in > >=20 the message BODY, include a line containing: UNSUB ORACLE-L = <FONT=20 size=3D2>> > (or the name of mailing list you want to be removed =

from).  You may > > also send the = HELP=20
 command for other information (like subscribing). >=20
 -- > Please see the official ORACLE-L = FAQ: <A=20
 target=3D_blank =
href=3D"http://www.orafaq.com">http://www.orafaq.com</A>=20 > -- > Author: = Emine=20
 ATES >   INET:=20 emineates_at_postmaster.co.uk > = <FONT=20
 size=3D2>> Fat City Network Services    -- (858)=20 538-5051  FAX: (858) 538-5051 > San = Diego,=20
 California        -- Public = Internet access=20
 / Mailing Lists >=20 =

--------------------------------------------------------------------</FON=

T>=20
 > To REMOVE yourself from this mailing list, = send an=20
 E-Mail message > to: ListGuru_at_fatcity.com = (note=20
 EXACT spelling of 'ListGuru') and in > = the message=20
 BODY, include a line containing: UNSUB ORACLE-L >=20
 (or the name of mailing list you want to be removed from).  You=20 may > also send the HELP command for = other=20
 information (like subscribing). -- Please see the official = ORACLE-L=20
 FAQ: <A target=3D_blank=20
 href=3D"http://www.orafaq.com">http://www.orafaq.com</A> = <FONT=20
 size=3D2>-- Author: djordjej = <FONT=20
 size=3D2>  INET: djordjej_at_home.com Fat City Network Services    -- (858) =

538-5051  FAX: (858) 538-5051 San = Diego,=20
 California        -- Public = Internet access=20
 / Mailing Lists <FONT=20
 =

size=3D2>----------------------------------------------------------------=

Received on Fri Dec 01 2000 - 03:52:54 CST