Oracle 12cR2 / ORA-04031 - Unable to allocate xx bytes of shared memory (Shared Pool) (Hard Parse & Parallel Queries)

Question

Sometimes I'm getting ORA-04031 error on my DWH database. The last one like this:

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

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 ("shared pool", "unknown object", "KGLH0^4ad1e9c3", "kglHeapInitialize:temp")

And another one:

ORA-00604: error occured at recursive SQL level 1

ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool", "unknown object", "KGLH0^4ad1e9c3", "kglHeapInitialize:temp")

My SGA parameters are as follows:

SQL> show parameter sga_target

NAME                                 TYPE        VALUE

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

sga_target                           big integer 10G

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE

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

sga_max_size                         big integer 10G

SQL> 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> 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> 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:

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

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> 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,

Answer 1

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: ("shared Pool".."KKSSP^nnnn","kglseshtSegs") (Doc ID 2151847.1)

SOLUTION

1. Download and apply Patch 23315153.

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

2. The fix is included in Oracle release 12.2.

The bug has no workaround.