Re: How to trace memory resize ops

Ls

Do we set the minimum values using db_block_buffers to bypass sga_target?

CP.

On Mon, Mar 26, 2018 at 9:30 AM, Ls Cheng <exriscer_at_gmail.com> wrote:

> 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-l