Re: Pga is growing on 11.2.2.0

From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com>
Date: Wed, 15 Apr 2015 07:12:45 -0400
Message-ID: <CA+fnDAaTp1T11GxSnYu+i320D-bKHnD72JC5TbWS8YQPa8K60g_at_mail.gmail.com>

I don't necessarily disagree with any of this discussion (auto memory mgmt can certainly thrash sometimes) however I'd offer two additions:

1. I'm currently using ASMM across a rather large estate. Since I arrived a few years ago, we have been following a policy where we only set the minimums if there are problems. So far we haven't had to set a minimum yet. We do have some very busy databases, although admittedly we also write the app that runs on them so things mostly use binds and such, avoiding some of the more common exploding shared pool issues. Also our devs work great with our DBAs - when we see potential problems they are usually right on top of making improvements in the app to address them.
2. versions please, as Oracle is often changing things between releases. I have a lot of systems running on 11.2.0.3 with ASMM and we haven't yet seen thrashing (previous caveat may contribute).

That said, I don't think minimums are a bad idea. Although I'm not sure if it would really prevent "thrashing" (seems like that's usually jumping back and forth over some small threshold) but rather just prevents outright stupid allocations like Ls Cheng mentioned...

-J

--
http://about.me/jeremy_schneider


On Wed, Apr 15, 2015 at 3:03 AM, Ls Cheng <exriscer_at_gmail.com> wrote:


> Hi

>

> I dont use AMM at all but do use ASMM. With ASMM I set minimum values for

> all pool and cache, usually leave 10% to 20% free so the database kernel can

> manage

>

> On Wed, Apr 15, 2015 at 12:30 AM, MARK BRINSMEAD <mark.brinsmead_at_gmail.com>

> wrote:

>>

>> That sort of thing is hardly shocking, if you set AMM loose and let it do

>> its own thing completely without constraints.  Sadly, though, that seems to

>> be more-or-less the way Oracle had envisioned its use.

>>

>> You'll generally be much happier with AMM if you set lower-bounds for all

>> of your major memory pools, and leave AMM less room to play around.  In my

>> mind, leaving (only) about 20% of the database memory to AMMs discretion

>> seems about right.  I have no rigorous math or case-studies to support that

>> -- just instinct.

>>

>> Perhaps others have studied this more thoroughly?

>>

>> On Tue, Apr 14, 2015 at 5:59 PM, Ls Cheng <exriscer_at_gmail.com> wrote:

>>>

>>> +1

>>>

>>> AMM is a piece of junk, last customer had 8GB memory_target and after a

>>> week AMM set db_cache_size to 64M.. uh oh

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

Received on Wed Apr 15 2015 - 13:12:45 CEST