100MB Sort Area Size

[ligang]

I am tuning a oracle8.0.5 database . I am surprised discover that in the original initial file , the sort area size is set 100MB (even though the temporary tablespace is only 100MB) .

I think it's very wrong as the sort area size is created in UGA and it may consume more Server memeory .


How do you think ? Any suggestion

[sandycrab]

100 MB sort area size is quite reasonable. There are a Dbs where we use more then that say 200MB also. Only if there is not enough space in Sort area size then temporary segment is used by oracle.
Sandy

[pando]

normally more than 30 ~ 40 mb sort_area_size wont yield you extra benefits

[SANJAY_G]

SORT_AREA_SIZE parameter is per session so you should also consider no. of concurrent sessions..
100MB sort_area_size with 10 sessions will need 1GB of memory and if not available can cause heavy swaps..

Sanjay

[grjohnson]

Yes, large SORT_AREA_SIZE 's with a high number of concurrent sessions can bring a server to it's knees.

You should

select * from v$license;

You check your concurrent highwater mark and routinely check to see what is sorting via

select * from v$sort_usage;

Cheers,

[ligang]

I think v$license shows the high water mark of number of session connected . But sort area size is only required in the active session .

Any places to record the HWM of active concurrent session?

[SANJAY_G]

Originally posted by: ligang
I think v$license shows the high water mark of number of session connected . But sort area size is only required in the active session .

Any places to record the HWM of active concurrent session?

select current_utilization, max_utilization from v$resource_limit
where resource_name = 'sort_segment_locks';

Sanjay

[nagarjuna]

Having greater sort_area_size values will surely lead to performance problems during normal activity. When user connect to database, he is allocated some amount of memory for UGA ( this value depends on sort_area_size, maxdatafiles, opencursors etc). During normal database activity, the no. of users are high, so the requirement for memory. This leads to heavy amount of swapping space affecting the database performance. So, a sort_area_size value of 5mb or less should be good for normal database activity, and ask the developers to tune thier queries

Offcourse, sort_area_size of 500mb is good when we go for import or large index creation. These kind of activities should be planned and worked on.

Again, this is where human touch comes to play....

-nagarjuna

[pando]

I am not sure if you have tried but if you set

sort_area_size to 32MB
and compre it to a sort_area_size of 64MB

there is virtually no performance gain

so setting sort_area_size to 500mb is pretty useless

[jmodic]

Originally posted by pando
so setting sort_area_size to 500mb is pretty useless

... and also pretty harmless, I would add (despite what others have written about "large sort size + a lot of concurrent session = a great amount of memory reserved for sorting" - this is just another one of those old myths....)