Home » RDBMS Server » Performance Tuning » Sort and hash area size problem
Sort and hash area size problem [message #161923] Tue, 07 March 2006 22:54 Go to next message
sach_sumit
Messages: 9
Registered: September 2005
Junior Member
Hi all

We have Oracle 8.1.5 on novell server

At instance level I have set in pfile

#sort_Area_size=2m
#hash_area_size=3m

Now problem is when I see through OEM for individual sessions it shows me sort_area_size=6m or even greater for some sessions and in same way for hash_area_size (i.e greater then specified at instance level). How is it happening?

Because of this Process memory error ora 0403 starts coming as the OS has no available memory free to allocate as we have 300 connections in peak hrs. And we have mix of oltp and warehouse environment.
Total RAM is 3gb and sga is 900mb.

Also due to year end a lot of reports r running and it degrades the performance and finnaly process memory error starts to come.

Is it some bug at Oracle version level or it has something to do with our applications or is it like I am unable to figure out something.

What could be the issue?

Regards
Sumit Sachdeva
Re: Sort and hash area size problem [message #162067 is a reply to message #161923] Wed, 08 March 2006 09:41 Go to previous message
aciolac
Messages: 242
Registered: February 2006
Senior Member
I want to mention that sort_area_size parameter in the init.ora sets how many memory in SGA will be allocated for sort operations of users. So, this parameret should be realtively small(If You have problems with memory, set them for example 1M or smaller). If this memory is not enough for sort in session, Oracle get's additional space for this session in TEMP, which are freese after end of sort. So I think that OEM show to You the real space occupied for sorting(in SGA and TEMP).
Previous Topic: Wait event 'db file sequential read' on TABLE segment
Next Topic: help for not equal and index usage
Goto Forum:
  


Current Time: Thu Mar 28 13:22:41 CDT 2024