We have recently created a new UNDO tablespace on RECO tablespace because
of space crunch on DATA disk group. So basically all the DML and everything
happens on DATA whereas UNDO sits on RECO, so can it be the cause of such
issues anyway?
On Wed, May 5, 2021 at 2:00 PM Pap <oracle.developer35@xxxxxxxxx> wrote:
Thank you Tanel. We have *_enable_shared_pool_durations* set as TRUE.
And we don't see much of the resize operations for the node which came
across this error. Oracle suggests to set the minimum value for both
shared_pool and db_cache to get rid of this ora-4031. So wondering if this
will have a similar impact as setting _enable_shared_pool_durations to
FALSE? Another thing we noticed is post this error , we are seeing a lot of
queries running slow across databases, and there have been no change in
plan but the 'cell single block physical read' has been
increased significantly and eventually they are going for UNDO(i.e. High
transaction table consistent read undo record applied). Can this be related
to the memory error anyway?
On Tue, May 4, 2021 at 9:06 PM Tanel Poder <tanel@xxxxxxxxxxxxxx> wrote:
Since this is 11.2 and if you don't see shared pool shrink/grow
operations in v$sga_resize_ops - and since the failed allocation sizes are
pretty small (256 & 760 bytes, not 10+ kB and not even the standard 4k
extent size for library cache object heaps), I would suspect that you ran
out of memory in one of the shared sub-sub-pools for session-duration
allocations (KKSSP means "Session Pages" where session-connected things
like library cache lock & library cache pins are kept).
Oracle 12c splits your shared pool subpools into only 2 sub-sub-pools
(durations), but 11.2 splits them to 4.
What's the* _enable_shared_pool_durations* value in your env?
Also, you can take a look into number of shared pool sub-pools and their
free memory (although it doesn't show fragmentation info):
-
https://tanelpoder.com/2009/06/04/ora-04031-errors-and-monitoring-shared-pool-subpool-memory-utilization-with-sgastatxsql/
And since an ORA-4031 error should dump some shared pool heap details to
a tracefile, you can run heapdump analyzer on it, to see how much free
memory you had in the sub-sub-heaps at the time:
-
https://tanelpoder.com/2009/01/02/oracle-memory-troubleshooting-part-1-heapdump-analyzer/
On versions below 12c, when having unexplained shared pool memory errors
(ORA-4031s) and you don't want to go deeper with dynamic tracing
<https://github.com/tanelpoder/tpt-oracle/blob/master/dtrace/trace_kghal.sh>
and things like x$ksmlru
<https://github.com/tanelpoder/tpt-oracle/blob/master/ksmlru.sql>, then
a common workaround is to set *_enable_shared_pool_durations = false*
(with the usual comments that you should get some blessing from Oracle
support or by a MOS search for that parameter & documented bugs/issues).
In past (perhaps back in 9i, 10g days), I sometimes worked around the
unexplained shared pool issues (bugs), by reducing the *_kghdsidx_count*
value (and sometimes setting it to 1), to avoid the complexity (and new
bugs) of the shared pool subpools completely (but on 11.2.0.4 it probably
works ok enough...)
--
Tanel Poder
https://tanelpoder.com/events/
On Tue, May 4, 2021 at 9:02 AM Pap <oracle.developer35@xxxxxxxxx> wrote:
Hello Listers, It's a 4 node RAC database with version 11.2.0.4. It's
using ASMM. We saw queries failing with ORA-04031 error twice in the past ,
even if the sum of all the components of the shared_pool was around ~15GB
during the time(with sga_target being set as ~100GB on each node). We are
not seeing any such spike/variation in overall usage of the shared_pool
components but still getting this below error intermittently. .
And i remember, we used to see the same error in few other databases in
the past, but there we used to see the component "KGH-NO ACCESS" of shared
pool used to grow rapidly chewing up all the sga memory. But in this case
we are not seeing such symptoms and we seem to have free memory left while
it's errored out. So wondering if there is any associated bug?
*Parameters from V$parameter:-*
sga_max_size - 120GB
sga_target - 100GB
shared_pool_size - 0
memory_target - 0
*Error:*
ORA-04031: unable to allocate 760 bytes of shared memory ("shared
pool","unknown object","KKSSP^847","kglss")
ORA-04031: unable to allocate 256 bytes of shared memory ("shared
pool","unknown object","KKSSP^1807","kgllk")
ORA-04031: unable to allocate bytes of shared memory ("","","","")
Regards
Pap
