Re: How to trace memory resize ops
Date: Tue, 27 Mar 2018 17:41:01 +0000
Message-ID: <MM1P123MB0988F878B2501091FBA29CC4A5AC0_at_MM1P123MB0988.GBRP123.PROD.OUTLOOK.COM>
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
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
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
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
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
where component in ('DEFAULT buffer cache', 'shared pool') AND status = 'COMPLETE'
order by start_time DESC, component;
group by component;
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Mar 27 2018 - 19:41:01 CEST