ORACLE 12C : Shared Pool and durations enhancement

Recently when diagnosing a memory issue on the shared pool ora-4031 (11.2.0.3.9) i discovered that the root cause was related to duration management. Although, we have a lot of free space on every sub-pool and no fragmentation some duration was saturated.Riyaj Shamsudeen described a similar problem in his blog https://orainternals.wordpress.com/2009/08/06/ora-4031-and-shared-pool-duration/ so i will not discuss the problem i faced here. In this post i want to show some change that appeared in the 12c version that can reduce this type of problem.

Quick overview of duration (before 12c):

From oracle 10g and above each sub-pool in the shared pool was divided in 4 durations “instance”, “session”, “cursor”, and “execution”( as of metalink note Doc ID 1986741.1)

Capture

Let’s take a shared pool dump (oracle 11.2.0.3.9) :

HEAP DUMP heap name=”sga heap(1,0)” desc=c00000001fa31458
Total free space   =    11904
Total reserved free space   = 14556232
Unpinned space     = 40458752 rcr=9009 trn=11053
Permanent space    = 98673200
HEAP DUMP heap name=”sga heap(1,1)” desc=c00000001fa32cb0
Total free space   = 6733224
Total reserved free space   =   289984
Unpinned space     =        0 rcr=0 trn=0
Permanent space    =       80
HEAP DUMP heap name=”sga heap(1,2)” desc=c00000001fa34508
Total free space   =   100008
Total reserved free space   = 2498720
Unpinned space     =        0 rcr=0 trn=0
Permanent space    =       80
HEAP DUMP heap name=”sga heap(1,3)” desc=c00000001fa35d60
Total free space   = 17780512
Total reserved free space   = 4258232
Unpinned space     =        0 rcr=0 trn=0
Permanent space    =       80

Every duration has it’s own free list and Chunks are classified based upon their type(duration) so ORA-4031 errors are possible even if there is plenty of free space in other durations.(NOTE : most of the permanent chunck are allocated in duration 0)

The workaround in this case as metalink note (Doc ID 1675470.1) is

increasing the shared pool size

disabling durations by setting “_enable_shared_pool_durations”=false

Duration in 12c :

As of metalink note (Doc ID 1675470.1) and the implementation of “Bug 8857940 – Enh to group durations to reduce ora-4031 ” durations in the shared pool are grouped in 2 groups to allow better shareability of the memory (granule movement ) and avoid ora-4031.

Let’s take a shared pool dump (oracle 12.1.0.2.1) :

HEAP DUMP heap name=”sga heap(1,0)” desc=c0000000161674f0
Total free space   = 11830560
Total of this reserved free space   =   128000
Total of this reserved free space   =    11672
Total of this reserved free space   = 4013344
Total of this reserved free space   = 4193936
Total of all reserved free space   = 8346952
Unpinned space     = 91734272 rcr=17454 trn=19658
Permanent space    =151936112
HEAP DUMP heap name=”sga heap(1,3)” desc=c00000001616bdf8
Total free space   =105034520
Total of this reserved free space   = 21826784
Unpinned space     =        0 rcr=0 trn=0
Permanent space    =      112

As indicated by the shared pool dump there are only two goups of duration sga heap(1,0) (group sga heap(1,0) and sga heap(1,1) ) and sga heap(1,3) (group sga heap(1,2) and sga heap(1,3) ).

That’s it 😀

Ref : http://www.google.com/patents/US6253226