Re: How to trace memory resize ops
Date: Mon, 26 Mar 2018 16:30:16 +0200
Message-ID: <CAJ2-Qb-XoMW7maoUwFpy7+qubroPxsbMn+cW7th9ZdkznDD=oQ_at_mail.gmail.com>
Hi
I would recommened you set some minimum values for shared pool and buffer
cache.
I had a customer who used 32GB sga_target without any minimum value, after
3 weeks he was left with 64MB buffer cache (which introduced I/O
problems,many critial queries started using direct path reads). Was fixed
by setting minimum values.
Thanks
On Mon, Mar 26, 2018 at 12:43 PM, Ingrid Voigt <giantpanda_at_gmx.net> wrote:
> Oracle 11.2.0.4.171130 EE on Windows 64 Bit
>
>
> We've been having problems with memory resize operations shrinking the
> buffer cache
> below specified limits:
>
> Specified: 16GB db_cache_size, 3GB shared_pool_size, 20GB sga_max_size.
> Changes to: approx. 3.5GB db_cache_size, 16GB shared_pool_size
>
> This occurs about 2-3 times per month, usually after hours, severely
> decreases database
> performance and ends with a bounce of the database.
>
>
> According to MOS 1269139.1 the resizes are expected behavior.
>
>
> Ist there some way to trace / find out what exactly causes this?
> (SQL, background process, other details of the root cause)
>
> My customer is not willing to set _MEMORY_IMM_MODE_WITHOUT_AUTOSGA=false
> and risk ORA-04031. If that changes, could one see something with a trace
> of
> ORA-04031?
>
>
>
> Thanks and best regards
> Ingrid Voigt
> -- http://www.freelists.org/webpage/oracle-l
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 26 2018 - 16:30:16 CEST