Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Temporary Tablespace Design

RE: Temporary Tablespace Design

From: Hillman, Alex <Alex.Hillman_at_usmint.treas.gov>
Date: Thu, 12 Apr 2001 10:24:28 -0700
Message-ID: <F001.002E8603.20010412100102@fatcity.com>

I think you missed analyze operation. It is for this command is 4x<table size> may be required. Also space for temporary tablespaces can be on very cheap disks.

Alex Hillman

-----Original Message-----
Sent: Thursday, April 12, 2001 10:57 AM
To: Multiple recipients of list ORACLE-L

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

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Hillman, Alex
  INET: Alex.Hillman_at_usmint.treas.gov
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 - 12:24:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US