Re: index creation fails because temp tablespace is too small

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 28 Apr 2005 20:02:08 +0200
Message-ID: <d4r8er$d6n$00$1@news.t-online.com>

Sybrand Bakker schrieb:
> On 27 Apr 2005 12:43:51 -0700, "Dutch_alien" <arian_at_bearen.demon.nl>
> wrote:
>
>

>>Most of all, it made me wonder how sort_area_size is related to it.
>>(Background: One of my customers is using W2K, and we keep running into
>>the 2/3Gb memory limit).

>
>
> sort_area_size is related in such a way: as soon as your sort exceeds
> sort_area_size, you will hit the temporary segment.
> It is pretty daft to set sort_area_size to 2 or 3 G, especially as
> this is a *per sort limit* , *NOT* a *system wide limit*.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

As sort_area_size is session modifiable parameter , for rapid index creation it is of course not so bad idea to set it to 2-3 G or even more, assumed you have enough free memory on the os level,(to take effect on 9i upwards the workarea_size_policy must be set to manual which fortunately is session modifiable too) . Moreover, in big warehousing environments such big sort_area_size can be set on the instance level - in my last project i worked on the system with 42 G memory total, db_cache_size was set to 800 M and sort_area_size to 500 M, overall performance was ok, but i can as well imagine systems with significantly more memory...

Best regards

Maxim Received on Thu Apr 28 2005 - 13:02:08 CDT