Free Space in Temporary Tablespace

[gsmathew]

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

[vinit]

Hi,
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.


Cheers,

[pipo]

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;

[sonofsita]

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

[sonofsita]

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

then

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

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

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.

[julian]

I suggest you create the TEMP tablespace using a tempfile:


CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/datadg/disk22/temp.dbf'
SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

[xiaomao]

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.