Re: sort area size

On Sat, 22 Feb 2003 18:37:06 +0100, "emerald" <emerald_at_net.hr> wrote:

>SORT_AREA_SIZE determins the maximum amount of memory that can be given to
>each user for performing sorts. If a sort needs more space than
>SORT_AREA_SIZE, TEMP tablespace is used.
>In your database, each user can get 65536 bytes for sorting. If you had 200
>users, than they could allocate 200*65536 bytes at a time. Consider that, if
>you plan to increase this parameter.
>SORT_AREA_RETAINED_SIZE is the memory which user holds on the server even if
>he is not sorting at the moment. Since it is equal to SORT_AREA_SIZE, your
>users always hold 65536 for sorting.
>If you increase sort_area_size to, say, 512k, the situation would be like
>this: when user logs on, he gets 64k for sorting. When performing sort, he
>can allocate up to 512k for sorting operations (order by, group by). After
>sorting is done, he holds 64k (sort_area_retained_size), and releses the
>rest.
>
>
>
>manisiva2002 <member25406_at_dbforums.com> wrote in message
>news:2561721.1045901390_at_dbforums.com...
>>
>> How is a sort area size calculated.The size 65536 given to
>> sort_area_size in the init parameter and sort_area_retained_size which
>> is again 65536 idicates what ?
>> Can anyone explain this
>> Thanks
>> ManiSiva
>>
>> --
>> Posted via http://dbforums.com
>

From a similar thread in comp.databases.oracle.server

It is just simply incorrect. The sort_area_size is used during the initial phase of the sort, true enough. It starts at zero, true enough,
and the sort_area_size setting is what it can maximally grow to. But, assuming a sort has written several sort runs down to TEMP, those separate
sort runs have then to be merged into a completed sort. It is at this point, during the merge phase, that the sort area can shrink to whatever
the sort_area_retained_size is set to. That is, S_A_R_S is there to define
the amount of memory used during the merge phase. And (here's the thing!)
at the end of the merge phase, the sort area returns to ZERO. Always. Nothing is 'retained' just in case the user fancies doing another sort.

Ever.

BUT... and here's the other thing, whilst the sort area shrinks back to zero,
the memory is NOT returned to the operating system (and the bit of Oracle
documentation that Chuck quoted doesn't say it will be returned to the O/S, either). Therefore, you could well get out of memory errors when no
user has any sort area size: because it was *once* allocated.

Bear in mind that in Windows everything is running inside a single process
anyway, and processes have a maximum amount of memory they can reference.

Also bear in mind that if you do any degree of parallel query, or non-parallel joins on unindexed tables, then it is possible that the execution plan will require two sorts to take place simultaneously, and
that two lots of sort_area_size will therefore be required (and you should
multiple THAT by the degree of parallelism if any). So you could be allocating far more sort_area_size than you were anticipating for this reason alone.

Regards
HJR which basically contradicts your statements

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Feb 22 2003 - 12:46:12 CST