Home » RDBMS Server » Performance Tuning » PGA_AGGREGATE_TARGET & SORT_AREA_SIZE
PGA_AGGREGATE_TARGET & SORT_AREA_SIZE [message #206051] Tue, 28 November 2006 07:43 Go to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi All,
I have Oracle server. I have specified PGA_AGGREGATE_TARGET = 820M, is it required to specify SORT_AREA_SIZE also?

I heard that SORT_AREA_SIZE is not required to be specified if PGA_AGGREGATE_TARGET value is defined in the system. Is it true?

What would happen if I specify both?

Re: PGA_AGGREGATE_TARGET & SORT_AREA_SIZE [message #206053 is a reply to message #206051] Tue, 28 November 2006 07:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10673
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Re: PGA_AGGREGATE_TARGET & SORT_AREA_SIZE [message #207482 is a reply to message #206053] Tue, 05 December 2006 14:00 Go to previous message
Messages: 5
Registered: December 2006
Junior Member
I've read the quoted article above, and it gives a good overview. From what i've known, sort_area_size and sort_area_retained_size are no longer used the moment you set work_area_policy=auto and a pga_aggregate_target.

I have a follow-up question to that, though--even though the sort_area_size and hash_area_size are not used, do they count when pre-allocating memory for each user session that connects? Basically, I'm trying to size my SGA + PGA + user sessions all under the windows 2GB memory limit (This means oracle.exe shouldn't exceed around 1.8 million bytes in the task manager, as I understand). I'm in the same situation as Anand, where both sort_area_size and pga_aggregate_target parameters were set. I thought I had enough memory , yet one day during month-end, we ran into ORA-4030 errors saying not enough memory.
Previous Topic: bit confused about Execution Plan
Next Topic: more precise timing with TKPROF
Goto Forum:

Current Time: Sun Jul 23 06:04:47 CDT 2017

Total time taken to generate the page: 0.10235 seconds