Re: TEMP tablespace size in a Data Warehouse

Charlie,

On a 1 TB implementation, we used the "rule of thumb" of (1.5 * size of the largest table) as required temporary tablespace. The database release was in 7.3.4 and 8.0.5. This sizing was done keeping in mind, not only the need for temp segments during application sorts and index creations, but also for the potential need to perform a "full analyze" on the table with the "compute statistics" option. You are aware that a table analyze automatically induces analyze of all the relevant indexes on the said table.

Having said that, it needs to be mentioned here that after a table reaches a certain size threshold (environment-specific but usually in 10s of Gb), it is almost processor/computation prohibitive to do "computes". While 8.0 and above does allow "parallel analyzes" by the use of the dbms_utility. analyze_part_object procedure, the sheer cost of performing a compute is sometimes infeasible.

For most environments "estimates with sample sizes of 16% or above have been known to be statistically adequate". The statistical confidence interval for a 16% sample-size analyze is between 83-91%. I have used a sample size of 20% across the board for the past 5 years and it worked for me. Depending on the degree of skewness in your data, your mileage may vary. But at least you have a number to start with.

If the usual sort-related parameters have been tuned, it is relevant to mention here that the number of tables that are analyzed at a given time, is going to have a direct impact on the size of the temporary tablespace.

Best Regards,

Gaja.

• Charlie Mengler <charliem_at_mwh.com> wrote:
  > Is there any "rule of thumb" that can be used to guesstimate
  > the appropriate size
  > of the TEMP tablespace in a data warehouse instance? For
  > example
  > XXX% of total size or
  > TEMP should be 1.YY * the size of the largest table or
  > TEMP should be 1.ZZ * the size of the largest index or
  > ??????
  >
  > Along these lines what can be done to minimize or reduce the
  > amount of TEMP
  > that is needed & what are the trade-offs involved. (This is
  > beside the "obvious"
  > response WRT to sorts & Sort Area Size.)
  >
  > --
  > Charlie Mengler Maintenance
  > Warehouse
  > charliem_at_mwh.com 5505 Morehouse
  > Drive
  > 858-552-6229 San Diego, CA
  > 92121
  > Always be sincere about your enthusiasm, whether you mean it
  > or not.
  > --
  > Author: Charlie Mengler
  > INET: charliem_at_mwh.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).

  ---

Gaja Krishna Vaidyanatha   | 3460 West Bayshore Road,
Manager - Integration      | Palo Alto, CA 94303
& Consulting Services      | gaja_at_brio.com
Global Alliances           | (650)-565-4442
Brio Technology            | www.brio.com 

"Opinions and views expressed are my own and not of Brio Technology"