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