?: TEMP Tablespace

From: Dash, Saroj (CAP,CEF) <Saroj.Dash_at_gecapital.com>
Date: Mon, 4 Dec 2000 10:00:28 +0900
Message-Id: <10699.123491@fatcity.com>

-----???????-----

???: Nikunj Gupta [mailto:good_dba_at_hotmail.com]
????: Sunday, December 03, 2000 PM 03:50

??: Multiple recipients of list ORACLE-L
??: Re: TEMP Tablespace

Hi Lisa,

I agree with Djordje.

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...

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..
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.

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

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

HTH Nikunj

Original Message ----- From: Koivu, Lisa <mailto:lkoivu_at_qode.com> To: Multiple <mailto:ORACLE-L_at_fatcity.com> recipients of list ORACLE-L Sent: Thursday, November 30, 2000 04:45 AM Subject: RE: TEMP Tablespace

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 <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 <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

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

> 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

<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

<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
<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

Received on Sun Dec 03 2000 - 19:00:28 CST