Re: buffer advisor
- From: Jeremy Schneider <jeremy.schneider@xxxxxxxxxxxxxx>
- Date: Mon, 8 Sep 2014 09:19:22 -0400
On Sun, Sep 7, 2014 at 9:05 PM, Gaja Krishna Vaidyanatha <
dmarc-noreply@xxxxxxxxxxxxx> wrote:
>
> At the risk of be called "old school", I am big believer that SGA sizing
is a DBA's job whose understanding of the application, the workload and the
"data/index block working set" obviates the unnecessary automation of ASMM
and AMM.
>
> Are people using ASMM and AMM in production? Yes, of course they are.
Does it work across the board for all application workloads? I seriously
doubt it. At the end of the day, as I said, the cost of instability
introduced by this automation FAR EXCEEDS the cost of memory
over-allocation for SGA/PGA memory with manual configuration. This is where
using the AWR Advisory Statistics is quintessential for sizing the SGA/PGA
for stability and consistency in performance. And for me, stability and
consistency in performance, trumps automation.
>
This has been a good thread. Just to give one quick opposing point of
view... :)
I don't disagree that auto memory mgmt features have caused issues. That
being said, I have personally seen a lot of benefit in them. In my
particular case, I recently worked on a two person dba team that managed a
highly consolidated environment (over a thousand apps, combination of
schema/instance/server consolidation). The apps were fairly well behaved
when it came to query construction and shared pool use. Our default
database setup used hugepages and ASMM without minimums, and all of our
databases were stable while I was there. When I checked memory usage while
troubleshooting database performance issues, I did not see granules
swapping between shared pool and buffer cache as Mark referenced. (But I
have seen this before with different customers.) These databases were
mostly on 11.2.0.3 with the latest PSUs applied.
Taking the time to go through dozens of databases with hundreds of apps and
trying to understand memory needs and lay down hard sizes for pools would
have been totally unnecessary for us when we hadn't yet had any problems
with ASMM. Also, it would have taken time away from all the other
important stuff our two-person team was doing - careful design of hard
standards, new data center buildouts, migrations, continual patching,
constant monitoring, application support, developer training... etc!
So personally, I'm comfortable with ASMM (but not AMM) as a default
starting point, and setting cache size minimums or disabling it entirely
when problems arise.
-Jeremy
--
http://about.me/jeremy_schneider
Other related posts:
