Question:

> Hi guys,
> was just reading the Oracle DBA book and wanted to clear up a couple of
> issues:
> 1) The SORT_AREA_SIZE is a system parameter and not specific to each
> user, right? Meaning, this represents the size in memory for all users
> regarding Sorts and when exceeded temporary segments are used
> 2) When should temporary segments be placed in Permanent Tablespaces?
> 3) How is the PGA used regarding Temporary Segments and sorting if at
> all?
> Thanks in advance for
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Answer 1:

Sort_area_size is for EACH connected user - but is used incrementally -
thus you won't be using up that much memory for each person as soon as
they connect.

Answer 2:

The SORT_AREA_SIZE parameter is the maximum amount of memory given to an
Oracle session for sorting before using the temporary segments.

The parameter SORT_AREA_RETAINED_SIZE is the space that will be retained
for the results of the sort.

Lastly temporary segments should never be created in PERMANENT
tablespaces only in TEMPORARY tablespaces. This is for many performance
reasons.

Answer 3:

Just to confues the issue, there should be a permanent tablespace,
usually called TEMP, that is used just for temporary segments. The
other, 'Permanent', tablespaces hold the data.

Answer 4:

I don't think TEMP should be a permament tablespace.

Ideally it should be

- temporary
- raw (to avoid inode locking problems on Unix)
- based on a tempfile so it doesn't worry backup/recovery
- locally managed so it doesn't do nasties to space transactions