Re: Maximum size for PGA_AGGREGATE_TARGET
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>, development@xxxxxxxxxxxxxxxxx
- Date: Tue, 19 Jan 2010 07:35:13 -0800 (PST)
Hey Martin,
I may actually have a chance to test this today and the next couple days. We
have some new code that was moved into production for our marts. It ran very
well in test, but as luck would have it, we have some seriously large feeds
that have been pointed towards it in the last day that have caused it to make
some seriously incorrect choices for execution path when run in production.
I am seeing upwards of 70-90GB in sorts and even though the
PGA_AGGREGATE_TARGET is set to 10G on our environments, I have been
experiencing totals in PGA usage that are larger than that total with
parallelism when viewing at the both the database and OS level. I worked to
solve much of this last night, but I think I and the lead mart developer still
have a lot to track down on this and I can easily reproduce the situation, (I
actually had to patch the secondary mart server and PUT BACK commands to alter
the session and set up a manual workarea policy for hashing and sorting to stop
the problem...)
I'm back in the office today, so I'll let you know if I can trap some solid
info on it. They are adding more memory to the mart servers today for me and I
have a secondary server that I'm able to hammer/test in.
Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com
"Go away before I replace you with a very small and efficient shell script..."
--- On Tue, 1/19/10, Martin Bach <development@xxxxxxxxxxxxxxxxx> wrote:
From: Martin Bach <development@xxxxxxxxxxxxxxxxx>
Subject: Maximum size for PGA_AGGREGATE_TARGET
To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Date: Tuesday, January 19, 2010, 4:57 AM
Dear list,
I know this may sound silly but what's the maximum size for
pga_aggregate_target on a PAE kernel? Could this be defined > 2G and actually
use all of that if needed?
I'd normally say that it's limited by the maximum address space for processes
on 32bit systems which, depending on kernel is around 1.7-2G of memory. That's
also undermined by v$pgastat statistic "maximum pga allocated".
I did some background reading and research and it appears that there is a
1-many relationship between process and workarea(s). Could a process use
multiple workareas for really large sorts?
Anyway, no one should be on 32bit systems nowadays anyway if you need large
amounts of memory :) I'm really more used to 64bit boxes...
I used http://www.pythian.com/documents/Working_with_Automatic_PGA.ppt as
reference, but might be completely off the mark.
Regards,
Martin
-- Martin Bach
OCM 10g
http://martincarstenbach.wordpress.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
