Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle SORT_AREA_SIZE

Oracle SORT_AREA_SIZE

Hello experts,

I am a little unclear about
sort_area_size parameter and
it's relationship with physical memory.
According to definition, this parameter defines the maximum size of real memory(in bytes ) that will be used by a single sort.
How does it play with HP-UX where all memory is virtually mapped.
So, on the system with PGA( dedicated ) , say, 1MB, if a DBA needs to increase sort_area_size , the increase will come at the physical memory expense rather than all available memory?

Please, clarify.
Thanks a lot!
Dimitry.


3 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: Oracle SORT_AREA_SIZE

Hi,

you could check the following doc id 102339.1 on the Oracle metalink Web site in relation to SORT_AREA_SIZE , SORT_AREA_RETAIN_SIZE.

JL
fiat lux
Steve Steel
Honored Contributor

Re: Oracle SORT_AREA_SIZE

Hi

I like

http://www.orafaq.com/faq.htm

Enter at bottom of page. lots of oracle info and search on SORT_AREA_SIZE

Steve Steel
If you want truly to understand something, try to change it. (Kurt Lewin)
Brian Crabtree
Honored Contributor

Re: Oracle SORT_AREA_SIZE

SORT_AREA_SIZE is the amount of memory allocated for a sort to be done in memory. If SORT_AREA_SIZE is set to 1m and a sort will take 500k, then it will sort the information in memory, rather than using a more costly disk access. What you need to do is set it to a number, and then run either statspack or utlbstat/estat to see if you are performing more sorts in memory or in disk. This setting should not be set to high if there is a large number of processes, as it will actively take a large portion of memory, and could force your system into even more costly swap space. Normally, 1m is a good number to start with, and can increase from there depending on available memory and average number of processes.

Brian