Re: How to trace memory resize ops

From: Ls Cheng <exriscer_at_gmail.com>
Date: Wed, 28 Mar 2018 11:08:16 +0200
Message-ID: <CAJ2-Qb9fXE9Dr1BkZ_sLqapPLfiS4zvyp2BwpFEZ1auBka1CSw_at_mail.gmail.com>



Hi

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.

Thanks

On Tue, Mar 27, 2018 at 7:41 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> 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
> (MEMORY_TARGET/SGA_TARGET=0)
>
> 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: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Jeff Chirco <backseatdba_at_gmail.com>
> Sent: 27 March 2018 16:30:43
> To: Rich J; giantpanda_at_gmx.net
> 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 11.2.0.2 but I left it and still have it set with 11.2.0.4
>
> 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 <rjoralist3_at_society.servebeer.com
> <mailto:rjoralist3_at_society.servebeer.com>> 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
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 28 2018 - 11:08:16 CEST

Original text of this message