Temp tablespace sizing guidelines.

[bkerr]

I am working with a ~100 GB datawarehouse. Our largest segment is about 5GB (excluding indexes) and we are having problems running out of temporary tablespace with our batch queries and transformations. Our temporary tablespace is sized at 10GB, and our queries often run very close using all this.

Does anyone have any guidelines for Temporary tablespace sizings? Does 10GB seem to little?

Regards and thanks for any help,
Brendan.

[davey23uk]

silly answer really - but it needs to be as big as it needs to be.

best option is to find the queries and fix them so they dont do as much sorting in temp.

also look at your sort area setting - maybe they are too small

[mike9]

Hi,

I would say that 10Gb seems a bit small, but that totaly depends of the design of your warehouse/marts, the queries executed against the db and the available hash/sort or PGA memory. So I dont think that its possible, with the given information to say if yes or no 10Gb are to small.

If you are using 9i and have set WORKAREA_SIZE_POLICY to AUTO you may have a look at the following document:

Automatic PGA Memory Managment in 9i
http://metalink.oracle.com/metalink/...&p_id=223730.1

When executing queries you could also have a look at V$SQL_WORKAREA_ACTIVE using the following query (for more details see the Metalink note):

SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM, 
trunc(MAX_MEM_USED/1024) "MAX MEM",        
NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE 
FROM V$SQL_WORKAREA_ACTIVE ORDER BY 1,2;

HTH
Mike