Sort_Area_Size / Sort_Area_Retained_Size Proper Sizes
Posted on 2009-05-01
I am looking for some clarification of setting some Oracle parameters
for my Oracle 220.127.116.11 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.