A new mechanism to control the allocation of temporary storage space was
introduced in 7.3, initially as support for OPS, but it is equally applicable
to non-OPS environments. This requires the creation of a designated temporary
tablespace, that is one created with the designation TEMPORARY. The effect of
this is twofold:
1. No permanent objects (tables, indexes, etc.) can be stored in such a
tablespace.
2. A temporary segment created in this tablespace will not be de-allocated
at the end of the sort. Instead the extents are MARKED as free, are not
placed back on the freelist, and are retained for reuse. An element called
the Sort Extent Pool (SEP) is created in the SGA to describe them.
Subsequent sort operations will then be allocated extents from this pool
via a memory lookup, thereby avoiding most of the space management tasks
described above.
The temporary segment containing this “pool” of extents is referred to as an
“instance” and there will be one for each OPS instance opening the database.
Each instance will maintain its own SEP. The view V$SORT_SEGMENT makes these
visible. It contains information about every sort segment created in the
LOCAL instance. So, if node 1 executed a statement which created a sort
segment, node 2 will not see the entry in V$SORT_SEGMENT unless it has
already executed some statements which created a sort segment in node 2’s
instance.
The view DBA_SEGMENTS contains information about segments in the entire
database. So, if all nodes create sort segments, there will be multiple
entries in DBA_SEGMENTS, one for each node.
A single instance configuration will have just the one segment, hence one
pool of extents.
Another advantage for an OPS environment occurs when a sort requires an extent
and there are none free in the pool. The segments may grow in the usual way,
but if another instance does have a free extent, this can be re-assigned from
one pool to the other.
During a sort operation, the number of sort segment(s) created should
eventually grow to a stable point where no new extents are allocated. If
large sorts are occurring, which take up all of the available space, then
the operation may receive an ORA-1652. The solution here is to add additional
files to the tablespace.
It has also been observed that in an OPS environmemt ORA-1652 messages occur
in the alert.log while the re-assignment from one instance’s SEP free extents
to the requesting one seems to be working fine because client sessions
do not get the ORA-1652. This suggests that the re-allocation is internally
triggered by event 1652, and the ORA-1652 is not surpressed from being
written to the alert.log. This can be quite confusing for DBA’s.
SQL commands
============
1. CREATE TABLESPACE
DATAFILE …
DEFAULT STORAGE (…)
TEMPORARY;
or
ALTER TABLESPACE
TEMPORARY;
2. ALTER USER
N.B. The DEFAULT STORAGE clause governs the size of the sort extents created
within a tablespace. This should always be a multiple of the SORT_AREA_SIZE
specified in the init.ora file for each instance.