1748248 Members
3765 Online
108760 Solutions
New Discussion юеВ

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 3
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