Search

OakieTags

Who's online

There are currently 0 users and 39 guests online.

Recent comments

Affiliations

EXPLAIN PLAN shared memory

Recently I did an investigation of an ORA-04031 which happens almost regularly on a 10.2.0.4 Oracle database server with 9G of memory allocated to shared pool and disabled Automatic Shared Memory Management (sga_target=0). The problem query is a report that is a very big SQL query – more than 200K of plain text. The exact error message is “ORA-04031: unable to allocate 235704 bytes of shared memory (“shared pool”,”select * from (…”,”Typecheck”,”qry_text : qcpisqt”)”. Here I’ll briefly describe what I did to identify root cause of the error and will talk about one interesting detail of the EXPLAIN PLAN as it was used to reproduce the issue.

So there’re quite a number of good docs out there on the subject of ORA-04031, such as

  • ORA-04031 errors and monitoring shared pool subpool memory utilization with sgastatx.sql
  • Oracle Troubleshooting TV Show: Season 1, Episode 01 ;-)
  • Even before I started to investigate the issue I was almost 100% sure that the root cause is memory fragmentation and just too large query that cannot find a continuous memory chunk. I tried to confirm my assumption on a development database with 2GB shared pool. The error was reproduced here as well when I tried to EXPLAIN PLAN for the query I didn’t have access to the X$ stuff on this DB to run some queries, but I had ALTER SESSION grant and was able to get a heap dump of the Shared Pool with

    alter session set events '4031 trace name heapdump level 2';

    Then I used script heapdump_analyzer from TPT to aggregate trace results and see how many usable chunks of size greater than 200000 bytes available. Not many:





    Total_size #Chunks Chunk_siz From_heap, Chunk_type, Alloc_reason
    22666392 27 839496 heap(4,0), R-free,  
    22666392 27 839496 heap(3,0), R-free,  
    22666392 27 839496 heap(2,0), R-free,  
    20020800 86 232800 heap(1,0), R-freeable, sql area
    4190296 1 4190296 heap(1,0), recreate, KSFD SGA I/O b
    1044584 1 1044584 heap(1,0), recreate, PX subheap
    626576 1 626576 heap(2,0), R-free,  
    617112 1 617112 heap(3,0), R-free,  
    538048 2 269024 heap(4,0), freeable, PX msg pool
    351208 1 351208 heap(3,0), R-free,  
    347808 1 347808 heap(4,0), R-free,  
    308568 1 308568 heap(4,0), freeable, character set o
    269024 1 269024 heap(3,0), freeable, PX msg pool
    267360 1 267360 heap(3,0), R-free,  
    242016 1 242016 heap(4,0), R-free,  
    232800 1 232800 heap(1,0), freeable, sql area
    220840 1 220840 heap(1,0), free,  
    216632 1 216632 heap(1,0), free,  
    215392 1 215392 heap(1,0), free,  
    211344 1 211344 heap(1,0), free,  
    202856 1 202856 heap(2,0), R-free,  
    202520 1 202520 heap(2,0), R-free,  
    201744 1 201744 heap(1,0), free,  

    So there are very few chunks that are free; there’s no free chunk of appropriate (235704 bytes) size in sub-pool 1. And this was actually with half-full shared pool, i.e. v$sgastat reported 1GB of free memory out of 2GB shared pool. But still it was too fragmented to find a single big enough memory piece.
    The interesting part begins when the query that is constantly failing with ORA-04031 when doing EXPLAIN PLAN, executes perfectly well many times, even with a hard parse! That was really surprising and hard to explain. A co-worker of mine noticed that it could be due to a simple but also unclear fact that EXPLAIN PLAN shared cursor uses more memory – much more actually. Here is a simple script to demonstrate it with very straightforward query:

    set serveroutput off
    col uq_id new_value uq_id
    select to_char(sysdate, 'YYYYMMDDHH24MISS') uq_id from dual;
    
    select /*+ &uq_id */ count(*) from dual;
    
    select sharable_mem
      from v$sql 
     where sql_id = (select s.prev_sql_id from v$session s where sid = userenv('sid'))
       and child_number = (select s.prev_child_number from v$session s where sid = userenv('sid'));
    
    explain plan for select /*+ &uq_id */ count(*) from dual;
    
    select sharable_mem
      from v$sql 
     where sql_id = (select s.prev_sql_id from v$session s where sid = userenv('sid'))
       and child_number = (select s.prev_child_number from v$session s where sid = userenv('sid'));
    

    Here I’m using a specific string inside a comment to make sure that shared cursor has exactly 1 child cursor. And that is results I’ve got running script on different Oracle versions:

    Version Normal query EXPLAIN PLAN
    10.2.0.5 32-bit 8996 9289
    10.2.0.4 64-bit 10584 15321
    11.2.0.2 64-bit 15600 36881
    11.2.0.3 64-bit 15624 36905 (actual memory allocated appears to be much higher than reported)

    It’s clear that something is making EXPLAIN PLAN sharable cursors bigger for some reason. Thanks to Jonathan Lewis I found a MOS Bug 6242723: EXPLAIN PLAN FOR DOES USE MORE MEMORY (closed as not a bug) which explains what happened in Oracle version 10g: some memory, such as “typecheck” that was put into CGA in previous versions is now placed into SGA. There’s also described a way to see what’s in this memory. For some reason I wasn’t able to get the data the same way as described in the document. I did it little bit differently, but logically it’s the same way, /me thinks. It was done on the 11.2.0.3 @ Win7 64-bit.
    Dump library cache at some very high level (not a good idea trying this on a loaded system):

    SQL> alter session set events 'immediate trace name library_cache level 16';
    
    Session altered.
    

    In the trace file found two library cache objects corresponding to the two statements with all information about heaps that are being used by the cursors. This is it cut to a minimum:

    Bucket: #=49063 Mutex=000007FF59A09748(0, 11, 0, 6)
      LibraryHandle:  Address=000007FF5D435088 Hash=d36abfa7 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
        ObjectName:  Name=select /*+ 20111124204027 */ count(*) from dual
          FullHashValue=669e3c9bfa2348f7de703912d36abfa7 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3546988455 OwnerIdn=90
    ...
        LibraryObject:  Address=000007FF5732E0E0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
          DataBlocks:
            Block:  #='0' name=KGLH0^d36abfa7 pins=0 Change=NONE
              Heap=000007FF5D434FD0 Pointer=000007FF5732E180 Extent=000007FF5732E060 Flags=I/-/P/A/-/-
              FreedLocation=0 Alloc=2.437500 Size=3.976563 LoadTime=2667103
          ChildTable:  size='16'
            Child:  id='0' Table=000007FF5732EF90 Reference=000007FF5732E9D0 Handle=000007FF5D434C28
          Children:
            Child:  childNum='0'
    ...
                  DataBlocks:
                    Block:  #='0' name=KGLH0^d36abfa7 pins=0 Change=NONE
                      Heap=000007FF5D434B70 Pointer=000007FF5732D180 Extent=000007FF5732D060 Flags=I/-/-/A/-/-
                      FreedLocation=0 Alloc=2.500000 Size=3.937500 LoadTime=2667103
                    Block:  #='6' name=SQLA^d36abfa7 pins=0 Change=NONE
                      Heap=000007FF5732E7A0 Pointer=000007FF53FDAC88 Extent=000007FF53FDA030 Flags=I/-/-/A/-/E
                      FreedLocation=0 Alloc=4.953125 Size=7.898438 LoadTime=0
                NamespaceDump:
                  Child Cursor:  Heap0=000007FF5732D180 Heap6=000007FF53FDAC88 Heap0 Load Time=11-24-2011 20:40:26 Heap6 Load Time=11-24-2011 20:40:26
        NamespaceDump:
          Parent Cursor:  sql_id=dww1t2b9qpgx7 parent=000007FF5732E180 maxchild=1 plk=n ppn=n
    
    Bucket: #=95957 Mutex=000007FF59BD3678(0, 9, 0, 6)
      LibraryHandle:  Address=000007FF5D3F7918 Hash=60ed76d5 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
        ObjectName:  Name=explain plan for select /*+ 20111124204027 */ count(*) from dual
          FullHashValue=c6f3b5c0c9bd54209f7b684560ed76d5 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=1626175189 OwnerIdn=90
    ...
        LibraryObject:  Address=000007FF5731B0E0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
          DataBlocks:
            Block:  #='0' name=KGLH0^60ed76d5 pins=0 Change=NONE
              Heap=000007FF5D3F7860 Pointer=000007FF5731B180 Extent=000007FF5731B060 Flags=I/-/P/A/-/-
              FreedLocation=0 Alloc=2.437500 Size=3.976563 LoadTime=2667164
          ChildTable:  size='16'
            Child:  id='0' Table=000007FF5731BF90 Reference=000007FF5731B9D0 Handle=000007FF5D3F76C8
          Children:
            Child:  childNum='0'
    ...
                  DataBlocks:
                    Block:  #='0' name=KGLH0^60ed76d5 pins=0 Change=NONE
                      Heap=000007FF5D3F7610 Pointer=000007FF5731A180 Extent=000007FF5731A060 Flags=I/-/-/A/-/-
                      FreedLocation=0 Alloc=2.500000 Size=3.937500 LoadTime=2667164
                    Block:  #='6' name=SQLA^60ed76d5 pins=0 Change=NONE
                      Heap=000007FF5731B7A0 Pointer=000007FF53F91C88 Extent=000007FF53F91030 Flags=I/-/-/A/-/E
                      FreedLocation=0 Alloc=119.320313 Size=123.000000 LoadTime=0
                NamespaceDump:
                  Child Cursor:  Heap0=000007FF5731A180 Heap6=000007FF53F91C88 Heap0 Load Time=11-24-2011 20:40:26 Heap6 Load Time=11-24-2011 20:40:26
        NamespaceDump:
          Parent Cursor:  sql_id=9yyv88phfuxqp parent=000007FF5731B180 maxchild=1 plk=n ppn=n
    

    For each cursor there’s a list with one child cursor in it. Each child cursor, in turn, has references to two sub-heaps, 0 and 6. Dump of memory for sub-heaps 6:

    SQL> alter session set events 'immediate trace name heapdump_addr address 0x000007FF5732E7A0';
    
    Session altered.
    
    SQL> alter session set events 'immediate trace name heapdump_addr address 0x000007FF5731B7A0';
    
    Session altered.
    

    And this is a comparison of what is in trace for normal query and for the query with EXPLAIN PLAN:

    Normal query EXPLAIN PLAN
    HEAP DUMP heap name="SQLA^d36abfa7"  desc=000007FF5732E7A0
     extent sz=0xfe8 alt=32767 het=368 rec=0 flg=2 opc=2
     parent=00000001492BD1C0 owner=000007FF5732E660 nex=0000000000000000 xsz=0xfe8 heap=0000000000000000
     fl2=0x27, nex=0000000000000000, dsxvers=1, dsxflg=0x0
     dsx first ext=0x53fda030
    EXTENT 0 addr=000007FF53FD9018
      Chunk      7ff53fd9028 sz=     2936    free      "               "
      Chunk      7ff53fd9ba0 sz=       40    freeable  "kggsmInitCompac"
      Chunk      7ff53fd9bc8 sz=       32    freeable  "kggsmInitCompac"
      Chunk      7ff53fd9be8 sz=       32    freeable  "kggsmInitCompac"
      Chunk      7ff53fd9c08 sz=       40    freeable  "kggsmInitCompac"
      Chunk      7ff53fd9c30 sz=       32    freeable  "kggsmInitCompac"
      Chunk      7ff53fd9c50 sz=      144    freeable  "kggsmCommonInit"
      Chunk      7ff53fd9ce0 sz=       80    freeable  "kggsmInit:sm   "
      Chunk      7ff53fd9d30 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53fd9d58 sz=       48    freeable  "qeSel: qkxrXfor"
      Chunk      7ff53fd9d88 sz=       48    freeable  "kggac: kggacCre"
      Chunk      7ff53fd9db8 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53fd9de0 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53fd9e08 sz=      104    freeable  "opiprwd : opitc"
      Chunk      7ff53fd9e70 sz=      232    freeable  "pqctx:kkfdParal"
      Chunk      7ff53fd9f58 sz=       80    freeable  "ctxPlanSig:qksc"
      Chunk      7ff53fd9fa8 sz=       88    freeable  "KGHSC_ALLOC_BUF"
    EXTENT 1 addr=000007FF53FDA030
      Chunk      7ff53fda040 sz=       80    perm      "perm           "  alo=80
      Chunk      7ff53fda090 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53fda0b8 sz=       32    freeable  "kksoff : opitca"
      Chunk      7ff53fda0d8 sz=       40    freeable  "qeeRwo: qeeCrea"
      Chunk      7ff53fda100 sz=       64    freeable  "kksol : kksnsg "
      Chunk      7ff53fda140 sz=      128    freeable  "qeeOpt: qeesCre"
      Chunk      7ff53fda1c0 sz=       40    freeable  "qeeRwo: qeeCrea"
      Chunk      7ff53fda1e8 sz=      216    freeable  "qergss:qergsAll"
      Chunk      7ff53fda2c0 sz=       40    freeable  "qeeRwo: qeeCrea"
      Chunk      7ff53fda2e8 sz=       88    freeable  "qerfis : rfialo"
      Chunk      7ff53fda340 sz=       72    freeable  "qksmm: qksmmCs "
      Chunk      7ff53fda388 sz=       40    freeable  "xplGenXpl:planL"
      Chunk      7ff53fda3b0 sz=       32    freeable  "qkaReorderAggs."
      Chunk      7ff53fda3d0 sz=       96    freeable  "qkaapd : qkaqkn"
      Chunk      7ff53fda430 sz=      400    freeable  "opixpop:kctdef "
      Chunk      7ff53fda5c0 sz=       32    freeable  "opixfalo:froaty"
      Chunk      7ff53fda5e0 sz=       32    freeable  "opixfalo:ctxkct"
      Chunk      7ff53fda600 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53fda628 sz=       32    freeable  "kobjn : kkdcchs"
      Chunk      7ff53fda648 sz=       64    freeable  "cxach : opiSem "
      Chunk      7ff53fda688 sz=       80    freeable  "ctxqrol : kkqsr"
      Chunk      7ff53fda6d8 sz=       48    freeable  "qksrcMarkQB:qks"
      Chunk      7ff53fda708 sz=       48    freeable  "ktamd : ktagmd "
      Chunk      7ff53fda738 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53fda760 sz=      136    freeable  "audRegFro:audta"
      Chunk      7ff53fda7e8 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53fda810 sz=      400    freeable  "kctdef : qcdlgo"
      Chunk      7ff53fda9a0 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53fda9c8 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53fda9f0 sz=       64    freeable  "unmdef in opipr"
      Chunk      7ff53fdaa30 sz=       48    freeable  "qctctx: kkmqccr"
      Chunk      7ff53fdaa60 sz=       72    freeable  "qcsctx: kkmqccr"
      Chunk      7ff53fdaaa8 sz=       40    freeable  "qcptgc: kkmqccr"
      Chunk      7ff53fdaad0 sz=       80    freeable  "qcpctx: kkmqccr"
      Chunk      7ff53fdab20 sz=       56    freeable  "qcmemctx : kkmq"
      Chunk      7ff53fdab58 sz=      152    freeable  "qcctx : kkmqccr"
      Chunk      7ff53fdabf0 sz=       64    freeable  "kksol : kkscuf "
      Chunk      7ff53fdac30 sz=       64    freeable  "kksol : kkscuf "
      Chunk      7ff53fdac70 sz=      912    freeable  "ctxdef:kksLoadC"
    Total heap size    =     8088
    FREE LISTS:
     Bucket 0 size=152
      Chunk      7ff53fda060 sz=        0    kghdsx
     Bucket 1 size=280
     Bucket 2 size=536
     Bucket 3 size=1048
     Bucket 4 size=2072
      Chunk      7ff53fd9028 sz=     2936    free      "               "
     Bucket 5 size=4120
     Bucket 6 size=4144
     Bucket 7 size=4168
     Bucket 8 size=4192
     Bucket 9 size=4216
    Total free space   =     2936
    UNPINNED RECREATABLE CHUNKS (lru first):
    PERMANENT CHUNKS:
      Chunk      7ff53fda040 sz=       80    perm      "perm           "  alo=80
    Permanent space    =       80
    
    HEAP DUMP heap name="SQLA^60ed76d5"  desc=000007FF5731B7A0
     extent sz=0xfe8 alt=32767 het=368 rec=0 flg=2 opc=2
     parent=00000001492BD1C0 owner=000007FF5731B660 nex=0000000000000000 xsz=0xfe8 heap=0000000000000000
     fl2=0x27, nex=0000000000000000, dsxvers=1, dsxflg=0x0
     dsx first ext=0x53f91030
    EXTENT 0 addr=000007FF53F72F28
      Chunk      7ff53f72f38 sz=     3688    free      "               "
      Chunk      7ff53f73da0 sz=       40    freeable  "kggsmInitCompac"
      Chunk      7ff53f73dc8 sz=       32    freeable  "kggsmInitCompac"
      Chunk      7ff53f73de8 sz=       32    freeable  "kggsmInitCompac"
      Chunk      7ff53f73e08 sz=       40    freeable  "kggsmInitCompac"
      Chunk      7ff53f73e30 sz=      144    freeable  "kggsmCommonInit"
      Chunk      7ff53f73ec0 sz=       80    freeable  "kggsmInit:sm   "
    EXTENT 1 addr=000007FF53F73F28
      Chunk      7ff53f73f38 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 2 addr=000007FF53F74F28
      Chunk      7ff53f74f38 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 3 addr=000007FF53F75F28
      Chunk      7ff53f75f38 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 4 addr=000007FF53F76F28
      Chunk      7ff53f76f38 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 5 addr=000007FF53F77F28
      Chunk      7ff53f77f38 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 6 addr=000007FF53F78F28
      Chunk      7ff53f78f38 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 7 addr=000007FF53F79F28
      Chunk      7ff53f79f38 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 8 addr=000007FF53F7AF28
      Chunk      7ff53f7af38 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 9 addr=000007FF53F7BF28
      Chunk      7ff53f7bf38 sz=     4176    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 10 addr=000007FF53F7CFA0
      Chunk      7ff53f7cfb0 sz=     4176    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 11 addr=000007FF53F7E018
      Chunk      7ff53f7e028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 12 addr=000007FF53F7F018
      Chunk      7ff53f7f028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 13 addr=000007FF53F80018
      Chunk      7ff53f80028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 14 addr=000007FF53F81018
      Chunk      7ff53f81028 sz=       64    freeable  "kggsmInitCompac"
      Chunk      7ff53f81068 sz=       48    freeable  "qeSel: qkxrXfor"
      Chunk      7ff53f81098 sz=       48    freeable  "kggac: kggacCre"
      Chunk      7ff53f810c8 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f810f0 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f81118 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f81140 sz=      232    freeable  "pqctx:kkfdParal"
      Chunk      7ff53f81228 sz=       80    freeable  "ctxPlanSig:qksc"
      Chunk      7ff53f81278 sz=       88    freeable  "KGHSC_ALLOC_BUF"
      Chunk      7ff53f812d0 sz=       40    freeable  "qeeRwo: qeeCrea"
      Chunk      7ff53f812f8 sz=       64    freeable  "kksol : kksnsg "
      Chunk      7ff53f81338 sz=      128    freeable  "qeeOpt: qeesCre"
      Chunk      7ff53f813b8 sz=       40    freeable  "qeeRwo: qeeCrea"
      Chunk      7ff53f813e0 sz=      216    freeable  "qergss:qergsAll"
      Chunk      7ff53f814b8 sz=       40    freeable  "qeeRwo: qeeCrea"
      Chunk      7ff53f814e0 sz=       88    freeable  "qerfis : rfialo"
      Chunk      7ff53f81538 sz=       72    freeable  "qksmm: qksmmCs "
      Chunk      7ff53f81580 sz=       40    freeable  "xplGenXpl:planL"
      Chunk      7ff53f815a8 sz=       32    freeable  "qkaReorderAggs."
      Chunk      7ff53f815c8 sz=       96    freeable  "qkaapd : qkaqkn"
      Chunk      7ff53f81628 sz=      400    freeable  "opixpop:kctdef "
      Chunk      7ff53f817b8 sz=       32    freeable  "opixfalo:froaty"
      Chunk      7ff53f817d8 sz=       32    freeable  "opixfalo:ctxkct"
      Chunk      7ff53f817f8 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f81820 sz=       32    freeable  "kobjn : kkdcchs"
      Chunk      7ff53f81840 sz=       64    freeable  "cxach : opiSem "
      Chunk      7ff53f81880 sz=       80    freeable  "ctxqrol : kkqsr"
      Chunk      7ff53f818d0 sz=       48    freeable  "qksrcMarkQB:qks"
      Chunk      7ff53f81900 sz=       48    freeable  "ktamd : ktagmd "
      Chunk      7ff53f81930 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f81958 sz=      136    freeable  "audRegFro:audta"
      Chunk      7ff53f819e0 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f81a08 sz=      400    freeable  "kctdef : qcdlgo"
      Chunk      7ff53f81b98 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f81bc0 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f81be8 sz=       88    freeable  "qbpdef: qekbCre"
      Chunk      7ff53f81c40 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f81c68 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f81c90 sz=      568    freeable  "frodef:qcpitnm "
      Chunk      7ff53f81ec8 sz=       48    freeable  "idndef : qcpiex"
      Chunk      7ff53f81ef8 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f81f20 sz=      112    freeable  "optdef: qcopCre"
      Chunk      7ff53f81f90 sz=      112    freeable  "qcpifqtqc : qcs"
    EXTENT 15 addr=000007FF53F82018
      Chunk      7ff53f82028 sz=     4056    freeable  "qbcqtcHTHeap   "  ds=000007FF53F91310
    EXTENT 16 addr=000007FF53F83018
      Chunk      7ff53f83028 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f83050 sz=       64    freeable  "seldef: qcopCre"
      Chunk      7ff53f83090 sz=     3952    freeable  "qbcqtcHTHeap   "  ds=000007FF53F91310
    EXTENT 17 addr=000007FF53F84018
      Chunk      7ff53f84028 sz=     4056    freeable  "qbcqtcHTHeap   "  ds=000007FF53F91310
    EXTENT 18 addr=000007FF53F85018
      Chunk      7ff53f85028 sz=       64    freeable  "opldef: qcopCre"
      Chunk      7ff53f85068 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f85090 sz=     3952    freeable  "qbcqtcHTHeap   "  ds=000007FF53F91310
    EXTENT 19 addr=000007FF53F86018
      Chunk      7ff53f86028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 20 addr=000007FF53F87018
      Chunk      7ff53f87028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 21 addr=000007FF53F88018
      Chunk      7ff53f88028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 22 addr=000007FF53F89018
      Chunk      7ff53f89028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 23 addr=000007FF53F8A018
      Chunk      7ff53f8a028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 24 addr=000007FF53F8B018
      Chunk      7ff53f8b028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 25 addr=000007FF53F8C018
      Chunk      7ff53f8c028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 26 addr=000007FF53F8D018
      Chunk      7ff53f8d028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 27 addr=000007FF53F8E018
      Chunk      7ff53f8e028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 28 addr=000007FF53F8F018
      Chunk      7ff53f8f028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 29 addr=000007FF53F90018
      Chunk      7ff53f90028 sz=     4056    recreate  "TCHK^60ed76d5  "  latch=0000000000000000
         ds      7ff53f91b38 sz=    97584 ct=       24
                 7ff53f73f38 sz=     4056
                 7ff53f74f38 sz=     4056
                 7ff53f75f38 sz=     4056
                 7ff53f76f38 sz=     4056
                 7ff53f77f38 sz=     4056
                 7ff53f78f38 sz=     4056
                 7ff53f79f38 sz=     4056
                 7ff53f7af38 sz=     4056
                 7ff53f7bf38 sz=     4176
                 7ff53f7cfb0 sz=     4176
                 7ff53f7e028 sz=     4056
                 7ff53f7f028 sz=     4056
                 7ff53f80028 sz=     4056
                 7ff53f86028 sz=     4056
                 7ff53f87028 sz=     4056
                 7ff53f88028 sz=     4056
                 7ff53f89028 sz=     4056
                 7ff53f8a028 sz=     4056
                 7ff53f8b028 sz=     4056
                 7ff53f8c028 sz=     4056
                 7ff53f8d028 sz=     4056
                 7ff53f8e028 sz=     4056
                 7ff53f8f028 sz=     4056
    EXTENT 30 addr=000007FF53F91030
      Chunk      7ff53f91040 sz=       80    perm      "perm           "  alo=80
      Chunk      7ff53f91090 sz=      616    recreate  "qbcqtcHTHeap   "  latch=0000000000000000
         ds      7ff53f91310 sz=    16632 ct=        5
                 7ff53f82028 sz=     4056
                 7ff53f83090 sz=     3952
                 7ff53f84028 sz=     4056
                 7ff53f85090 sz=     3952
      Chunk      7ff53f912f8 sz=      184    freeable  "qcpifqtqc : kgh"
      Chunk      7ff53f913b0 sz=       40    freeable  "qcpifqtqc : qbc"
      Chunk      7ff53f913d8 sz=       48    freeable  "qbcqut : qcpisq"
      Chunk      7ff53f91408 sz=      720    freeable  "qbcdef:qcpiqbk "
      Chunk      7ff53f916d8 sz=      584    freeable  "xpl : prsxpl   "
      Chunk      7ff53f91920 sz=       64    freeable  "unmdef in opipr"
      Chunk      7ff53f91960 sz=       48    freeable  "qctctx: kkmqccr"
      Chunk      7ff53f91990 sz=       72    freeable  "qcsctx: kkmqccr"
      Chunk      7ff53f919d8 sz=       40    freeable  "qcptgc: kkmqccr"
      Chunk      7ff53f91a00 sz=       80    freeable  "qcpctx: kkmqccr"
      Chunk      7ff53f91a50 sz=       56    freeable  "qcmemctx : kkmq"
      Chunk      7ff53f91a88 sz=      152    freeable  "qcctx : kkmqccr"
      Chunk      7ff53f91b20 sz=      208    freeable  "Typecheck heap "
      Chunk      7ff53f91bf0 sz=       64    freeable  "kksol : kkscuf "
      Chunk      7ff53f91c30 sz=       64    freeable  "kksol : kkscuf "
      Chunk      7ff53f91c70 sz=      912    freeable  "ctxdef:kksLoadC"
    Total heap size    =   125952
    FREE LISTS:
     Bucket 0 size=152
      Chunk      7ff53f91060 sz=        0    kghdsx
     Bucket 1 size=280
     Bucket 2 size=536
     Bucket 3 size=1048
     Bucket 4 size=2072
      Chunk      7ff53f72f38 sz=     3688    free      "               "
     Bucket 5 size=4120
     Bucket 6 size=4144
     Bucket 7 size=4168
     Bucket 8 size=4192
     Bucket 9 size=4216
    Total free space   =     3688
    UNPINNED RECREATABLE CHUNKS (lru first):
    PERMANENT CHUNKS:
      Chunk      7ff53f91040 sz=       80    perm      "perm           "  alo=80
    Permanent space    =       80
    

    So one of the biggest differences in the EXPLAIN PLAN is presence of TCHK allocations, presumably this is “typecheck” allocations. 22 of them are 4056 bytes and 2 are 4176 bytes, totaling 97584 bytes of shared memory. It’s possible to go further and dump what is inside this allocations:

    SQL> alter session set events 'immediate trace name heapdump_addr address 0x000007FF53F91B38';
    
    Session altered.

    HEAP DUMP heap name="TCHK^60ed76d5"  desc=000007FF53F91B38
     extent sz=0xfc0 alt=32767 het=32767 rec=0 flg=2 opc=2
     parent=000007FF5731B7A0 owner=000007FF5731B660 nex=0000000000000000 xsz=0xfc0 heap=0000000000000000
     fl2=0x27, nex=0000000000000000, dsxvers=1, dsxflg=0x0
     dsx first ext=0x53f90058
    EXTENT 0 addr=000007FF53F73F50
      Chunk      7ff53f73f60 sz=     2936    free      "               "
      Chunk      7ff53f74ad8 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f74b00 sz=       32    freeable  "xplCopyRow:qkbl"
      Chunk      7ff53f74b20 sz=      520    freeable  "xplins:xplrow  "
      Chunk      7ff53f74d28 sz=      488    freeable  "KGHSC_ALLOC_BUF"
    EXTENT 1 addr=000007FF53F74F50
      Chunk      7ff53f74f60 sz=      104    freeable  "qksxaMoveList:x"
      Chunk      7ff53f74fc8 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f75028 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f75088 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f750e8 sz=     3624    freeable  "kggec.c.kggfa  "
    EXTENT 2 addr=000007FF53F75F50
      Chunk      7ff53f75f60 sz=       88    freeable  "qolHintAlloc:pi"
      Chunk      7ff53f75fb8 sz=       56    freeable  "qolHintAlloc:pi"
      Chunk      7ff53f75ff0 sz=       56    freeable  "qolHintAlloc:pi"
      Chunk      7ff53f76028 sz=      224    freeable  "qolHintAlloc:ne"
      Chunk      7ff53f76108 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f76168 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f761c8 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f76228 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f76288 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f762e8 sz=      520    freeable  "xplins:xplrow  "
      Chunk      7ff53f764f0 sz=       96    freeable  "qolHintAlloc:pi"
      Chunk      7ff53f76550 sz=      224    freeable  "qolHintAlloc:ne"
      Chunk      7ff53f76630 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f76690 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f766e8 sz=     2088    freeable  "kggec.c.kggfa  "
    EXTENT 3 addr=000007FF53F76F50
      Chunk      7ff53f76f60 sz=       80    freeable  "qolHintAlloc:pi"
      Chunk      7ff53f76fb0 sz=      224    freeable  "qolHintAlloc:ne"
      Chunk      7ff53f77090 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f770e8 sz=     3624    freeable  "kggec.c.kggfa  "
    EXTENT 4 addr=000007FF53F77F50
      Chunk      7ff53f77f60 sz=       72    freeable  "chedef : qcuatc"
      Chunk      7ff53f77fa8 sz=     3944    freeable  "kgghte.c.kggfa "
    EXTENT 5 addr=000007FF53F78F50
      Chunk      7ff53f78f60 sz=       72    freeable  "rwodef : rwoalc"
      Chunk      7ff53f78fa8 sz=     3944    freeable  "qke.c.kggfa    "
    EXTENT 6 addr=000007FF53F79F50
      Chunk      7ff53f79f60 sz=       72    freeable  "chedef : qcuatc"
      Chunk      7ff53f79fa8 sz=     3944    freeable  "kggec.c.kggfa  "
    EXTENT 7 addr=000007FF53F7AF50
      Chunk      7ff53f7af60 sz=      272    free      "               "
      Chunk      7ff53f7b070 sz=       48    freeable  "kghscCopyData:d"
      Chunk      7ff53f7b0a0 sz=      520    freeable  "xplins:xplrow  "
      Chunk      7ff53f7b2a8 sz=       56    freeable  "qolHintAlloc:pi"
      Chunk      7ff53f7b2e0 sz=      224    freeable  "qolHintAlloc:ne"
      Chunk      7ff53f7b3c0 sz=       40    freeable  "xplCopyRow:qkbl"
      Chunk      7ff53f7b3e8 sz=       48    freeable  "xplCopyRow:proj"
      Chunk      7ff53f7b418 sz=       40    freeable  "xplCopyRow:opti"
      Chunk      7ff53f7b440 sz=      224    freeable  "qolHintAlloc:ne"
      Chunk      7ff53f7b520 sz=      224    freeable  "qolHintAlloc:ne"
      Chunk      7ff53f7b600 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f7b658 sz=       88    freeable  "kgghte.c.kggfa "
      Chunk      7ff53f7b6b0 sz=      192    freeable  "qknAllocate:qkn"
      Chunk      7ff53f7b770 sz=       88    freeable  "qke.c.kggfa    "
      Chunk      7ff53f7b7c8 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f7b820 sz=      200    freeable  "qkeCreateAnalys"
      Chunk      7ff53f7b8e8 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f7b910 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f7b938 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f7b960 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f7b988 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f7b9b0 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f7b9d8 sz=       72    freeable  "kggdlHd:Init   "
      Chunk      7ff53f7ba20 sz=      232    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f7bb08 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f7bb60 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f7bbc0 sz=      328    freeable  "qknAllocate:qkn"
      Chunk      7ff53f7bd08 sz=      160    freeable  "qmuCreatePermSu"
      Chunk      7ff53f7bda8 sz=       72    freeable  "allocator state"
      Chunk      7ff53f7bdf0 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f7be50 sz=      192    freeable  "qknAllocate:qkn"
    EXTENT 8 addr=000007FF53F7BF50
      Chunk      7ff53f7bf60 sz=     4136    freeable  "qkxr.c.kgght   "
    EXTENT 9 addr=000007FF53F7CFC8
      Chunk      7ff53f7cfd8 sz=     4136    freeable  "qkxr.c.kgght   "
    EXTENT 10 addr=000007FF53F7E040
      Chunk      7ff53f7e050 sz=       72    freeable  "kkoFroCtx: qksf"
      Chunk      7ff53f7e098 sz=     3944    freeable  "kggec.c.kggfa  "
    EXTENT 11 addr=000007FF53F7F040
      Chunk      7ff53f7f050 sz=       72    freeable  "chedef : qcuatc"
      Chunk      7ff53f7f098 sz=     3944    freeable  "qkxr.c.kgght   "
    EXTENT 12 addr=000007FF53F80040
      Chunk      7ff53f80050 sz=       48    freeable  "rwodef : rwoalc"
      Chunk      7ff53f80080 sz=       72    freeable  "qksxaMoveList:x"
      Chunk      7ff53f800c8 sz=       40    freeable  "opldef: qcopCre"
      Chunk      7ff53f800f0 sz=      112    freeable  "opndef: qcopCre"
      Chunk      7ff53f80160 sz=       40    freeable  "rwodef : rwoalc"
      Chunk      7ff53f80188 sz=      104    freeable  "qkaFroCtx: qksf"
      Chunk      7ff53f801f0 sz=       72    freeable  "qkaQbcCtx: qksq"
      Chunk      7ff53f80238 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f80298 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f802f8 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f80358 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f803b8 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f80418 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f80478 sz=       72    freeable  "qksxaAddList:xa"
      Chunk      7ff53f804c0 sz=       72    freeable  "qksxaAddList:xa"
      Chunk      7ff53f80508 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f80568 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f805c8 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f80628 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f80650 sz=       40    freeable  "qbnm : qksqbAll"
      Chunk      7ff53f80678 sz=       48    freeable  "kkoqdsmpqi : kk"
      Chunk      7ff53f806a8 sz=      176    freeable  "kkotp : kkoiqb "
      Chunk      7ff53f80758 sz=       56    freeable  "kkoqbc: costCac"
      Chunk      7ff53f80790 sz=       88    freeable  "fptdef : apacfc"
      Chunk      7ff53f807e8 sz=      280    freeable  "kkoFroAnn: qksf"
      Chunk      7ff53f80900 sz=      464    freeable  "kkoqb: qksqbIni"
      Chunk      7ff53f80ad0 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f80b28 sz=       88    freeable  "qkxr.c.kgght   "
      Chunk      7ff53f80b80 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f80ba8 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f80bd0 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f80bf8 sz=       56    freeable  "kkoInitOptimCac"
      Chunk      7ff53f80c30 sz=      104    freeable  "kkqtFroCtx: qks"
      Chunk      7ff53f80c98 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f80cc0 sz=       80    freeable  "qbcreg : qksqbR"
      Chunk      7ff53f80d10 sz=      232    freeable  "kkqtQbcCtx: qks"
      Chunk      7ff53f80df8 sz=      520    recreate  "kggec.c.kggfa  "  latch=0000000000000000
         ds      7ff53f90108 sz=      520 ct=        1
    EXTENT 13 addr=000007FF53F86040
      Chunk      7ff53f86050 sz=       72    freeable  "kggslHd:Init   "
      Chunk      7ff53f86098 sz=     3944    freeable  "kgghte.c.kggfa "
    EXTENT 14 addr=000007FF53F87040
      Chunk      7ff53f87050 sz=       72    freeable  "chedef : qcuatc"
      Chunk      7ff53f87098 sz=     3944    freeable  "kgghte.c.kggfa "
    EXTENT 15 addr=000007FF53F88040
      Chunk      7ff53f88050 sz=       72    freeable  "kggslAllocItem "
      Chunk      7ff53f88098 sz=     3944    freeable  "kggec.c.kggfa  "
    EXTENT 16 addr=000007FF53F89040
      Chunk      7ff53f89050 sz=       72    freeable  "kggsmCommonInit"
      Chunk      7ff53f89098 sz=     3944    freeable  "kgghte.c.kggfa "
    EXTENT 17 addr=000007FF53F8A040
      Chunk      7ff53f8a050 sz=       72    freeable  "qbnm : qksqbAll"
      Chunk      7ff53f8a098 sz=     3944    freeable  "kgghte.c.kggfa "
    EXTENT 18 addr=000007FF53F8B040
      Chunk      7ff53f8b050 sz=       72    freeable  "idndef : qcuAll"
      Chunk      7ff53f8b098 sz=     3944    freeable  "kggec.c.kggfa  "
    EXTENT 19 addr=000007FF53F8C040
      Chunk      7ff53f8c050 sz=       72    freeable  "kggsmCommonInit"
      Chunk      7ff53f8c098 sz=     3944    freeable  "kgghte.c.kggfa "
    EXTENT 20 addr=000007FF53F8D040
      Chunk      7ff53f8d050 sz=       72    freeable  "chedef : qcuatc"
      Chunk      7ff53f8d098 sz=     3944    freeable  "kggec.c.kggfa  "
    EXTENT 21 addr=000007FF53F8E040
      Chunk      7ff53f8e050 sz=       72    freeable  "halias : qkshtT"
      Chunk      7ff53f8e098 sz=     3944    freeable  "kggec.c.kggfa  "
    EXTENT 22 addr=000007FF53F8F040
      Chunk      7ff53f8f050 sz=       72    freeable  "idndef : qcuAll"
      Chunk      7ff53f8f098 sz=     3944    freeable  "kggms.c.kggfa  "
    EXTENT 23 addr=000007FF53F90058
      Chunk      7ff53f90068 sz=       80    perm      "perm           "  alo=80
      Chunk      7ff53f900b8 sz=       56    freeable  "qkshtRegistry :"
      Chunk      7ff53f900f0 sz=      184    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f901a8 sz=      144    freeable  "qksbgCreateCurs"
      Chunk      7ff53f90238 sz=      160    freeable  "kksgaAlloc elem"
      Chunk      7ff53f902d8 sz=      152    freeable  "kkets : kkdlgtd"
      Chunk      7ff53f90370 sz=       80    freeable  "kctph : kkdlgtd"
      Chunk      7ff53f903c0 sz=      248    freeable  "qsmksol : qsmg_"
      Chunk      7ff53f904b8 sz=      720    freeable  "qbcdef:prsdrv  "
      Chunk      7ff53f90788 sz=      304    freeable  "qkemgx: qkemgCr"
      Chunk      7ff53f908b8 sz=       88    freeable  "kgghte.c.kggfa "
      Chunk      7ff53f90910 sz=       88    freeable  "kgghte.c.kggfa "
      Chunk      7ff53f90968 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f909c0 sz=       80    freeable  "kggsmInit:sm   "
      Chunk      7ff53f90a10 sz=       88    freeable  "kgghte.c.kggfa "
      Chunk      7ff53f90a68 sz=       88    freeable  "kgghte.c.kggfa "
      Chunk      7ff53f90ac0 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f90b18 sz=       80    freeable  "kggsmInit:sm   "
      Chunk      7ff53f90b68 sz=       88    freeable  "kgghte.c.kggfa "
      Chunk      7ff53f90bc0 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f90c18 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f90c70 sz=       88    freeable  "kggms.c.kggfa  "
      Chunk      7ff53f90cc8 sz=      168    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f90d70 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f90dc8 sz=       72    freeable  "qksHd : qksAllo"
      Chunk      7ff53f90e10 sz=      400    freeable  "ctxcdef: opiprs"
      Chunk      7ff53f90fa0 sz=       48    freeable  "qcsqlpath: qcsA"
      Chunk      7ff53f90fd0 sz=       48    freeable  "qcsqlpath: qcsA"
    Total heap size    =    96600
    FREE LISTS:
     Bucket 0 size=80
      Chunk      7ff53f90088 sz=        0    kghdsx
     Bucket 1 size=168
      Chunk      7ff53f73f60 sz=     2936    free      "               "
      Chunk      7ff53f7af60 sz=      272    free      "               "
    Total free space   =     3208
    UNPINNED RECREATABLE CHUNKS (lru first):
    PERMANENT CHUNKS:
      Chunk      7ff53f90068 sz=       80    perm      "perm           "  alo=80
    Permanent space    =       80
    

    All this is too cryptic and I have no idea what exactly is located in this memory pieces. Also it’s very surprising to see that “typecheck” memory seems to be not accounted to sharable memory through V$-views at all, although it is a big part of it. More than 100K for a simple query is just too big in my opinion.

    Thank you for reading till the end :) I’ll be happy to hear your comments on the topic.

    Filed under: Oracle Tagged: memory, ORA-04031, tpt