Jason Oon, October 17, 2018 - 6:13 am UTC
Thanks Connor for the reply.
If I were to change from AMM to manual SGA & PGA what is the general percentage of overall server memory should I allocate to each SGA & PGA?
October 20, 2018 - 4:44 pm UTC
"Depends".
If I'm running 10 connected sessions, they might use (say) 10meg each...which means I can allocate a lot of memory to SGA.
But if I have 10,000 sessions, each running 10meg each, then
a) thats probably got some other problems :-)
b) thats a lot of memory that we need to assign to those sessions and hence SGA/PGA may need to be adjusted
You can use the advisors eg
V$PGA_TARGET_ADVICE
V$SGA_TARGET_ADVICE
and the lower level ones as well, to get an idea of where to start.
Some general Recommendations
Niraj, October 31, 2018 - 8:02 pm UTC
I would Agree with Connor. It is better to use SGA and PGA along with Huge pages. (You can make more research on Huge pages)
For example, If you have 100 GB Installed RAM,
Recommended huge pages are 65 GB and Non-Huge Page memory is 35 GB.
Now, If you have 10 databases running on the server,
For SGA-PGA:
> Sum of all SGAs should fit within huge pages. (65GB here)
> Sum of all PGAs should be less than non-huge page memory. (35 GB here)
Note:
It is not necessary to provide equal sga and pga to all the databases available on the server. Sometimes it depends on how busy that database is.
Below is just an example. We have 4 dbs on the server. This is how we allocated SGA-PGA for it.
DB_NAME SGA(Bytes) PGA(Bytes)
A1 4,294,967,296 1,073,741,824
A2 8,086,618,112 2,147,483,648
A3 4,294,967,296 1,073,741,824
A4 8,086,618,112 4,294,967,296
November 02, 2018 - 2:56 am UTC
thanks for the input