Fragmentation of Temporary Tablespace?

[Catcox]

My one temporary tablespace (size 157 megs) seems to be very fragmented
according to the following query.

The following is from page 245 of The Oracle DBA Handbook by Oracle Press.

1 select
2 tablespace_name,
3 sqrt(max(blocks)/sum(blocks))*
4 (100/sqrt(sqrt(count(blocks)))) fsfi
5 from dba_free_space
6 group by tablespace_name
7* order by 1
SQL> /

TABLESPACE FSFI
---------- ----------
DATA1 13.8439021
INDEX1 27.5638636
ROLLBACK 34.3932208
SYSTEM 51.1341948
TEMP 9.60729838

The book says that the largest fsfi is 100 and that below 30 is bad so clearly
I have some problems.

My question is why does the temp tablespace remain no less fragmented when
there are only a couple of users logged on? This fsfi rating also doesn't
change too much for the TEMP tablespace over the course of the day. Also
dba_segments shows 0 segments in this TEMP tablespace, so what's in there?
There aren't any tables or indexes in it.

I am at the same time getting a lot of:

ORA-01575: timeout waiting for space management resource

in my alert.log, but have posted a separate message on this.

Thank you,

Catherine Cox
cat...@aol.com

[Matthias Gresz]

On 6 Feb 1998 00:44:12 GMT, cat...@aol.com (Catcox) wrote:
Hi Catherine,

I think your temporary tablespace is to small to reach the needs of your users.
Since temporary tablespace is used when sorting and joining does not fit in SGA,
you should determine what kind of queries are run, estimate the space
requirements for the largest queries estimating the concurrencie of the queries
and thus getting a an idea of the space you need. If you've many user think of
creating several temporary tablespaces each for a distinct group of users thus
reducing contention.
--

Regards

Matthias Gresz :-)

--

Regards

Matthias Gresz :-)

[Rachel Carmichael]

Another thing to check is the number of sorts to disk vs the number of sorts in
memory. You should be doing more of your sorts in memory than to disk... more
efficient that way. To check, run the query:

select a.name, value
from v$statname a, v$sysstat b
where a.statistic#=b.statistic#
and a.name in ('sorts (disk)','sorts (memory)','sorts (rows)');

this will tell you the number of rows sorted and how many of the sorts were to disk
and to memory. If you have a large proportion of disk sorts to memory sorts,
increase your sort_area_size

Rachel

[David Shi]

I think you have a lot of continuous free extents in the temp tablespace which
need to be manually coalesced (alter tablespace temp coalesce).

David