RE: Temporary Tablespace Design

I would suggest that you increase (as much as possible) sort_area_size and sort_area_retained_size for your session when building indexes to minimize temporary tablespace use. Making temporary tablepspace of type temporary and adjusting default initial & next extent size can also help. HTH..
- Kirti Deshpande
  Verizon Information Services
   http://www.superpages.com

> -----Original Message-----
> From: CC Harvest [SMTP:ccharvest_at_yahoo.com]
> Sent: Thursday, April 12, 2001 11:23 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re:Temporary Tablespace Design
>
> Thanks Dick and Lisa for answering my question. I
> think I am going to either let the file auto-extend,
> or will try a smaller file as a start. I found my temp
> tablespace is too small(1GB) because it seems like
> takes forever to rebuild an index with nologging. I
> have 11 indexes on this tables, and it took me tons
> hours to do the index rebuilding. My application is
> a mixed system with 10% batch processing, and 90%
> OLAP.
> But we need the 10% batch processing part should be
> really fast.
>
> Thanks,
>
> Chris
>
> --- dgoulet_at_vicr.com wrote:
> > Chris,
> >
> > First let me say that I have a TON of respect
> > for Mike and count him as a
> > friend. That said, I also take exception to many of
> > his pronouncements from a
> > practical, not theoretical, point of view. Given
> > infinite resources, like disk
> > space and memory and CPU, he does have it absolutely
> > right. But in the real
> > world there is infinite nothing.
> >
> > The first item on my list here it to look at
> > what temp space is used for.
> > It's mainly used for sorting, grouping, and distinct
> > operations. These are the
> > normal things that involve temp segments, and in a
> > day to day operation that
> > will consume an amount of space. The other item
> > their used for is index
> > building, which is not a normal day to day
> > operation. Therefore the need for an
> > extremely large temp tablespace is a sporadic and
> > plan able event. Second,
> > comes the question of the purpose of the database.
> > If your building an OLTP
> > system then temp usage is going to be even less
> > since the majority of actions
> > will affect few rows at one time. If it's a data
> > warehouse on the other hand
> > then data mining operations tend to make extreme use
> > of temp for group and sort
> > operations, but even so the amount of data being
> > processed will not hit the
> > extremes and when it does it's most likely bogus in
> > the first place. My
> > favorite in this vein is our CIO who let loose a
> > Cartesian product query just
> > because he forgot to join the fact table to the
> > other tables. In this case the
> > lack of temp space brought the query to a halt
> > quickly and mercifully.
> >
> > OK, so where should you go? Well, I'll get into
> > our DB's which range from
> > our 150GB data warehouse to our 200GB operational
> > data store. The former has
> > 1GB of temp storage for normal operations. The
> > latter gets along very well on
> > 400MB of temp space. Both have a 14GB disk area
> > that they share as required for
> > those monster index rebuilds.
> >
> > Where you go from here is a lot of personal
> > decision. I recommend starting
> > small & working your way up as necessary. The
> > easiest way to do that is to
> > enable auto-extend.
> >
> > Dick Goulet
> >
> > ____________________Reply
> > Separator____________________
> > Author: CC Harvest <ccharvest_at_yahoo.com>
> > Date: 4/12/2001 12:05 AM
> >
> > What's your experience about the temporary table
> > design? I read Michael Ault's Orcale8 Administartion
> > and Management , it says "For Cost-based
> > optimization,
> > it should be 4 times of the largest table". I have a
> > table of 60 Million records, and it costs 16GB,
> > should
> > I have a 64GB temp tablespace(I don't think so,
> > though
> > it's a 100GB database, and I have a 300GB of
> > diskspace).
> >
> > Thanks for your advice.
> >
> > Chris
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Get email at your own domain with Yahoo! Mail.
> > http://personal.mail.yahoo.com/
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: CC Harvest
> > INET: ccharvest_at_yahoo.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:
> > INET: dgoulet_at_vicr.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).
>
>
> __________________________________________________
> Do You Yahoo!?
> Get email at your own domain with Yahoo! Mail.
> http://personal.mail.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: CC Harvest
> INET: ccharvest_at_yahoo.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: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.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).