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@xxxxxxxxxxxxxxxxxx
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@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
