Re: TEMP Tablespace

From: djordjej <djordjej_at_home.com>
Date: Sun, 3 Dec 2000 12:00:29 -0500
Message-Id: <10699.123483@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_00E4_01C05D20.A17C7A00 Content-Type: text/plain;

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

RE: TEMP TablespaceThe result is in bytes - the PHYBLKWRT which is in = blocks is mutiplied by the block size taken from v$parameter.

One further comment - this query assumes that all the users are set so = that their default temporary tablespace is TEMP.

Djordje

Original Message -----=20 From: Koivu, Lisa=20 To: Multiple recipients of list ORACLE-L=20 Sent: Thursday, November 30, 2000 7: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_00E4_01C05D20.A17C7A00 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 content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2614.3500" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV>The result is in bytes - the PHYBLKWRT =
which is in=20
blocks is mutiplied by the block size taken from = v$parameter.</DIV>
<DIV> </DIV>
<DIV>One further comment - this query =
assumes that all=20
the users are set so that their default temporary tablespace is=20 TEMP.</DIV>
<DIV> </DIV>
<DIV>Djordje</DIV>
<BLOCKQUOTE=20

style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: = 0px; PADDING-LEFT: 5px; PADDING-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 href=3D"mailto:lkoivu_at_qode.com" title=3Dlkoivu_at_qode.com>Koivu, = Lisa</A> </DIV>
 <DIV style=3D"FONT: 10pt arial">To: <A = href=3D"mailto:ORACLE-L_at_fatcity.com"=20
 title=3DORACLE-L_at_fatcity.com>Multiple recipients of list ORACLE-L</A> =
</DIV>

 <DIV style=3D"FONT: 10pt arial">Sent: Thursday, November 30, = 2000 7: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 =

href=3D"http://www.qode.com" =
target=3D_blank>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 href=3D"http://www.orafaq.com" = target=3D_blank>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
 href=3D"http://www.orafaq.com" =
target=3D_blank>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 href=3D"http://www.orafaq.com"=20 target=3D_blank>http://www.orafaq.com</A> -- =

Author: djordjej   INET:=20
 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>----------------------------------------------------------------=

----=20
 To REMOVE yourself from this mailing list, send an = E-Mail=20
message to: ListGuru_at_fatcity.com (note EXACT = Received on Sun Dec 03 2000 - 11:00:29 CST