100MB Sort Area Size

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

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

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

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

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,

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?

Quote:

---

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?

---

Code:

---

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

---

Sanjay

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 :p

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

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

Quote:

---

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....)

Hi jmodic,
Can you elaborate more on this? Or any url/link ?

Sanjay

For a start:
http://www.ixora.com.au/newsletter/2000_11.htm

Thanks jmodic.

The crucial thing to consider, before deciding on SORT_AREA_SIZE is "peak number of concurrent disk sorts"

Sanjay

Sure, but on well managed systems (particulary those with large number of concurent users) that value should typicaly not be very far above 1 in any case.

rarely do you see any benefit from setting the sort_area_aize to anything larger than a few mb's, however, to be sure here's a script that will show you disk sort activity for a particular os user. You can easily change to show for a db user but in our env (peoplesh*t) everone connects to the db as the same db user so we look at the os user. If you see a high disk/mem sort ratio then you might consider upping the sort area size or if you specifice processes that perform large disk sorts you could always issue alter session set sort_area_size for that specific process.

SET VER OFF
ACCEPT usr_name PROMPT 'Enter the os user => '
SELECT substr(vs.username,1,20) "db user",
substr(vs.osuser,1,20) "os user",
substr(vsn.name,1,20) "Type of Sort",
vss.value
FROM v$session vs,
v$sesstat vss,
v$statname vsn
WHERE (vss.statistic#=vsn.statistic#) AND
(vs.sid = vss.sid) AND
(vsn.name like '%sort%')
AND vs.osuser = '&usr_name'
ORDER BY 2,3;

Quote:

---

Originally posted by jmodic
For a start:
http://www.ixora.com.au/newsletter/2000_11.htm

---

I know that article Jurij, it is a very good one. I have BTW tested a large production system (for 2 months) with SORT_AREA_SIZE equal to 10M, it is now 1M. No significant impact. Boxes we use now have enough memory so that in fact 10M sounds better to me than 1M. But 100M, that's too much.