RE: Temporary Tablespace Design

Hi Chris,
 The entire sort may not take place in the memory, but having more memory for sorting minimizes disk usage, which can lead to disk I/O, space mgmt issues and may hamper performance. If there is enough free memory available when building indexes, it is okay to use it, as much as possible, to speed up the sorting.
 Another thing to consider is properly sizing the initial, next extents for temp segments. Initial = next and ideally they should be some multiples of the SORT_AREA_SIZE and + 1 db block size (some argue about this 1 extra db block size as unnecessary if the block overhead is built into sizing the data file itself). Oracle writes one sort area size worth of information to disk when needed. Proper sizing of the extents minimizes space mgmt tasks. Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the former will be used while fetching data after sort phase is completed. 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:30 AM
> To: kirti.deshpande_at_verizon.com
> Cc: ORACLE-L_at_fatcity.com
> Subject: RE: Temporary Tablespace Design
>
> Kirti:
> The server is solely used for the Oracle Database
> and it has 2GB Ram. I didn't increase sort_area_size
> too much because I thought the table is so big, and
> probablay I could not run them in the memory. That's
> why I just try the temp tablespace.
>
> Thanks,
>
> Chris
>
> --- "Deshpande, Kirti" <kirti.deshpande_at_verizon.com>
> wrote:
> > 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).
> > >
> >
> === message truncated ===
>
>
> __________________________________________________
> 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: 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).