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

Capture

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

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s