Aman.... said...Hi sir,
Very good article.Can you please some more information in it about how Memory Target works?
You set it and leave Oracle to do the work. Apart from the normal monitoring you would expect, and maybe using the advisor, I don't see what else there is to say.
If you want to know about internals you might want to start by reading the links I've just added to the article.
Aman.... said...Hi sir,
Thanks a ton sir.I shall read them.
Dave H said...In the article, you stated that you should set both MEMORY_TARGET to your required value, as well as setting PGA_AGGREGATE_TARGET to zero.
-- Set the dynamic parameters. Assuming Oracle has full control.
ALTER SYSTEM SET MEMORY_TARGET=5G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;
However we tripped over a little issue with that today, since setting PGA_AGGREGATE_TARGET=0 seems to disable PGA AMM altogether. See here for details:
You should probably try instead:
alter system reset PGA_AGGREGATE_TARGET scope=spfile;
Not tested this yet, but we suspect that this is our issue.
Hope this is useful to someone.
Trung Phan said...Thanks for your advice.
It helps me to fix the issue.
Rgds, ... Trung
Halimdba said...Hi Tim,
I know this is a very old post but still, people are finding it and learning!
Do you think, is there any meaning of AMM where Database server's OS ( physical memory) RAM > 4GB ?
Even today I faced (in Oracle 19c) this issue ( [DBT-11211] while using DBCA.
I never use AMM, and I wouldn't advise anyone to use it. ASMM is a better option, and supports hugepages. OK, AMM is simpler for beginners on very small databases, but I still wouldn't use it. I'll put a note at the top of the article to tell people to avoid it.
Cyril said...Hi Tim,
thanks for this article!
It's September 2020 now and Oracle 19c has been released.
Is your advice:
"Don't use this feature! Although on the surface it looks like an improvement from a simplicity perspective, it is a bad feature. "
still valid for Oracle 12c and 19c or not any more?
It's effectively a dead feature. It has not been, and will not be improved to make it relevant. I've added an extra couple of sentences.
"Even Oracle have distanced themselves from it. In later releases it can't be selected in the DBCA for memory configurations in excess of 4G."
Cyril said...Thanks, Tim, for your response!
DO NOT ask technical questions here! They will be deleted!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!
If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.