The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

venerdì, luglio 28, 2017

Uncontrolled growth of Shared Pool (bug 21373473)

In this days, I have some dbs that suffering of  ORA-04031. Just for info, I report the "oerr" output for this error

[oracle]/sbRdbms/oracle-$oerr ORA 04031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause:  More shared memory is needed than was allocated in the shared
//          pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
//          DBMS_SHARED_POOL package to pin large packages,
//          reduce your use of shared memory, or increase the amount of
//          available shared memory by increasing the value of the
//          initialization parameters SHARED_POOL_RESERVED_SIZE and
//          SHARED_POOL_SIZE.
//          If the large pool is out of memory, increase the initialization
//          parameter LARGE_POOL_SIZE.
//          If the error is issued from an Oracle Streams or XStream process,
//          increase the initialization parameter STREAMS_POOL_SIZE or increase
//          the capture or apply parameter MAX_SGA_SIZE.

So. This is my simple analysis

The SGA is 32GB.

SELECT sum(value)/1024/1024/1024 sga_gbytes 
FROM v$sga;


But the shared pool is more then 29GB

SELECT component, current_size/1024/1024/1024 gbytes 
FROM v$sga_dynamic_components

COMPONENT                GBYTES
-------------------- ----------
shared pool               29.25 <<<<<
DEFAULT buffer cache     1.5625
large pool                   .5
Shared IO Pool               .5
java pool                 .0625

So, I want to know which component of the shared pool is occupying more space

SELECT pool, name, bytes/1024/1024/1024 gbytes 
FROM v$sgastat 
WHERE pool='shared pool' 

POOL         NAME                           GBYTES
------------ -------------------------- ----------
shared pool  ges enqueues               14.7394131 <<<<<
shared pool  ges resource dynamic       7.26247394 <<<<<
shared pool  free memory                3.79368246
shared pool  gcs resources              .867172517
shared pool  gcs shadows                .481762514

14.739GB + 7.262GB = 22GB. So 29.25GB - 22GB = 6GB  (more or less), It's always a big Shared Pool, but much more human. I suspect that other components of the Shared Pool (here you can see "gcs resources" and "gcs shadows") could be less in size if I resolve the biggest issue.

If I see the history of the shared pool (here I report the output for just one instance), I see that the "shared pool" always grows while the "buffer cache" decreases:

---------- ------------------- -------------------- ------------- --------- -------------------- ----------
         2 17-07-2017 23:03:01 DEFAULT buffer cache SHRINK        IMMEDIATE       21,273,509,888         65
         2 18-07-2017 16:07:56 DEFAULT buffer cache GROW          DEFERRED        21,474,836,480          3
         2 18-07-2017 23:15:09 DEFAULT buffer cache SHRINK        IMMEDIATE       18,052,284,416         55
         2 19-07-2017 23:40:40 DEFAULT buffer cache SHRINK        IMMEDIATE        8,992,587,776        146
         2 20-07-2017 21:41:29 DEFAULT buffer cache SHRINK        IMMEDIATE        1,275,068,416        126
         2 20-07-2017 22:14:40 DEFAULT buffer cache GROW          DEFERRED         1,409,286,144          3
         2 21-07-2017 00:20:41 DEFAULT buffer cache GROW          DEFERRED         1,677,721,600          2
         2 17-07-2017 23:03:01 shared pool          GROW          IMMEDIATE       11,408,506,880         65
         2 18-07-2017 23:15:09 shared pool          GROW          IMMEDIATE       14,831,058,944         55
         2 19-07-2017 23:40:40 shared pool          GROW          IMMEDIATE       23,890,755,584        146
         2 20-07-2017 21:41:29 shared pool          GROW          IMMEDIATE       31,675,383,808        126
         2 20-07-2017 22:14:40 shared pool          SHRINK        DEFERRED        31,541,166,080          1
         2 21-07-2017 00:20:40 shared pool          SHRINK        DEFERRED        31,406,948,352          1

Browsing the internet (Saint Google), I found this link, where the author Marco Mischke have had the same problem.

He reports the Doc ID 2063751.1 from MOS: ORA-04031 Errors Occurring with High "ges resource dynamic" & "ges enqueues" Memory Usage In The Shared Pool

CAUSE: This is due to unpublished bug:21373473 fixed in 12.2, and occurs due to DX and BB locks being cached but not cleared out.  This fix removes the need to cache DX and BB locks and hence reduces overall shared pool memory usage in RAC instances.

SOLUTION: Download and apply the one-off patch number 21373473 (Patch:21373473) for your platform and version combination. Please note that if using, then you should also apply the related patch number 21260431 (Patch:21260431) which also impacts the shared pool memory allocations identified here. This issue can also be worked around by setting _GES_DIRECT_FREE_RES_TYPE="CTARAHDXBB" in the instances.

Nessun commento: