Thank you Tim.
We are seeing there were a lot of resize operations in the past that
happened with status as 'COMPLETE'. Why is it failing suddenly then? Also
these setup of shard_pool_size and db_cache_size params are just the
minimum sizes set to safeguard and it's up to oracle to bump up that value
based on the workload. Please correct me if I'm wrong. Also we are seeing
in dba_history_parameter there is a drop in value of db_cache_size
happening from ~3.5GB to 2GB, but no such changes happened for
shared_pool_size. So ideally this should not be the cause of this sudden
error. Correct me wrong?
On Wed, Jun 15, 2022 at 6:00 PM Tim Gorman <tim.evdbt@xxxxxxxxx> wrote:
SGA_TARGET is zero, so of course a resize operation failed.
Please reset SGA_TARGET to equal SGA_MAX_SIZE and please consider
unsetting all of the other SGA parameters except SHARED_POOL_SIZE, to allow
automatic SGA management to do what it is supposed to do? Please unset the
underscore parameters too.
The days of micromanaging SGA components by guesses and wishes were over
decades ago.
On 6/15/2022 2:20 AM, Pap wrote:
We are hitting below Ora-4031 in one customer database. Even we have 'free
memory' available (~2GB) in shared_pool during the error. And also the size
of the "KKSSP" sub pool and it was showing null value only. Its version
19.11 database has the below memory setup. We do see the resize operation
in which the shared_pool_size was 12GB and was GROWing and db_cache was 2GB
and was tryin to SHRINK and both errored out.
ORA-04031: unable to allocate 12312 bytes of shared memory ("shared
pool","unknown object","KKSSP^3497","kglseshtTable")
memory_target - 0, memory_max_target - 0,sga_max_size - 35GB,sga_target -
0,shared_pool_size - 8GB,shared_pool_reserved_size -
1.2GB,_shared_pool_reserved_pct - 15,db_cache_size- 20GB
