Re: How to trace memory resize ops

From: Jeff Chirco <>
Date: Tue, 27 Mar 2018 08:30:43 -0700
Message-ID: <>

I would definitely set minimum values. Are you using memory_target parameter? Just explicitly set shared_pool and db_cache_size to the minimum you want.
I had some problems with my database on Windows where the memory resized too frequent and crashed the database. Oracle Support also had me set this hidden parameter *_memory_broker_stat_interval=999* which required 999 seconds between resizes of shared_pool and db_cache. This was when my database was but I left it and still have it set with

Also check these queries out to view when resize is happening.

select START_TIME, component, oper_type, oper_mode, initial_size/1024/1024 "INITIAL", FINAL_SIZE/1024/1024 "FINAL", END_TIME from DBA_HIST_MEMORY_RESIZE_OPS
where component in ('DEFAULT buffer cache', 'shared pool') AND status = 'COMPLETE'
order by start_time DESC, component;

select component, AVG(FINAL_SIZE)/1024/1024 "AVG FINAL", MEDIAN(FINAL_SIZE)/1024/1024 "MEDIAN FINAL", MAX(FINAL_SIZE)/1024/1024 "MAX FINAL"
from v$sga_resize_ops
group by component;

On Mon, Mar 26, 2018 at 9:22 AM, Rich J <> wrote:

> On 2018/03/26 05:43, Ingrid Voigt wrote:
> 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
> I had a similar issue a number of years back (probably v10.1), with 1
> application that had 250K statements in the SGA (no binds), increasing the
> shared pool from 1.5GB to >11GB. A side effect was that stats collection
> on the dependent table caused a library cache lock that froze the instance
> for 10+ minutes while it invalidated those statements. Workarounds
> ensued...
> My solution was to set the memory sizing back to manual by having
> SGA_TARGET=0 and specifying sizes of all pools manually. Coming up with
> the exact size is an exercise in itself, but we've been stable ever since.
> The vendor's app is still not using bind variables and we're on v11.3
> (going to 12 soonish), but the shared pool size is now static. Ideal?
> No. But the vendor's extremely unlikely to rewrite their
> SQL-Server-centric app to accommodate.
> Just a thought...
> Rich

Received on Tue Mar 27 2018 - 17:30:43 CEST

Original text of this message