0

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,

2
  • What do logs say? Is there an associated ORA-600? – Michael Kutz Nov 23 '20 at 15:13
  • @MichaelKutz Hi Michael, there is no ORA-600. – jrdba123 Nov 23 '20 at 16:10
0

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.

  1. The fix is included in Oracle release 12.2.

The bug has no workaround.

2
  • Hi Balazs. Which informations do you need? – jrdba123 Nov 23 '20 at 16:12
  • Hi Balazs. I edited my question. – jrdba123 Nov 23 '20 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.