AMM experiences

Giganews Newsgroups
Subject: AMM experiences
Posted by:  Mladen Gogala (gogala.REMOVETHISmlad…
Date: Tue, 10 Apr 2012

I am reluctant to set AMM on one of my production databases. The machine
is Dell, 4 quad-core CPU's and 64GB of RAM, 64 bit RH EL 5.8. I was
allocating a hefty 32GB SGA, with PGA_AGGREGATE_TARGET set to 20GB. 32GB
for SGA was allocated by defining huge pages.
Now, my manager challenged me by mentioning memory_max_size and
memory_target. My answer was that PGA and SGA have very different paging
characteristics and that they shouldn't be lumped together. I also
mentioned that the page table for the 32GB shared memory segment that is
SGA is a whopping 64MB, if 4KB pages are used, which makes it quite likely
that page tables will be swapped out, sometimes in the future, with
predictably disastrous consequences for the speed of the system.
Normally, these two arguments should suffice, but I am now required to
"show the numbers", which is not at all hard to do. I am organizing a
little benchmark using swingbench, with 2 types of queries and I am
configuring it in such a way that the memory will be close to exhaustion.
I don't have much doubt about the outcome of the benchmark, but I would
like to hear other experiences.
Has anybody used AMM on a large box (see the description above) and what
were the results and impressions? I must confess that AMM looks to me like
one of those DBA 2.0 features, intended for small to medium instances, not
large monsters with 32GB SGA size.
I wouldn't like my SGA to be paged out and I wouldn't like my sort/hash
work areas in memory not to be paged out at some point. Please, feel free
to chime in, except by trying to advertise blogs or "sexy chats".