Re: avg % of SGA allocated to buffer cache

From: Tim Gorman <tim_at_evdbt.com>
Date: Fri, 25 Oct 2013 21:06:20 -0600
Message-ID: <526B31AC.4090408_at_evdbt.com>

Today, I was just (figuratively) tsk-tsk-ing a customer for having a dev/test server with four cores and 28Gb of RAM supporting six Oracle databases, each with 2-4Gb SGAs, and expecting real-world performance benchmarking. My rule of thumb is 8 Gb of RAM per core as a starting point, but since these folks are experiencing average waits on "db file sequential read" of 700ms, they should probably be thinking more like 8 Tb per core. :-)

Oracle Support note #169706.1 recommends kernel SHM settings assuming no more than 50% of RAM for SGA, and I've worked in environments where up to 75% of RAM was allocated SGAs without ill effect, but those were highly transactional environments without significant PGA utilization.

If Oracle is citing a situation with 50 non-PDB database instances running on a server that size, then certainly they're talking about 50 instances without any workload that are just being started and are not expected to actually do anything further. Likely, the only reason they cited 250 PDBs in their example is because that's right below the maximum number of PDBs for 12cR1; else we might have seen claims for 400-500 PDBs, in all likelihood. And why not, when all you have to do is stand them up?

So, not intended as a real-world recommendation, but just a lab simulation to prove that non-workload PDB instances use roughly 20% of non-workload non-PDB instances.

On 10/25/2013 5:45 PM, kyle Hailey wrote:
> Has one seen, or does anyone have, data on what the average size of buffer
> cache is across Oracle databases? and/or average % of SGA allocated to
> buffer cache.
> One reason I ask, among several, is that pluggable databases save about
> 300-400MB per instance going from non-PDB to PDB.
> Thus Oracle claims they can go from 50 instances on a 20GB RAM host to 250
> with PDB.
> First I'd be surprised at anyone running 50 Oracle instances on 20GB of RAM
> second I find it hard to fathom running 250 PDB on 20GB of RAM meaning that
> they'd all have a few MB of buffer cache available each at best.
>
> If PDB option is $17,500 it might be more cost effective to either add more
> memory to the machine or add more cache to the back end array serving I/O.
>
> Musing on configuration options ...
>
> - Kyle
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Oct 26 2013 - 05:06:20 CEST