OraFAQ Forum: Server Administration » sort_area

Home » RDBMS Server » Server Administration » sort_area_size too small

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sort_area_size too small [message #64599]

Fri, 11 February 2005 21:05

ramya
Messages: 87
Registered: August 2002

Member

Hi
i am using 8i database in solaris 8.Whenever a batch jobs runs i found the sorting is happening in disk more( the temporary tablespace is using very heavily) the value of the parameter sort_Area_size is 64 k.SO how much i can increase the value of this parameter.How can i determine a optiomal value for this to improve the performance(we have 2 1 gb ram)
with regards
ramya

Report message to a moderator

Re: sort_area_size too small [message #112624 is a reply to message #64599]

Mon, 28 March 2005 06:33

kumar_dba
Messages: 16
Registered: March 2005
Location: chandigarh

Junior Member

It will b not more than 2m bcoz it will b for per session
OK

Report message to a moderator

Re: sort_area_size too small [message #112655 is a reply to message #64599]

Mon, 28 March 2005 10:43

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

sort_area_size is the amount of memory to use for sorting purposes for each session that is connected to the db. So if you you have a large DSS type system with only say 10 people at most connecting at any time, and you have 500 mb you can allocate to sort in total for the db, then you can make it around 50mb. But if you have lots of concurrent users it will be less. And if you upgrade db version you can not worry about it near as much with pga_agg_target.

Oh and don't forget to have memory for hash_area_size and shared_pool.

Report message to a moderator

Re: sort_area_size too small [message #112731 is a reply to message #64599]

Tue, 29 March 2005 00:18

d.c.b.a
Messages: 44
Registered: March 2005
Location: China

Member

If you want to build an large index, just using alter session set sort_area_size=50m or 100m, before you issue the create index command.

For normal operation 1m is enough.

Report message to a moderator

Previous Topic:	SQL Server 2000 db migration to Oracle 9i
Next Topic:	Insufficient Privileges

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 13 08:27:49 CST 2025