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
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.
Timur Akhmadeev's blog 
8 Comments
Great blog. I remember similar situation with rule based optimizer where query had big inlist and plan was huge because of redundant index unix scans .
So, simple explain blew out our production till slush shared pool because of fragmentation . That was in 9.2 DB
Regards
GregG
Thanks Timur, very interesting post
As far as I understand the simple view usage can help to resolve problem, isn’t it?
Igor
Hi Igor
First of all I don’t see here a big issue. Yes, EXPLAIN PLAN cursors require some additional memory that looks to be huge enough compared to the statement, but that’s just fine. In a normal system there is no need to run it frequently.
Second, I’m not quite sure what do you mean by “simple view”. In case you think that wrapping SQL in a view such as SELECT * and then explaining it could make these memory chunks disappear, then it’s not true:
SQL> SQL> explain plan for select * from (select /*+ &uq_id */ count(*) from dual); Explained. SQL> SQL> select sharable_mem 2 from v$sql 3 where sql_id = (select s.prev_sql_id from v$session s where sid = userenv('sid')) 4 and child_number = (select s.prev_child_number from v$session s where sid = userenv('sid')); SHARABLE_MEM -------------------- 36961 SQL> SQL> SQL> explain plan for select null from (select /*+ &uq_id */ count(*) from dual); Explained. SQL> SQL> select sharable_mem 2 from v$sql 3 where sql_id = (select s.prev_sql_id from v$session s where sid = userenv('sid')) 4 and child_number = (select s.prev_child_number from v$session s where sid = userenv('sid')); SHARABLE_MEM -------------------- 36964Thanks Timur, but I mean your initial huge qry_text size query which got “ORA-04031: unable to allocate 235704 bytes of shared memory (“shared pool”,”select * from (…”,”Typecheck”,”qry_text : qcpisqt”)”
Will shared pool memory usage decrease if this query is wrapped into view?
For example:
SQL> select * from view_maked_from_huge_query_text;
Is this way suitable for ORA-04031 problem resolution?
Igor
I think there are cases when wrapping SQL in a view could help in avoiding ORA-4031, like when you use 2 different queries based on the view – I guess. I didn’t test it, so can’t say for sure.
Timur, the typecheck memory numbers are not in SHARABLE_MEM, but in TYPECHECK_MEM column in V$SQL… see if this adds up …
Hi Tanel,
thanks for stopping by!
Yes, indeed TYPECHECK_MEM shows missing 90K+ of memory. It’s nice to know that it is accounted somehow, although not very conveniently I must say.
Another thing that I’ve noticed when I run modified script – if I re-check TYPECHECK_MEM after some inactivity time, it goes to zero (this looks promising – probably the TYPECHECK part is abandoned quickly), but also SHARABLE_MEM increases a bit from 36K to 56K:
SHARABLE_MEM TYPECHECK_MEM -------------------- -------------------- 15624 0 Explained. SHARABLE_MEM TYPECHECK_MEM -------------------- -------------------- 36905 96600 SQL> / SHARABLE_MEM TYPECHECK_MEM -------------------- -------------------- 56418 0That is wrong, I checked memory of the last executed statement which is not what’s needed. Here’s the right version & output:
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, typecheck_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; col prev_sql_id new_value sql_id col prev_child_number new_value child_number select s.prev_sql_id, prev_child_number from v$session s where sid = userenv('sid'); select sharable_mem, typecheck_mem from v$sql where sql_id = '&sql_id' and child_number = &child_number;SQL> @tc\xplan_shared_mem UQ_ID -------------- 20130117072128 COUNT(*) -------------------- 1 SHARABLE_MEM TYPECHECK_MEM -------------------- -------------------- 15624 0 Explained. PREV_SQL_ID PREV_CHILD_NUMBER ------------- -------------------- 65mhg515qht3d 0 SHARABLE_MEM TYPECHECK_MEM -------------------- -------------------- 36905 96600 SQL> / SHARABLE_MEM TYPECHECK_MEM -------------------- -------------------- 36905 96600 SQL> / SHARABLE_MEM TYPECHECK_MEM -------------------- -------------------- 36905 966002 Trackbacks
[…] cursor plus some infrastructure overhead. I don’t know too much details but compared to a normal child cursor size of 15K, 100-ish bytes doesn’t look much to me. And the profit would be huge. To support […]
[…] given SQL again, in a special, more instrumented way, it has more information at hand apparently (and it uses more memory too). Or it could just be that whoever wrote V$SQL_PLAN, didn’t write the piece of code for […]