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: 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).
Received on Thu Apr 12 2001 - 11:51:32 CDT
