Oracle SORT_AREA_SIZE

[Dimitry Snezhkov]

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.

[Jean-Luc Oudart]

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

[Steve Steel]

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

[Brian Crabtree]

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