100MB Sort Area Size

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.