In one of 19.11, Exadata database we are encountering Ora -04031 even we have free memory available. And also we are seeing the minimum set value i.e. db_cache_size and shared_pool_size are increasing/decreasing automatically as visible in dba_hist_parameter. Below is the current DB parameters from v$parameter.
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- 6GB
So my question is
- with just sga_max_size being set as non zero but sga_target as '0' , does it mean we are using automatic memory management here? And thus there is chances that we were hitting below bug?
ORA-4031 Errors On ASM Instance When Huge Pages Are Enabled On The System (Doc ID 1625886.1)
2)And why the resize operation even happening here as we have set both sga_target and memory_target as 0, so the db_cache_size and shared_pool_size should be treated as static values? or its happening weirdly because we have sga_max_size is set as non zero value?
- Again the dba_cache_size and shared_pool_size are manually set and are treated as the minimum allocated values and oracle will perform the resize operation automatically based on the workload but will not go below these set values. So, why are we seeing the values of dba_cache_size and shared_pool_size going below the set minimum values and these change in the values are visible over time in dba_hist_parameter view too?
4)What is the correct memory parameter setup we should have here? Should we go for manual memory management option.