Q

Best size for SORT_AREA_SIZE

How do I determine the best size for SORT_AREA_SIZE? Prior to Oracle9i, one would issue queries similar to the...

following:

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!

This was last published in March 2004

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close