Temp tablespace sizing guidelines.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Temp tablespace sizing guidelines.

  1. #1
    Join Date
    Feb 2004
    Posts
    14

    Temp tablespace sizing guidelines.

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  3. #3
    Join Date
    Mar 2002
    Posts
    534
    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):

    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width