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 first published in March 2004

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close