oracle buffer pool tuning

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

"
Unless you have a very specific reason to do so, you don't need to worry about individual buffers anymore."

so the more RAM I add the more is the buffer pool memory ?

PGA and SGA too ?auto managed ?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

good ! tks.

but sorry , since what version it has this feature.

"Read the doc link I provided.
"

so as buffer cache is part of SGA, so it will be automatically managed you meant ?

https://oracle-base.com/articles/11g/automatic-memory-management-11gr1

>> but sorry , since what version it has this feature.

Believe it was first introduced in 9i. Back then I think there was two parameters that you needed to set as PGA and SGA were handled independently. I think in 11gR1 they were combined:

>> so as buffer cache is part of SGA, so it will be automatically managed you meant ?

Everything is in the docs.

ASKER

tks.

ASKER

tks man.

https://blog.dbi-services.com/12cr2-dbca-automatic-memory-management-and-databasetype/

Keep in mind that if you're using 12.2 or higher, AMM is NOT possible anymore if you're running with more than 4GB RAM.

So, I suggest, you rather stick to ASMM instead.

Which Oracle database server is running with less than 4GB nowadays?!

Thanks for the distinction. I use AMM and ASMM interchangeably even though I probably should not.

You're welcome ;-)

When we did the recent Oracle upgrades, we ran into exactly this issue ;-)

ASKER

Alex,

"Which Oracle database server is running with less than 4GB nowadays?"

yes, very hard to find one !

ok ASMM then.

are you all saying when upgrading to version later than 12c, it is not allowed at all if you have AMM enabled ?

>> are you all saying when upgrading to version later than 12c, it is not allowed at all if you have AMM enabled ?

No.

Don't get hung up on the terms AMM and ASSM. Oracle just changed things but the concepts are pretty much the same.

Oracle went from controlling SGA and PGA differently which I think is what they called AMM. They then switched to two parameters that control both the SGA and PGA.

I mentioned this above but didn't really differentiate between what they called it.

ASKER

"Don't get hung up on the terms AMM and ASSM. Oracle just changed things but the concepts are pretty much the same."

ok

so they should retire AMM but ASMM instead ?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

slightwv,

but you all are suggesting I use ASMM instead right?

so why "As we see, AMM is the evolution of ASMM for release 11g and beyond." ?

a later release AMM is worse than ASMM ?

Alex,

"When system memory is greater than 4 gigabytes, automatic memory management is disabled, and automatic shared memory management is enabled."

then we should not worry!

"If you choose advanced installation, then DBCA enables you to select automatic memory management or automatic shared memory management.
"

I thought Advance installation should do it for us too .....

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

"Pull the "S" out and they both say: Automatic Memory Management."

yeah !

but one work with > 4GB of RAM but one doesn't ! this is the concern.

and I read that buffer pool cache is part of SGA.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

ahhahhaa tks.

ASKER

tks both.

ASKER

I read this:

https://blog.dbi-services.com/12cr2-dbca-automatic-memory-management-and-databasetype/

and it say:

You are in this situation because you have several GB of RAM. Current servers have lot of memory. You don’t have to size it to the near 100MB. Start with some values, run with it. Look at the performance and the memory advisors. Are you doing too much physical I/O on tables where you expect data to be in cache? Then increase the SGA, and maybe set a minimum for the buffer cache. Do you see lot of hard parse because your application runs lot of statements and procedures? Then increase the SGA and maybe set a minimum for the shared pool. Do you run lot of analytic queries that full scan tables and have to hash and sort huge amount of data? Then decrease the SGA and increase the PGA_AGGREGATE_TARGET.

so it seems PGA and SGA share the SAME physical memory and they are not overlapping with each other ?

and also :

https://blog.dbi-services.com/12cr2-dbca-automatic-memory-management-and-databasetype/

"So what?
If you have a server with more than few GB, then set SGA and PGA separately. Start with the ratios above, and then monitor performance and advisors. Physical servers today have at least 32GB. Even with a small VM with 1GB for my labs, I prefer to set them separately, because in that case I want to be sure to have a minimum size for buffer cache and shared pool. You may have lot of small VMs with 3GB and think about setting MEMORY_TARGET. But using large pages is a recommendation here because the hypervisor will have lot of memory to map, so ASMM is still the recommandation."

so turn off AMM and then set PGA and SGA separately ? or just use ASMM , right ?

"Once you know the size of all SGA, look at Hugepagesize in /proc/meminfo, set the number of hugepages in /etc/sysctl.conf, run sysctl -p and your instances will use available large pages for the SGA."

large page is good for SGA ? how larger? 64KB page or 128KB page?