Re: huge pages, ASMM and SGA sizing
Date: Wed, 23 Jul 2014 12:14:49 -0400
Message-ID: <1406132089.53cfdf795aecb_at_webmail.tpk.net>
"What does ADDM say?" Graham Wood IS superior advice IF you have a running load against which to check.
Read, to a reasonable level of technical understanding, Frits Hoogland's stuff about adaptive direct read and think clearly about how that relates to your workload mix before you pick a starting point. For some sizes of databases where most of the frequently accessed blocks will in fact fit, 80-20 might be a good starting point. But if your maximum possible block buffer cache is a small fraction of the blocks that must routinely be referenced, then pga and adaptive direct read are your friends. In that case a smaller block buffer cache with smallish look-up tables and side references persistently cached and kept warm by your favorite method to allow maximum pga use by each session would be better.
Rendered short enough for an email, I'd suggest those are the polar cases and you should think clearly (tm - Cary Millsap) where you fit.
Versus the total machine memory, we also have to consider what else besides Oracle RDBMS server load is on your machine. PROBABLY, since you're paying Oracle licenses per cpu, other load on RDBMS servers should be limited to things that ultimately save RDBMS server cpu bandwidth. Just for example, say, the cpu load of sftp to get a file source for an etl local to a memory drive or plug-in SSD so that no RDBMS cpu is burned on network waits during the load might be justified (depending on the differential to whatever the access might otherwise be.) That is just an example, but the idea is only to put load on the licensed RDBMS cpus that is net conservative of cpu load. And you want to reserve enough memory for those jobs so they operate as efficiently as possible (including memory for transient duplicate location of file sources for etl and external file access, if that is part of your job profile.)
Turning on ASMM long enough to get a recommendation *may* also be useful. My observation is that leaving it on permanently tends to lead to a periodic swap of a few shared pool and block buffer cache granules that is unhelpful, but that a short observation different each load profile workshift window you have will confirm whether you are in a reasonable state.
Good luck. Fortunately the difference between "about right" and "perfect" is small or zero unless you are seriously undersized in memory.
mwf
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 23 2014 - 18:14:49 CEST