Description
===========
When monitoring the size of the available free space in the TEMPORARY tablespace
it does not increased once sorts are finished even with no client connections
to the database. They may also be a large number of EXTENTS of type TEMPORARY
in the temporary tablespace.
Users may also experience ORA-1652 when running transactions. This error implies
that there is no free space left. SMON is not cleaning up after waiting for
hours and queries are failing.
Scope & Application
===================
If the TEMPORARY TABLESPACE is of type TEMPORARY, the space is not released
until the instance is shutdown. If the TEMPORARY TABLESPACE is of type
PERMANENT, then cleanup is performed by SMON after completion of the process
using it.
A sort segment is created by the first statement that uses a TEMPORARY
tablespace for sorting, after startup. These are not released until the
database is restarted. The view V$SORT_SEGMENT can be used to see how
many of the currently allocated sort segments are being used.
If a PERMANENT tablespace is used for sorting, then any temporary segments
created are dropped by SMON on completion of the statement, and the space
is released for use by other objects.
Running the query:
select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE ,
TABLESPACE_NAME
from DBA_SEGMENTS
where SEGMENT_TYPE = ‘TEMPORARY’;
to give results similar to:
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
—– ———— ———— —————
SYS 4.2 TEMPORARY TEMP
Note: segment_name and tablepace_name are likely to be different.
Then determine the tablespace type by running:
select TABLESPACE_NAME,
CONTENTS
from DBA_TABLESPACES
where TABLESPACE_NAME in ();
to give results similar to:
TABLESPACE_NAME CONTENTS
—————————— ———
TEMP TEMPORARY
TEMP1 PERMANENT
The above query will indicate what type of tablespace the database has defined.
Explanation
===========
If the tablespace in which the TEMPORARY segment resides is of type
TEMPORARY then sort segments not being released is normal behavior in 7.3+.
ORA-1652 implies that the tablespace is too small.
A sort segment is created by the first statement that used a TEMPORARY tablespace
for sorting, after startup. A sort segment created in a TEMPOARY tablespace
is only released at shutdown. The large number of EXTENTS is caused when the
STORAGE clause has been incorrectly calculated.
If the tablespace in which the TEMPORARY segment resides is of type
PERMANENT, then check that following events are not set in the initalisation
parameter file:
event=”10061 trace name context forever, level 10″
event=”10269 trace name context forever, level 10″
If they are set, comment out the lines and restart database.
These two events prevent SMON from cleaning up TEMP segments and
coalescing. In Oracle Version 7.0, only event 10061 existed and if
set prevented SMON from both cleaning up TEMP segments and coalescing.
From oraus.msg:
10061, 00000, “disable SMON from cleaning temp segments
10269, 00000, “Don’t do coalesces of free space in SMON”
// *Cause: setting this event prevents SMON from doing free space coalesces
As user sessions require space for sorting, they will be allocated temporary
segment(s) from the user’s temporary tablespace. If the user’s temporary
tablespace is of type PERMANENT and these two events are set, then the session
may eventually run out of space and any new sessions requiring space in the
same tablespace may fail with “ORA-1652: unable to extend temp segment by %s
in tablespace %s”.
For performance issues, tablespaces marked as temporary allocate extents
as needed, but the extents are not released or deallocated after the operation
is done. Instead, the extent is simply marked as FREE or available for the
next sort operation. This eliminates the need to continuously allocate and
deallocate extents after every sort operation, hence gaining performance
benefits.
To find out if a TEMP segment is FREE or being USED and also in which
tablespace the segment resides, query the new dynamic performance view
V$SORT_SEGMENT.
For example:
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS
from v$sort_segment;
>>> DURING the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
—————- ———— ———– ———–
TEMP 590 590 0
>>> AFTER the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
————— ———— ———— ———–
TEMP 590 0 590
This query shows us that during the sort the USED_BLOCK count
increased since we are using the sort extents and after the sort is done the
USED_BLOCK decreased and FREE_BLOCK count increased because the extents got
released back to the sort extent pool.
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.
Proper calculation of the Default Storage clause parameters will increase
performance and maximize use of storage.
NOTE: By default when a tablespace is created its contents are set to
hold “PERMANENT” objects, (i.e this includes all segments including sort
segment). Sort segments residing in PERMANENT tablespace are
still cleaned up by SMON as before, after the sort is completed. If a
large number of sort segments have been created, SMON may take some
time to drop them.
To make a tablespace hold only temporary objects we need to set the
contents to TEMPORARY, by issuing the following command:
ALTER TABLESPACE
and verify this with the following query:
SVRMGR> select TABLESPACE_NAME,CONTENTS from dba_tablespaces;
TABLESPACE_NAME CONTENTS
—————- ———
SYSTEM PERMANENT
RBS PERMANENT
TEMP TEMPORARY
TOOLS PERMANENT
CYN PERMANENT
RBSTMP PERMANENT