Hi Everyone,
I currently have a dilemma with another DBA in our organization which I would like to seek advice. The other DBA insists that she attended Oracle training class in the past which taught her that only 40/50% of the memory (~25G) in a 64G windows box, should be allocated to Oracle, for example, SGA_TARGET set to 16G, PGA_AGGREGATE_TARGET set to 4G PGA_AGGREGATE_LIMIT set to 8G. and there will be ~40G physical memory unused. Assume there are no other applications except Oracle RDBMS in the box and assume the database is big.... so when Oracle performance report suggests increasing SGA size , she thinks we should add memory to the box. But my understanding is that, after Oracle 12c introduces the new parameter "PGA_AGGREGATE_LIMIT", Oracle will not expand and exhaust all memory of the box, we could easily increase the SGA_TARGET in this case, to use the 40G unused memory without any issue. my understanding is that the 40%/50% Oracle teaching limitation is only prior to Oracle 12.1 to avoid PGA exhaust all physical memory, this mindset should be changed for now with the new PGA_AGGREGATE_LIMIT parameter.
Could you tell which of us is correct?
I saw there are many threads discussing similar scenarios but all these threads I see are really old like ten or more years ago when 12c is not available, so I think we need a new thread to discuss the current version.
Thank you
Patrick