Sort_Area_Size / Sort_Area_Retained_Size Proper Sizes

Posted on 2009-05-01
Last Modified: 2013-12-19
Good Day,

I am looking for some clarification of setting some Oracle parameters
for my Oracle instance.

My current server configuration is as follows:
   Windows 2003 R2 Enterprise Edition w/ SP2
   Windows BOOT.INI has the /3GB switch enabled
   Physical RAM = 4GB
   Storage = EMC CX300 SAN
   CPUs = 2 x 3GHz Quad Core Intels

My current Oracle memory configuration is as follows:

Other memory and SGA related information shows as follows:
   Fixed Size = 75,804
   Redo Buffers = 1,056,768
   Sum = 2,418,767,900
   Buffer Hit % = 98.74
   Buffer Nowait % = 100.00
   Library Hit % = 98.67
   Execute to Parse % = 61.02
   Redo NoWait % = 99.99
   In-memory Sort % = 98.37
   Soft Parse % = 92.22
   Latch Hit % = 99.89

So my problem is, of course, performance.  We have come to realize that this particular database/application is a very read intensive database.  So we have configured the data files to spread out appropriately amount 30 some fiber channel 15K hard drives attached via fiber channel HBAs.  This however, has not solved my performance problem to my satisfaction.

After some heavy research into the next step, I have been studying up on the SORT_AREA_SIZE parameter.  After checking the number of disk sorts, the average size (12M) and the number of concurrent disk sorts (20) I have decided that a sort_area_size  = 15M should be appropriate.  Once I implement this into my test environment the response from the application is GREATLY improved, so I have confirmed that the setting works.  As a side note, once I set the sort_area_size to 15M I no longer have any disk sorts show up.

However,  due to my memory limitation on the server and current settings I am afraid of implementing this into my production environment and would like to confer with some other experts to see if I set my sort_area_size to 15M and have a concurrent sort of 20, would I run into memory shortages?

I do not have a very clear understanding of where this 'sort_area_size' memory is being taken from.  Does it use some of the allocated memory from the shared_pool_size or does it use it from the db_block_buffers.  I understand that it uses the UGA, but again, I am not sure where that memory is allocated.

Next to that, should I be setting the Sort_Area_Retained_Size value to something, since the majority of the information in this database is read, would this help?  What size should I set this too?

Thanks.  If there is any values that I missed let me know and I will post.
Question by:nftcadmins
    1 Comment
    LVL 17

    Accepted Solution


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now