ORA-04031 Unable To Allocate 32 Bytes Of Shared Memory (shared pool,SQLA,tmp)

郑全

ORA-04031: Unable To Allocate 32 Bytes Of Shared Memory ("shared pool","select tablespace_id, rfno, ...","SQLA","tmp")" (Doc ID 1986741.1)       

In this Document

Symptoms

Cause

Solution

References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later

Oracle Database Cloud Schema Service - Version N/A and later

Oracle Database Exadata Cloud Machine - Version N/A and later

Oracle Cloud Infrastructure - Database Service - Version N/A and later

Oracle Database Exadata Express Cloud Service - Version N/A and later

Information in this document applies to any platform.

SYMPTOMS

Alert log reports errors like below in almost all the instances:

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select tablespace_id, rfno, ...","SQLA","tmp")"

CAUSE

The trace file shows there are 6 sga subpools allocated.

...

****************** End of process map dump ************

==============================================

TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 1

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

"KGH: NO ACCESS " 4056 MB 61%

"gcs dynamic s " 713 MB 11%

"free memory " 625 MB 9%

"gcs dynamic r " 501 MB 8%

"init_heap_kfsg " 195 MB 3%

"FileOpenBlock " 122 MB 2%

"gcs resources " 64 MB 1%

"ges enqueues " 62 MB 1%

"gcs shadows " 45 MB 1%

"PRTMV " 40 MB 1%

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

free memory 625 MB

memory alloc. 6031 MB

Sub total 6656 MB

==============================================

TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 2

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

"KGH: NO ACCESS " 3822 MB 71%

"free memory " 343 MB 6%

"SQLA " 223 MB 4%

"gcs dynamic r " 146 MB 3%

"KGLH0 " 126 MB 2%

"gcs dynamic s " 98 MB 2%

"gc name table " 96 MB 2%

"ges resource " 78 MB 1%

"PRTMV " 68 MB 1%

"gcs resources " 65 MB 1%

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

free memory 343 MB

memory alloc. 5033 MB

Sub total 5376 MB

==============================================

TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 3

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

"KGH: NO ACCESS " 3812 MB 80%

"free memory " 215 MB 5%

"SQLA " 112 MB 2%

"KGLH0 " 97 MB 2%

"ges resource " 80 MB 2%

"gcs resources " 66 MB 1%

"gcs shadows " 46 MB 1%

"PRTMV " 45 MB 1%

"KQR X PO " 44 MB 1%

"ges enqueues " 22 MB 0%

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

free memory 215 MB

memory alloc. 4521 MB

Sub total 4736 MB

==============================================

TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 4

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

"KGH: NO ACCESS " 3557 MB 79%

"free memory " 367 MB 8%

"KQR L PO " 79 MB 2%

"KGLH0 " 76 MB 2%

"gcs resources " 66 MB 1%

"SQLA " 61 MB 1%

"gcs shadows " 45 MB 1%

"PRTMV " 39 MB 1%

"ges enqueues " 16 MB 0%

"KGLHD " 12 MB 0%

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

free memory 367 MB

memory alloc. 4113 MB

Sub total 4480 MB

==============================================

TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 5

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

"KGH: NO ACCESS " 3689 MB 85%

"free memory " 215 MB 5%

"KGLH0 " 74 MB 2%

"gcs resources " 65 MB 2%

"gcs shadows " 44 MB 1%

"PRTMV " 40 MB 1%

"SQLA " 32 MB 1%

"ges enqueues " 24 MB 1%

"db_block_hash_buckets " 21 MB 0%

"KGLHD " 12 MB 0%

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

free memory 215 MB

memory alloc. 4137 MB

Sub total 4352 MB

==============================================

TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 6

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

"KGH: NO ACCESS " 3685 MB 76%

"free memory " 240 MB 5%

"gcs dynamic s " 138 MB 3%

"flashback generation buff " 122 MB 2%

"SQLA " 116 MB 2%

"gcs dynamic r " 106 MB 2%

"KGLH0 " 91 MB 2%

"gcs resources " 64 MB 1%

"gcs shadows " 44 MB 1%

"PRTMV " 41 MB 1%

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

free memory 240 MB

memory alloc. 4624 MB

Sub total 4864 MB

TOTALS ---------------------------------------

Total free memory 2005 MB

Total memory alloc. 28 GB

Grand total 30 GB

==============================================

There are 6 subpools created in the SGA and a high amount of memory is allocated to "KGH: NO ACCESS "

There are multiple bugs open on the same but on 11gR2 there is no actual fix.

SOLUTION

As a solution for the issue you can disable the use of durations.

The shared pool can have subpools with 4 durations. These durations are "instance", "session", "cursor", and "execution". By default these durations are separate from each other.

The disable durations, you have to set the underscore parameter "_enable_shared_pool_durations = false":

SQL> alter system set "_enable_shared_pool_durations"=false scope=spfile;

- restart the database

This is recommended by development for all similar issues.

The main advantage of "_enable_shared_pool_durations = false" is that all the durations are combined into one pool and so a duration will not run out while another duration has free memory.

This issue will be fixed in 12c version due to architectural changes which will allow less need of durations for subpools.

Note:

Please note that unpublished Enhancement Request Bug 8857940 - NEED COMMON DURATIONS FOR UNSHRINKABLE POOLS 8857940, fixed starting with 12.1.0.1, addresses the durations issue as well. With the fix for Bug 8857940 in place, Oracle permits to group the shared pool durations in 2 groups to allow better shareability of the memory and avoid the ORA-4031 errors.

Another possible solution is to apply Patch 8857940 if it is available for your platform and version.