[Last Call] Learn how to a build a cloud-first strategyRegister Now


Sort_Area_Size / Sort_Area_Retained_Size Proper Sizes

Posted on 2009-05-01
Medium Priority
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

k_murli_krishna earned 1500 total points
ID: 24282402

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

834 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