Re: Temporary Segments

Ansers intermixed with post.

In article <8c2la4$1rn$1_at_nnrp1.deja.com>, vasarpota_at_my-deja.com wrote:
> Hi guys,
> was just reading the Oracle DBA book and wanted to clear up a couple
 of
> issues:
> 1) The SORE_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
No, sort_area_size of space is taken from OS for every user when using dedicated servers and under MTS the sort_area is part of the UGA which comes out of the shared pool. For an OLTP a sort_area_size of 64K - 128K is usually sufficient to perform 99.5+% of sorts in memory. There will always be a few sorts that go to disk. The sort area size can be modified dynamically at the session level so if you are an OLTP you can set the size to a reasonable number and bump it up for batch jobs that need a lot of sort space.

> 2) When should temporary segments be placed in Permanent Tablespaces?
Temporary mode tablespaces were new with 7.3 and should be used for sort tablespace unless you run into a problem directly related to how Oracle manages temporary tablespaces.

> 3) How is the PGA used regarding Temporary Segments and sorting if at
> all?

I believe in a dedicated server environment that the UGA is technically part of the PGA so the sort_area_size and sort_area_retained_size would be part of the PGA. See above for MTS and UGA.

> Thanks in advance for any help
>

I hope this helps.

--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.