Oracle 12cR2 / ORA-04031 - Unable to allocate xx bytes of shared memory (Shared Pool) (Hard Parse & Parallel Queries) Sometimes I'm getting ORA-04031 error on my DWH database. The last one like this:

&quot;ORA-04031: unable to allocate 56 bytes of shared memory (&quot;shared
pool&quot;, &quot;unknown object&quot;, &quot;KKSSP^4366&quot;, &quot;kglseshtSegs&quot;)&quot;

Here is another ORA-04031 for different days:

ORA-12801: error signaled in parallel query server ...
ORA-04031: unable to allocate 40 bytes of shared memory (&quot;shared
pool&quot;, &quot;unknown object&quot;, &quot;KGLH0^4ad1e9c3&quot;, &quot;kglHeapInitialize:temp&quot;)

And another one:

ORA-00604: error occured at recursive SQL level 1
ORA-04031: unable to allocate 40 bytes of shared memory (&quot;shared
pool&quot;, &quot;unknown object&quot;, &quot;KGLH0^4ad1e9c3&quot;, &quot;kglHeapInitialize:temp&quot;)

My SGA parameters are as follows:
SQL&gt; show parameter sga_target

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_target                           big integer 10G

SQL&gt; show parameter sga_max_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                         big integer 10G

SQL&gt; show parameter sga_min_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_min_size                         big integer 0

And here is my cursor_sharing parameter (Oracle recommends EXACT value for the best practice):
SQL&gt; show parameter cursor_sharing

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

cursor_sharing                       string      EXACT

And here is the my free memory for sharing pool (even if the values are changing, they are generally close to that):
SQL&gt; select inst_id, name, bytes/1024/1024 MB from gv$sgastat where pool='shared pool' and name='free memory';

   INST_ID NAME                                                                           MB

---------- ---------------------------------------------------------------------- ----------
       
  1        free memory                                                            183.724869
       
  2        free memory                                                            258.926163

In document for 12c, Oracle says:

&quot;If free memory is always available in the shared pool, then
increasing its size offers little or no benefit.&quot;

So, I will not increase the shared_pool size.
I'm suspecting hard parse and parallel queries for ORA-04031.
And here are some statistics:
SQL&gt; select name, sum(value) from gv$sesstat s1 join gv$statname s2 on s1.statistic#=s2.statistic# where name like '%parse count%' group by name order by 1,2;

NAME                                                                   SUM(VALUE)

---------------------------------------------------------------------- ----------

parse count (describe)                                                         68

parse count (failures)                                                        490

parse count (hard)                                                          40772

parse count (total)                                                       1295326

Here is a temporary (workaround) solution:
alter system flush shared_pool;

Do you have any advice to solve this problem completely? If other information is needed I can provide it.
NOTE: I edited my question.
Best regards,
With only that information, I can give only the below.
Yes, I am aware that the bug is fixed in 12.2 and you wrote in the title the database was 12.2 (but no other proof).
ORA-04031: (&quot;shared Pool&quot;..&quot;KKSSP^nnnn&quot;,&quot;kglseshtSegs&quot;) (Doc ID 2151847.1)

SOLUTION

Download and apply Patch 23315153.

In case the needed patch is not available, please open a Service
Request with Oracle Support.

The fix is included in Oracle release 12.2.

The bug has no workaround.