Re: How to trace memory resize ops

From: Ls Cheng <>
Date: Wed, 28 Mar 2018 11:08:16 +0200
Message-ID: <>


According to the note this kicks in when sga_target is 0, what if ASMM is on? In our case we had sga_target (so ASMM was on) and setting minimums fixed the issue.


On Tue, Mar 27, 2018 at 7:41 PM, Jonathan Lewis <
> wrote:

> It looks as if it's not very well known that in 11.2 the SGA can do
> automatic resizing even when you think you've set up the parameter file to
> stop it, and think you have fixed size memory areas. The OP did actually
> say they had set the db_cache_size to 16GB and the shared_pool to 3.5 GB,
> and pointed to MoS note 1269139.1 - though it would have been helpful to
> supply the title as well, which is:
> SGA Re-Sizes Occurring Despite AMM/ASMM Being Disabled
> This note is where the comment about setting parameter
> _memory_imm_mode_without_autosga to FALSE came from.
> Personally, if I couldn't get a good handle of what was driving the
> massive increase in the shared pool I think I'd set the parameter and wait
> for a 4031 crash because it's possible that the crash just won't happen if
> the SGA is limited. (If you fix the size it's possible that the problem is
> garbage that can't be cycled out of the shared pool fast enough when the
> shared pool is very large, but would be dropping out much more frequently
> and efficiently with a small shared_pool).
> Regards
> Jonathan Lewis
> ________________________________________
> From: <> on
> behalf of Jeff Chirco <>
> Sent: 27 March 2018 16:30:43
> To: Rich J;
> Cc: oracle-l-freelist
> Subject: Re: How to trace memory resize ops
> 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
> where component in ('DEFAULT buffer cache', 'shared pool') AND status =
> order by start_time DESC, component;
> select component, AVG(FINAL_SIZE)/1024/1024 "AVG 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 Wed Mar 28 2018 - 11:08:16 CEST

Original text of this message