AMM experiences

From: Mladen Gogala <>
Date: Tue, 10 Apr 2012 15:48:44 +0000 (UTC)
Message-ID: <jm1kor$spg$>

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".
Received on Tue Apr 10 2012 - 10:48:44 CDT

Original text of this message