Databases Reference
In-Depth Information
If we define the default temporary tablespace for a user, this pre-allocates a
SORT
SEGMENT
,
which is never deallocated. When there is the need for a sort operation, no sequential space
management actions (allocations) are executed to satisfy the request, resulting in a great
improvement in performance.
In fact, temporary space segments are created when the first sort occurs. They are extended
when there is a need for a greater sort area in memory, and they shrink to leave enough place
for other sort operations when these operations occur.
Optimal storage parameters for temporary tablespaces
We can use
INITIAL
and
NEXT
values as integer multiples of
SORT_AREA_SIZE
parameter and
PCTINCREASE
set to
zero
to obtain optimal performance when not
using locally managed tablespaces. However, Oracle recommends (
http://download.
oracle.com/docs/cd/B28359_01/server.111/b28274/memory.htm
) we use the
PGA_AGGREGATE_TARGET
parameter and to set
WORKAREA_SIZE_POLICY
to
AUTO
in
order to obtain the best performance.
The
PCTINCREASE
parameter cannot be specified when we use the
AUTOALLOCATE
option
for the tablespace. We can have different tablespaces for each database user and we can
query the dynamic performance view
V$SORT_USAGE
to retrieve the details about the active
disk sorts occurring in the instance.
We can obtain better performance by striping the temporary tablespace using multiple disks.
Please note that—due to their nature—temporary tablespaces are not affected by backup and
restore operations.
See also
F
There's more information on Dynamic Performance Views in
Appendix
A
,
Dynamic
Performance Views
and in
Chapter
9
,
Tuning Memory
the
Tuning
the
Program
Global
Area
and
the
User
Global
Area
recipe
