Free Space in Temporary Tablespace Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Free Space in Temporary Tablespace

  1. #1
    Join Date
    Oct 2001
    Hi All ,

    The database in our organisation have around 350 MB
    of Temporary tablespace. I often run the query

    select tablespace_name , sum(bytes)/(1024* 1024)
    from dba_free_space group by tablespace_name

    I always see the tempory tablespace having less than
    5MB of Free space even when there is no transactions for
    a long time. As far as I have understood temporary
    tablespace are used, mostly for transactions requiring
    sort operation that does not fit in SGA memory and is
    released when the transaction is complete.

    My questions are

    Is it something normal ?
    Does it have any negative impact on the performance ?
    If so then how to release space other than dropping
    and recreating the temporary tablespace ?

    My storage parametes for temporary tablespace are
    Inital 400 K , Next 400 K , Pctincrease 0 , & AutoExtend enabled.

    Thanking you in advance

    Oracle DBA
    National Bank Of Kuwait

  2. #2
    Join Date
    Oct 2000
    Can you bounce your DB and then check the free space ? Also what is the max_extents of your temp tablespace ?
    also check out your sort_area_size init.ora parameter.
    You are right about temp tablespaces and they are used in sort operations as well as index rebuilds.


  3. #3
    Join Date
    Feb 2001
    Paris, France
    it is a normal behaviour for Oracle to allocate as much space as possible in temp tablespace, and to manage it, freeing space when needed.
    if you REALLY want to flush your tablespace, then :

    alter tablespace TEMP default storage (pctincrease 1);
    alter tablespace TEMP default storage (pctincrease 0);
    alter tablespace TEMP coalesce;

  4. #4
    Join Date
    Sep 2001
    Fort Smith
    Default Storage specification for your TEMPORARY tablespace:
    Use the following guidelines to specify DEFAULT STORAGE:
    Set INITIAL=NEXT.Since a process always writes data equal to
    SORT_AREA_SIZE to a temporary segment, a good value for the extent
    size is (n*s + b)
    where: n is a positive integer
    s is the value of SORT_AREA_SIZE initialization parameter
    b is the value of DB_BLOCK_SIZE initialization parameter
    Using this value optimizes temporary segment usage by allowing
    sufficient space for a header block and multiple sort run data to be
    stored in each extent.

    Specify a PCTINCREASE of zero to ensure that all extents are of the
    same size.

    The MAXEXTENTS parameter only affects a temporary segment if the
    tablespace is a PERMANENT tablespace."

  5. #5
    Join Date
    Sep 2001
    Fort Smith
    here is the maths for sizing extents from metalink

    You calculate the estimated space for TEMPORARY segments as you would for
    any other tables. The worse case will to the same size as table A (the 4
    million row table). If the GROUP BY of 26 columns is a subset of table A,
    then it will be smaller. To calculate do the following:

    block overhead = 24 (cache header) + 19 (fixed itl header) + 14 (data hdr)
    = 57 bytes

    Note: the 4 bytes at the end of the block have already been taken account
    of in the 24 bytes for the cache header.
    add 4 bytes per table directory entry. (4 bytes only if table not
    not clustered)

    block size - block overhead - table dir - block - free space

    So for example, at 10% free space with 2048 block size and 57 bytes of
    overhead (assuming 1 itl), non-clustered we get

    bas = 2048 - 57 - 23 - 4 - 2x - (2048 - 57 - 23 * .1)
    = 1964 - 2x - 196 ^^^^^^^^^^^^^^^^^^^
    = 1768 - 2x calculated on block - some overhead stuff

    #r/b = x = 1768 - 2x - x * max(9, avg row size)

    assume avg row size = 3


    2x + 9x = 1768
    x = 1768 / 11
    = 160

    therefore, #blocks = #rows / #r/b

    So for 10000 rows, #blocks = 10000 / 160
    = 62 blocks

    For your 27 column group by you will need to come up with a row length.
    This may be larger than the 2048 byte block size. Do the following

    assume avg row size (which note includes space for column overhead and row
    flags) is 4000 bytes. Then

    #r/b = 1768 - 2x - 4000x
    --> 4002x = 1768
    x = 1768 / 4002
    x = .441

    --> 1 / .441 = # blocks/r

    2. How does the sum clause operate in this situation and how much space would
    it need? (the size of the table, etc..)

    I doubt that the sum function will need much space, it will be a running
    total after the group by is computed off the temporary segment.

    3. Same as #2 but with group by's. I guess what he is hoping for is a high
    level algorithm as to what operations are involved and how they are

    Use the formulas outlined under Q1 above. Anytime you use a GROUP BY,
    ORDER BY, or join tables, you will need a sort area size and if the
    sort/join cannot be computed in memory (number of rows in table will
    not all fit into the memory allocated by SORT_AREA_SIZE), a temporary
    segment will be created.

  6. #6
    Join Date
    Jun 2001
    Helsinki. Finland
    I suggest you create the TEMP tablespace using a tempfile:


  7. #7
    Join Date
    Oct 2001
    Your problem is very normal and i think julian's suggestion is your best choice. When temporary tablespace is created with TEMPORARY type, the space will not be released until the instance is shutdown. If it is created with PERMANENT type, then SMON will do the cleanup. However, if there are large number of sort segments in your temporary tablepace, SMON may take more time to do the housekeep and your perfermance maybe affected. So the best choice is create locally managed temporary tablespace which benifits of faster cleanup and reduces contention CPU usage by SMON.

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