Hi,
we are facing a strange issue behaviour with our database, we are encountering ORA-04031 error.
----------------------------------------------------------------------------------------------------
ORA-04031: 56 Byte des Shared Memorys konnten nicht zugewiesen werden ("shared pool","unknown object","KKSSP^1959","kglseshtSegs")
-----------------------------------------------------------------------------------------------------
we have increased the sga_target from 7gb to 17gb, error persisted!
the ora-04031 is occuring at the night around 02.54 PM, during our Analysis we decided to check whether the automatic memory resize operations is successfuly happening or not, following is the result:
WHEN COMPONENT OPER_TYPE INITIAL_SIZE/1000000 FINAL_SIZE/1000000 TARGET_SIZE/1000000 STATUS
------------------------- ------------------------- --------------------------------------- -------------------- ------------------ ------------------- ---------------------------
18.12.2019 02:52:29 shared pool GROW 13824,426 13891,5348 13891,5348 ERROR
18.12.2019 02:52:29 DEFAULT buffer cache SHRINK 2214,59251 2147,48365 2147,48365 ERROR
18.12.2019 02:52:29 shared pool GROW 13824,426 13891,5348 13891,5348 ERROR
18.12.2019 02:52:29 DEFAULT buffer cache SHRINK 2214,59251 2147,48365 2147,48365 ERROR
18.12.2019 02:52:29 shared pool GROW 13824,426 13891,5348 13891,5348 ERROR
18.12.2019 02:52:29 DEFAULT buffer cache SHRINK 2214,59251 2147,48365 2147,48365 ERROR
18.12.2019 02:52:29 shared pool GROW 13824,426 13891,5348 13891,5348 ERROR
18.12.2019 02:52:29 DEFAULT buffer cache SHRINK 2214,59251 2147,48365 2147,48365 ERROR
in fact, all Resize ops failed!
Then we checked the free Memory of the shared Pool (data saved in dba_hist_sgastat ) and we were shocked to see that FREE MEMORY of shared pool is enough (3431 MB were free at the time of Memory resize) …
SNAP_ID TIME INSTANCE_NUMBER NAME POOL A.BYTES/1000000
---------- ---------------------- --------------- --------------------------------- --------------------------------- ---------------
110920 18.12.2019 02:30:04 2 free memory shared pool 3431,94359
My question is, why oracle is trying to Grow shared pool by reducing DB Buffer when the database already has enough memory for the shared pool??????
Thx