Re: ASMM - resizing triggers/thresholds

Hey Alfredo DBA ;)

Thanks for your reply.

The resize_ops end with the last SHRINK on the buffer cache, shortly before we fixed the library cache eater. So I think basically it works, but refuses to accept my freeing up LC. ( in fact, flushing the shared pool should do that job, isn't it? But maybe simply does not trigger the thresholds of that very mechanism.)

The LC cache issue itself was just simply high version counts, eating up LC (and thus, shrinking buffer cache), parsing times, but no hard errors.

Yes, for sure minima for the common memory components are good practice, but sometimes somebody else made the decisions how to set up the DB in question... Guess you recognise the situation. :)

Thanks again, hope to see you in Texas?

Regards

-- 
Martin Klier // Performing Databases GmbH 
Managing Partner // Senior DB Consultant 
Oracle ACE Director 

martin.klier_at_performing-db.com // https://www.performing-databases.com 



> Von: "Alfredo Abate" <alfredo.abate_at_gmail.com>

> An: "Martin Klier" <martin.klier_at_performing-db.com>

> CC: "Oracle-L Freelists" <oracle-l_at_freelists.org>

> Gesendet: Samstag, 2. Februar 2019 13:50:37


> Betreff: Re: ASMM - resizing triggers/thresholds





> Hi Martin,


> When you say you don't see any resizing operations occurring, there are no rows

> at all when you check v$sga_resize_ops?




> The issue you are facing with the library cache is in the form of ORA-04031


> errors?




> When I use ASMM, I've always set minimums for the pools and then let Oracle


> decide how to use the remaining amount. Part of this is a remaining paranoia

> from using ASMM when it first came out and causing us some performance issues

> with huge swings in where it was allocating memory (mostly between the shared

> pool and the buffer cache).




> I would be interested myself if someone has an answer to the internal questions


> that you asked (where, how, what, when). :)




> Alfredo





> On Thu, Jan 31, 2019 at 4:01 AM Martin Klier - Performing Databases GmbH < [


> mailto:martin.klier_at_performing-db.com | martin.klier_at_performing-db.com ] >

> wrote:


>> Hi listers,

>> how and when are memory areas like DB cache and shared pool being resized, when
>> the instance runs in Automatic Shared Memory Management?

>> In my case, I have a system that has had some shared pool eating (child cursor
>> issue, blowing up the library cache), which was resolved. Now we have a
>> de-facto undersized buffer cache and low library cache consumption, but I can't
>> see any resizing taking place. I have SGA max size = SGA Target and no minimum
>> values for any component here.

>> Platform: Linux, Oracle EE 12.2.0.1

>> It would not be a big problem to restart the instance at some point, but this
>> may be different for other cases. So my interest is more educational. :)
>> In short: Where are the thresholds, what does trigger the resizing, how often is
>> it scanned, when does it happen?

>> Thanks in advance!
>> --
>> Martin Klier // Performing Databases GmbH
>> Managing Partner // Senior DB Consultant
>> Oracle ACE Director

>> [ mailto:martin.klier_at_performing-db.com | martin.klier_at_performing-db.com ] // [
>> https://www.performing-databases.com/ | https://www.performing-databases.com ]

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