Re: huge pages, ASMM and SGA sizing
- From: "Rich Jesse" <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Jul 2014 09:44:45 -0500 (CDT)
Hey April,
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
