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).
Received on Thu Apr 12 2001 - 10:19:27 CDT
