Hi Everyone,

Few days while working on Shared Pool issue where we were getting ORA-4031 on one of the 11gR2 database i discovered something interesting and new regarding the SGA duration management. Here i wont discuss the problem that i faced and how we fixed but would try to show the architectural changes that has happened with 12c that can now fix these errors/issues.

Let me explain what are ‘Durations’ first. The shared pool is made up of a number of granules. The shared pool then split into sub-pools if you have a large enough SGA, and each sub-pool consists of a number of non-overlapping granules. In 11g each sub-pool also split into four sub-sub-pools known as durations.

**What was there before 12c arrived** —

Starting from Oracle 10g each sub-pool in SGA was divided in to four durations.

Let’s check the distribution by generating the Heap Dump for shared pool, here i used oradebug with level 2 (This provides you the full SGA Summary or you can try with level 2050 to get full summary with contents).

SQL> oradebug setmypid Statement processed. SQL> oradebug dump heapdump 2 Statement processed. SQL> oradebug tracefile_name /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8127.trc ..... ****************************************************** HEAP DUMP heap name="sga heap(1,0)" desc=380030610 Total heap size =218102664 Total free space = 1066928 Total reserved free space = 8439520 Unpinned space = 38812528 rcr=11971 trn=17906 Permanent space =208595160 HEAP DUMP heap name="sga heap(1,1)" desc=380031e68 Total heap size = 67108512 Total free space = 2912528 Total reserved free space = 1382816 Unpinned space = 0 rcr=0 trn=0 Permanent space = 0 HEAP DUMP heap name="sga heap(1,2)" desc=3800336c0 Total heap size =167771280 Total free space = 92743480 Total reserved free space = 3852856 Unpinned space = 0 rcr=0 trn=0 Permanent space = 0 HEAP DUMP heap name="sga heap(1,3)" desc=380034f18 Total heap size =268434048 Total free space = 74547592 Total reserved free space = 13497472 Unpinned space = 0 rcr=0 trn=0 Permanent space = 0 HEAP DUMP heap name="sga heap(2,0)" desc=380039e38 Total heap size =201325536 Total free space = 17200 Total reserved free space = 8435920 Unpinned space = 26474112 rcr=7934 trn=8094 Permanent space =192871456 HEAP DUMP heap name="sga heap(2,1)" desc=38003b690 Total heap size = 83885640 Total free space = 48723768 Total reserved free space = 1035792 Unpinned space = 0 rcr=0 trn=0 Permanent space = 0 HEAP DUMP heap name="sga heap(2,2)" desc=38003cee8 Total heap size =369096816 Total free space =258674312 Total reserved free space = 16982464 Unpinned space = 0 rcr=0 trn=0 Permanent space = 0 HEAP DUMP heap name="sga heap(2,3)" desc=38003e740 Total heap size =218102664 Total free space = 17202608 Total reserved free space = 10966696 Unpinned space = 0 rcr=0 trn=0 Permanent space = 0 HEAP DUMP heap name="sga heap(3,0)" desc=380043660 Total heap size =184548408 Total free space = 13008 Total reserved free space = 5061928 Unpinned space = 26943408 rcr=4930 trn=9425 Permanent space =179472608 HEAP DUMP heap name="sga heap(3,1)" desc=380044eb8 Total heap size = 67108512 Total free space = 27568352 Total reserved free space = 4744 Unpinned space = 0 rcr=0 trn=0 Permanent space = 0 HEAP DUMP heap name="sga heap(3,2)" desc=380046710 Total heap size =352319688 Total free space =233302736 Total reserved free space = 15981216 Unpinned space = 0 rcr=0 trn=0 Permanent space = 0 HEAP DUMP heap name="sga heap(3,3)" desc=380047f68 Total heap size =385873944 Total free space =143746536 Total reserved free space = 19402616 Unpinned space = 0 rcr=0 trn=0 Permanent space = 0 ..... ******************************************************

So above stats shows that we have three sub-pools [Sub pool (1,0), (1,1), (1,2), (1,3) ….. (3,0), (3,1), (3,2), (3,3)] of SGA Heaps with Four Durations each heap. And every duration has its own size, free space and reserved free space. This type of distribution possibly causes the ORA 4031 even when you have enough free space in other durations and this is what the actual cause in my earlier case, but here we won’t discuss how we fixed that.

Okay so now **lets do the same with 12c database**. Lets generate the Heap Dump for SGA in 12c database and see the distributions of durations here. Once again we will use the oradebug to dump heapdump with Level 2.

SQL> oradebug setmypid Statement processed. SQL> oradebug dump heapdump 2 Statement processed. SQL> oradebug tracefile_name /u01/app/oracle/diag/rdbms/tunedb/tunedb/trace/tunedb_ora_11054.trc ****************************************************** HEAP DUMP heap name="sga heap(1,0)" desc=0x60103678 extent sz=0xfe0 alt=304 het=32767 rec=9 flg=0x82 opc=0 parent=(nil) owner=(nil) nex=(nil) xsz=0x1000000 heap=(nil) fl2=0x24, nex=(nil), idx=1, dsxvers=1, dsxflg=0x0 dsx first ext=0x73000000 dsx empty ext bytes=0 subheap rc link=0x730000c0,0x730000c0 ****************************************************** HEAP DUMP heap name="sga heap(1,3)" desc=0x60107f80 extent sz=0xfe0 alt=304 het=32767 rec=9 flg=0x82 opc=0 parent=(nil) owner=(nil) nex=(nil) xsz=0x1000000 heap=(nil) fl2=0x24, nex=(nil), idx=1, dsxvers=1, dsxflg=0x0 dsx first ext=0x7e000000 dsx empty ext bytes=0 subheap rc link=0x7e0000c0,0x7e0000c0

Alright, so here we only have two groups of SGA durations – ‘Sub pool 1, duration 0’ and ‘Sub Pool 1 duration 3’ for improved sharability and to avoid ORA 4031 errors.

**Hope It helps
Prashant Dixit**