Hi, We are using version-11.2.0.4 of oracle Exadata. We suddenly saw queries failing with error "ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select timestamp, flags from...","SQLA","tmp")". At this point we were not even able to execute any query in the database, so we flushed the shared pool immediately and things started getting smooth. Then we verify the dba_hist_sga_stat and found the specific memory component of shared_pool named as "KGH: NO ACCESS" was just keep on increasing consistently since last one month and reached the highest point today and so failed.
We are using ASMM here and we have sga_target set as~16gb and sga_max_size set as ~20gb, we have not set any value for shared_pool or buffer_cache. and i found from dba_hist_sga_stat during the failure the size of "KGH NO ACCESS" component was reached ~15GB. I found few of the documents as below, related to this exact symptom when the specific memory component(kgh no access) increase consistently in ASMM. And the solution has been provided as to set the minimum value of shared_pool and db_cache to fix this issue. Now we again saw similar issue in another database with version 11.2.0.4.
So i have two question here 1)How to determine the suitable value of shared_pool and db_cache at this point for all our databases. 2) As some of our DBAs stating rather evaluating the correct minimum size of shared_pool and buffer_cache, if we just make the sga_target equal to the sga_max_size that will have the similar effect. I am not able to correlate if this is correct way to go for and how logically?
How To Prevent The Growth Of The Component 'KGH: NO ACCESS' In The Shared Pool When ASMM Is Enabled (Doc ID 451960.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=443099286818694&id=451960.1&_afrWindowMode=0&_adf.ctrl-state=7tsc3bzk_49
KGH: NO ACCESS allocations in V$SGASTAT – buffer cache within shared pool!
https://blog.tanelpoder.com/2009/09/09/kgh-no-access-allocations-in-vsgastat-buffer-cache-within-shared-pool/
Bug 22748943 : ORA-4031 WITH HIGH ALLOCATIONS IN KGH: NO ACCESS
https://support.oracle.com/epmos/faces/BugDisplay?_afrLoop=443097156691711&id=22748943&_afrWindowMode=0&_adf.ctrl-state=7tsc3bzk_4