Re: Maximum size for PGA_AGGREGATE_TARGET
Date: Tue, 19 Jan 2010 07:35:13 -0800 (PST)
Message-ID: <331406.45884.qm_at_web32002.mail.mud.yahoo.com>
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_at_the-playground.de> wrote:
From: Martin Bach <development_at_the-playground.de> Subject: Maximum size for PGA_AGGREGATE_TARGET To: "ORACLE-L" <oracle-l_at_freelists.org> 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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 19 2010 - 09:35:13 CST