How do I determine the best size for SORT_AREA_SIZE? Prior to Oracle9i, one would issue queries similar to the...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
SELECT value AS memory_sorts FROM v$sysstat WHERE name='sorts (memory)'; SELECT value AS disk_sorts FROM v$sysstat WHERE name='sorts (disk)';
The idea is to keep the ratio of disk sorts to total sorts (disk + memory) very low, or even at zero. If this ratio was high (i.e. over 10%) then increase SORT_AREA_SIZE.
In Oracle9i, one would now use the PGA_AGGREGATE_TARGET instead of individually tuning the four *_AREA_SIZE parameters. I have more information on my Web site (http://www.peasland.net) in a white paper titled "Tuning PGA in Oracle 9i." Feel free to download this paper to see how manually tuning the SORT_AREA_SIZE is a thing of the past!
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.