Re: How to trace memory resize ops

Hello Ingrid,

> Ist there some way to trace / find out what exactly causes this?

Sure - here we go ...

SQL> alter system set "_memory_management_tracing"=7 scope=memory sid='*';

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Ingrid Voigt <giantpanda_at_gmx.net> hat am 26. März 2018 um 12:43 geschrieben:
>
>
> 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