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

x
?
Solved

Sort_Area_Size / Sort_Area_Retained_Size Proper Sizes

Posted on 2009-05-01
1
Medium Priority
?
1,289 Views
Last Modified: 2013-12-19
Good Day,

I am looking for some clarification of setting some Oracle parameters
    Sort_Area_Size
    Sort_Area_Retained_Size
for my Oracle 8.1.7.3 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:
   open_cursors=300
   CURSOR_SPACE_FOR_TIME=TRUE
   optimizer_mode=CHOOSE
   log_buffer=1048576
   db_block_size=8192
   processes=500
   shared_pool_size=1,342,177,280
   db_block_buffers=128000
   java_pool_size=262,144

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.
0
Comment
Question by:nftcadmins
1 Comment
 
LVL 17

Accepted Solution

by:
k_murli_krishna earned 1500 total points
ID: 24282402
0

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