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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Jeff Chirco <backseatdba@xxxxxxxxx>
Sent: 27 March 2018 16:30:43
To: Rich J; giantpanda@xxxxxxx
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@xxxxxxxxxxxxxxxxxxxxx<mailto:rjoralist3@xxxxxxxxxxxxxxxxxxxxx>>
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
--
//www.freelists.org/webpage/oracle-l
