Re: huge pages, ASMM and SGA sizing

> I am now getting pressure from all sides to increase the SGA "because we
> have plenty of memory now" with huge pages. Triple the current SGA
> size....what are the ramifications of doing this? In 10G there were lots
> of easy ways to check buffer/pool sizes with hit ratios.
>
> Does anyone have some advice? I need solid evidence to back up what I
> say....just pointing to the ADDM report isn't what they want. We don't own
> Database Replay.
>
> I currently have the SGA allocated manually....I have not turned on ASMM
> yet (but it is on the table) and don't plan on using AMM due to known
> issues with huge pages.

Interesting! I'm going through SGA sizing exercises right now, too, although the pressure's all coming from me rather than externally.

First and foremost, if you have shared pool pressure from apps that don't use bind variables, ASMM will be the slow death of your instance. Our ERP does (thankfully!) use 'em, but some bolt-ons do not, and I had an ever-growing shared pool thanks to the pool sizing lines the instance puts at the top of the spfile. SQL invalidations from stats collection would lock the library cache for 5-10 minutes when the shared pool grew to 11G and there were 250K duplicate statements. Icky. Manual sizing for me! Being able to have Tom Kyte's ear for 2 minutes to validate my ASMM-to-manual plan was extremely helpful, too. ;)

We also had a couple of SQLs that increasingly performed worse as its associated table sizes grew. Not trusting the cache hit ratios, which were inconsistent at best for me, I added a buffer cache monitor metric to EM12c that grabs objects from V$BH larger than a threshold. I plot those on a multi-series graph of object sizes over time. To see objects struggle for space in the buffer cache throughout the day was an eye-opener for me, and justified a RAM purchase. The "before" RAM picture was all spikes and the "after" a nice set of flat lines. Elapsed times dropped dramatically. No, not the most scalable of solutions, but coupled with a necessary data archiving policy and a lack of practical control on SQLs coming out of the ERP system, it's a very good fit for us.

I'm not sure why I've never seen buffer cache object analysis over time in any monitoring tool before. It's something I monitor regularly and has been very helpful in sizing the buffer cache for me.

Hope this drivel helps! GL!

Rich

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