Ask the Expert

Best size for SORT_AREA_SIZE

How do I determine the best size for SORT_AREA_SIZE?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: