Skip to Main Content
  • Questions

  • Strange behaviour of Automatic Memory Resize

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ahmad.

Asked: December 18, 2019 - 4:14 pm UTC

Last updated: January 13, 2020 - 3:13 am UTC

Version: 12.2.0.2

Viewed 1000+ times

You Asked

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

and Connor said...

Check out the following

ORA-04031: ("shared Pool".."KKSSP^nnnn","kglseshtSegs") (Doc ID 2151847.1)

There are patches available

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ahmad, January 07, 2020 - 9:05 am UTC

Hi,

thx for the Reply, our DB's version is Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production.


i checked the document, it says that the fix is included in Oracle release 12.2

I am not sure if that is the case especially the exception still occuring, i would like to check if the patch id 23315153 is installed, do you have a a way to check it via an SQL Stmt ? i know Opatch shows the installed Patches but i need to verify it with an sql statement so i can forward the stmt and the output to our management ..


thx

Connor McDonald
January 08, 2020 - 12:54 am UTC

Sorry, my mistake, I read your version incorrectly - I saw the "0.2" at the end (and since there is not a 12.2.0.2, I assumed 12.1)

I think its time you have a chat to Support.

Ahmad, January 08, 2020 - 8:17 am UTC

Hi,

Does the Version 12.2.0.1.0 include the patch of this bug ?

or you mean we should contact support because the patch is installed with the version that we are currently running (12.2.0.1.0) however the error is still occuring?


regards

Connor McDonald
January 10, 2020 - 4:09 am UTC

No, it was my mis-reading of your version.

*However* the reason I suggest chatting to Support is that looking at the "free memory" stat is somewhat of a misnomer, because it represents the total size of all pieces of free memory.

So the database might need 'n' bytes of memory for type "X", and there might not be sufficient, even though there might be plenty free of type "Y". So a growth in sga might be totally justified even if you have plenty of free memory.

Just *one* example of this is the duration attribute for share pool memory. Detail on that in MOS note 2382705.1, but you can see how just looking at total free memory does not paint the entire picture.

Support might get you to do some heap dumps etc to diagnose further.

A reader, January 10, 2020 - 8:42 am UTC

Hi,

ok, however looking back at the free Memory stats i can see that the free Memory of the component : shared pool was at the time big enough ~ 3431,94359


plz look again at below stats:
SNAP_ID      TIME                 INSTANCE_NUMBER  NAME         POOL       A.BYTES/1000000
---------- ---------------------- --------------- --------    ----------   ----------------  
110920       18.12.2019 02:30:04    2             free memory shared pool  3431,94359


Connor McDonald
January 13, 2020 - 3:13 am UTC

Sorry I wasn't very clear.

The *shared pool* itself is made up of many components of different types - my previous review was talking solely about *within* the shared pool.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.