sql test/test S12DB SQL> create table t1 as select rownum as id, t.* from dba_objects t; SQL> create index t1_i on t1(id); SQL> select count(*) from t1 where owner = 'TEST'; SQL> alter session set nls_language = 'GERMAN'; SQL> select count(*) from t1 where owner = 'TEST'; SQL> select sql_text, sql_id, child_number, address, lower(to_char(hash_value,'XXXXXXXXXXXXXXXX')) as LIBRARY_CACE_HASH_OBJ from v$sql where sql_id = '9rvubrnhw6pg5'; SQL_TEXT SQL_ID CHILD_NUMBER ADDRESS LIBRARY_CACE_HASH -------------------------------------------------- ------------- ------------ ---------------- ----------------- select count(*) from t1 where owner = 'TEST' 9rvubrnhw6pg5 0 000000019F21AB90 21c355e5 select count(*) from t1 where owner = 'TEST' 9rvubrnhw6pg5 1 000000019F21AB90 21c355e5 SQL> oradebug dump heapdump 2 SQL> oradebug dump heapdump_addr 1 0x19db8ba18 SQL> oradebug dump heapdump_addr 1 0x19da6f0b0 SQL> oradebug dump library_cache_object 16 0x000000019F21AB90 Processing Oradebug command 'dump heapdump 2' ****************************************************** HEAP DUMP heap name="sga heap" desc=60001448 extent sz=0x4d3a8 alt=272 het=32767 rec=9 flg=-126 opc=0 parent=0 owner=0 nex=0 xsz=0x0 heap=0 ****************************************************** HEAP DUMP heap name="sga heap(1,0)" desc=60076a38 extent sz=0xfe0 alt=272 het=32767 rec=9 flg=-126 opc=0 parent=0 owner=0 nex=0 xsz=0x1000000 heap=0 EXTENT 0 addr=19d000000 Chunk 19d9ed2d0 sz= 4096 recreatUR "KGLH0^21c355e5 " latch=0 ds 19da29ef0 sz= 4096 ct= 1 Chunk 19da6e8e0 sz= 4096 recreatPC "KGLH0^21c355e5 " latch=0 ds 19d837e78 sz= 8192 ct= 2 19db8b880 sz= 4096 Chunk 19da8adf8 sz= 4096 recreatUC "KGLH0^21c355e5 " latch=0 ds 19d811140 sz= 4096 ct= 1 Chunk 19db8b880 sz= 4096 freeable "KGLH0^21c355e5 " ds=19d837e78 ****************************************************** HEAP DUMP heap name="sga heap(1,3)" desc=6007b340 extent sz=0xfe0 alt=272 het=32767 rec=9 flg=-126 opc=0 parent=0 owner=0 nex=0 xsz=0x1000000 heap=0 EXTENT 0 addr=19c000000 Chunk 19c98ec60 sz= 4096 freeableU "SQLA^21c355e5 " ds=19db8ba18 Chunk 19c98fc60 sz= 4096 recreatUC "SQLA^21c355e5 " latch=0 ds 19db8ba18 sz= 8192 ct= 2 19c98ec60 sz= 4096 Chunk 19c990c60 sz= 4096 freeableU "SQLA^21c355e5 " ds=19da6f0b0 Chunk 19c9aac60 sz= 4096 recreatUR "SQLA^21c355e5 " latch=0 ds 19da6f0b0 sz= 8192 ct= 2 19c990c60 sz= 4096 ****************************************************** Oradebug command 'dump heapdump 2' console output: Processing Oradebug command 'dump heapdump_addr 1 0x19db8ba18' ****************************************************** HEAP DUMP heap name="SQLA^21c355e5" desc=19db8ba18 extent sz=0xfe8 alt=32767 het=456 rec=0 flg=2 opc=2 parent=60001448 owner=19db8b8d0 nex=0 xsz=0xfe8 heap=0 EXTENT 0 addr=19c98ec78 Chunk 19c98ec88 sz= 1200 free " " Chunk 19c98f138 sz= 40 freeable "kggsmInitCompac" Chunk 19c98f160 sz= 40 freeable "kggsmInitCompac" Chunk 19c98f188 sz= 40 freeable "kggsmInitCompac" Chunk 19c98f1b0 sz= 40 freeable "kggsmInitCompac" Chunk 19c98f1d8 sz= 40 freeable "kggsmInitCompac" Chunk 19c98f200 sz= 144 freeable "kggsmCommonInit" Chunk 19c98f290 sz= 80 freeable "kggsmInit:sm " Chunk 19c98f2e0 sz= 40 freeable "idndef : qcuAll" Chunk 19c98f308 sz= 48 freeable "qeSel: qkxrXfor" Chunk 19c98f338 sz= 40 freeable "kggac: kggacCre" Chunk 19c98f360 sz= 40 freeable "kksoff : opitca" Chunk 19c98f388 sz= 104 freeable "opiprwd : opitc" Chunk 19c98f3f0 sz= 272 freeable "pqctx:kkfdParal" Chunk 19c98f500 sz= 88 freeable "ctxPlanSig:qksc" Chunk 19c98f558 sz= 104 freeable "KGHSC_ALLOC_BUF" Chunk 19c98f5c0 sz= 48 freeable "qeeRwo: qeeCrea" Chunk 19c98f5f0 sz= 64 freeable "kksol : kksnsg " Chunk 19c98f630 sz= 64 freeable "kksol : kksnsg " Chunk 19c98f670 sz= 136 freeable "qeeOpt: qeesCre" Chunk 19c98f6f8 sz= 48 freeable "qeeRwo: qeeCrea" Chunk 19c98f728 sz= 224 freeable "qergss:qergsAll" Chunk 19c98f808 sz= 64 freeable "kksol : kksnsg " Chunk 19c98f848 sz= 64 freeable "kksol : kksnsg " Chunk 19c98f888 sz= 64 freeable "kksol : kksnsg " Chunk 19c98f8c8 sz= 40 freeable "ub1[]: qkexrXfo" Chunk 19c98f8f0 sz= 96 freeable "opn: qkexrInitO" Chunk 19c98f950 sz= 40 freeable "idndef : qcuAll" Chunk 19c98f978 sz= 56 freeable "idndef*[]: qkex" Chunk 19c98f9b0 sz= 120 freeable "opn: qkexrInitO" Chunk 19c98fa28 sz= 144 freeable "qeeOpt: qeesCre" Chunk 19c98fab8 sz= 424 freeable "qertbs:qertbIAl" EXTENT 1 addr=19c98fc90 Chunk 19c98fca0 sz= 112 perm "perm " alo=112 Chunk 19c98fd10 sz= 88 freeable "kksol : kksnsg " Chunk 19c98fd68 sz= 64 freeable "kksol : kksnsg " Chunk 19c98fda8 sz= 48 freeable "qeeRwo: qeeCrea" Chunk 19c98fdd8 sz= 72 freeable "qksmm: qksmmCs " Chunk 19c98fe20 sz= 40 freeable "xplGenXpl:planL" Chunk 19c98fe48 sz= 40 freeable "qkaReorderAggs." Chunk 19c98fe70 sz= 40 freeable "qesmaInitTblCtx" Chunk 19c98fe98 sz= 88 freeable "qertbAllocatePa" Chunk 19c98fef0 sz= 96 freeable "qkaapd : qkaqkn" Chunk 19c98ff50 sz= 272 freeable "kccdef: qkxrMem" Chunk 19c990060 sz= 64 freeable "kksol : kksnsg " Chunk 19c9900a0 sz= 64 freeable "kafco : qkacol " Chunk 19c9900e0 sz= 416 freeable "opixpop:kctdef " Chunk 19c990280 sz= 40 freeable "opixfalo:froaty" Chunk 19c9902a8 sz= 40 freeable "opixfalo:ctxkct" Chunk 19c9902d0 sz= 40 freeable "chedef : qcuatc" Chunk 19c9902f8 sz= 40 freeable "kobjn : kkdcchs" Chunk 19c990320 sz= 64 freeable "cxach : opiSem " Chunk 19c990360 sz= 88 freeable "ctxqrol : kkqsr" Chunk 19c9903b8 sz= 48 freeable "qksrcMarkQB:qks" Chunk 19c9903e8 sz= 48 freeable "ktamd : ktagmd " Chunk 19c990418 sz= 40 freeable "idndef : qcuAll" Chunk 19c990440 sz= 416 freeable "kctdef : qcdlgo" Chunk 19c9905e0 sz= 96 freeable "unmdef in opipr" Chunk 19c990640 sz= 56 freeable "qctctx: kkmqccr" Chunk 19c990678 sz= 88 freeable "qcsctx: kkmqccr" Chunk 19c9906d0 sz= 40 freeable "qcptgc: kkmqccr" Chunk 19c9906f8 sz= 80 freeable "qcpctx: kkmqccr" Chunk 19c990748 sz= 56 freeable "qcmemctx : kkmq" Chunk 19c990780 sz= 152 freeable "qcctx : kkmqccr" Chunk 19c990818 sz= 64 freeable "kksol : kkscuf " Chunk 19c990858 sz= 64 freeable "kksol : kkscuf " Chunk 19c990898 sz= 968 freeable "ctxdef:kksLoadC" Total heap size = 8088 FREE LISTS: Bucket 0 size=152 Chunk 19c98fcc8 sz= 0 kghdsx Bucket 1 size=280 Bucket 2 size=536 Bucket 3 size=1048 Chunk 19c98ec88 sz= 1200 free " " Bucket 4 size=2072 Bucket 5 size=4120 Bucket 6 size=4144 Bucket 7 size=4168 Bucket 8 size=4192 Bucket 9 size=4216 Total free space = 1200 UNPINNED RECREATABLE CHUNKS (lru first): PERMANENT CHUNKS: Chunk 19c98fca0 sz= 112 perm "perm " alo=112 19c98fca0 sz= 112 cpmlst "kghdsx " Permanent space = 112 ****************************************************** Oradebug command 'dump heapdump_addr 1 0x19db8ba18' console output: Processing Oradebug command 'dump heapdump_addr 1 0x19da6f0b0' ****************************************************** HEAP DUMP heap name="SQLA^21c355e5" desc=19da6f0b0 extent sz=0xfe8 alt=32767 het=456 rec=0 flg=2 opc=2 parent=60001448 owner=19da6ef68 nex=0 xsz=0xfe8 heap=0 EXTENT 0 addr=19c990c78 Chunk 19c990c88 sz= 1200 free " " Chunk 19c991138 sz= 40 freeable "kggsmInitCompac" Chunk 19c991160 sz= 40 freeable "kggsmInitCompac" Chunk 19c991188 sz= 40 freeable "kggsmInitCompac" Chunk 19c9911b0 sz= 40 freeable "kggsmInitCompac" Chunk 19c9911d8 sz= 40 freeable "kggsmInitCompac" Chunk 19c991200 sz= 144 freeable "kggsmCommonInit" Chunk 19c991290 sz= 80 freeable "kggsmInit:sm " Chunk 19c9912e0 sz= 40 freeable "idndef : qcuAll" Chunk 19c991308 sz= 48 freeable "qeSel: qkxrXfor" Chunk 19c991338 sz= 40 freeable "kggac: kggacCre" Chunk 19c991360 sz= 40 freeable "kksoff : opitca" Chunk 19c991388 sz= 104 freeable "opiprwd : opitc" Chunk 19c9913f0 sz= 272 freeable "pqctx:kkfdParal" Chunk 19c991500 sz= 88 freeable "ctxPlanSig:qksc" Chunk 19c991558 sz= 104 freeable "KGHSC_ALLOC_BUF" Chunk 19c9915c0 sz= 48 freeable "qeeRwo: qeeCrea" Chunk 19c9915f0 sz= 64 freeable "kksol : kksnsg " Chunk 19c991630 sz= 64 freeable "kksol : kksnsg " Chunk 19c991670 sz= 136 freeable "qeeOpt: qeesCre" Chunk 19c9916f8 sz= 48 freeable "qeeRwo: qeeCrea" Chunk 19c991728 sz= 224 freeable "qergss:qergsAll" Chunk 19c991808 sz= 64 freeable "kksol : kksnsg " Chunk 19c991848 sz= 64 freeable "kksol : kksnsg " Chunk 19c991888 sz= 64 freeable "kksol : kksnsg " Chunk 19c9918c8 sz= 40 freeable "ub1[]: qkexrXfo" Chunk 19c9918f0 sz= 96 freeable "opn: qkexrInitO" Chunk 19c991950 sz= 40 freeable "idndef : qcuAll" Chunk 19c991978 sz= 56 freeable "idndef*[]: qkex" Chunk 19c9919b0 sz= 120 freeable "opn: qkexrInitO" Chunk 19c991a28 sz= 144 freeable "qeeOpt: qeesCre" Chunk 19c991ab8 sz= 424 freeable "qertbs:qertbIAl" EXTENT 1 addr=19c9aac90 Chunk 19c9aaca0 sz= 112 perm "perm " alo=112 Chunk 19c9aad10 sz= 88 freeable "kksol : kksnsg " Chunk 19c9aad68 sz= 64 freeable "kksol : kksnsg " Chunk 19c9aada8 sz= 48 freeable "qeeRwo: qeeCrea" Chunk 19c9aadd8 sz= 72 freeable "qksmm: qksmmCs " Chunk 19c9aae20 sz= 40 freeable "xplGenXpl:planL" Chunk 19c9aae48 sz= 40 freeable "qkaReorderAggs." Chunk 19c9aae70 sz= 40 freeable "qesmaInitTblCtx" Chunk 19c9aae98 sz= 88 freeable "qertbAllocatePa" Chunk 19c9aaef0 sz= 96 freeable "qkaapd : qkaqkn" Chunk 19c9aaf50 sz= 272 freeable "kccdef: qkxrMem" Chunk 19c9ab060 sz= 64 freeable "kksol : kksnsg " Chunk 19c9ab0a0 sz= 64 freeable "kafco : qkacol " Chunk 19c9ab0e0 sz= 416 freeable "opixpop:kctdef " Chunk 19c9ab280 sz= 40 freeable "opixfalo:froaty" Chunk 19c9ab2a8 sz= 40 freeable "opixfalo:ctxkct" Chunk 19c9ab2d0 sz= 40 freeable "chedef : qcuatc" Chunk 19c9ab2f8 sz= 40 freeable "kobjn : kkdcchs" Chunk 19c9ab320 sz= 64 freeable "cxach : opiSem " Chunk 19c9ab360 sz= 88 freeable "ctxqrol : kkqsr" Chunk 19c9ab3b8 sz= 48 freeable "qksrcMarkQB:qks" Chunk 19c9ab3e8 sz= 48 freeable "ktamd : ktagmd " Chunk 19c9ab418 sz= 40 freeable "idndef : qcuAll" Chunk 19c9ab440 sz= 416 freeable "kctdef : qcdlgo" Chunk 19c9ab5e0 sz= 96 freeable "unmdef in opipr" Chunk 19c9ab640 sz= 56 freeable "qctctx: kkmqccr" Chunk 19c9ab678 sz= 88 freeable "qcsctx: kkmqccr" Chunk 19c9ab6d0 sz= 40 freeable "qcptgc: kkmqccr" Chunk 19c9ab6f8 sz= 80 freeable "qcpctx: kkmqccr" Chunk 19c9ab748 sz= 56 freeable "qcmemctx : kkmq" Chunk 19c9ab780 sz= 152 freeable "qcctx : kkmqccr" Chunk 19c9ab818 sz= 64 freeable "kksol : kkscuf " Chunk 19c9ab858 sz= 64 freeable "kksol : kkscuf " Chunk 19c9ab898 sz= 968 freeable "ctxdef:kksLoadC" Total heap size = 8088 FREE LISTS: Bucket 0 size=152 Chunk 19c9aacc8 sz= 0 kghdsx Bucket 1 size=280 Bucket 2 size=536 Bucket 3 size=1048 Chunk 19c990c88 sz= 1200 free " " Bucket 4 size=2072 Bucket 5 size=4120 Bucket 6 size=4144 Bucket 7 size=4168 Bucket 8 size=4192 Bucket 9 size=4216 Total free space = 1200 UNPINNED RECREATABLE CHUNKS (lru first): PERMANENT CHUNKS: Chunk 19c9aaca0 sz= 112 perm "perm " alo=112 19c9aaca0 sz= 112 cpmlst "kghdsx " Permanent space = 112 ****************************************************** Oradebug command 'dump heapdump_addr 1 0x19da6f0b0' console output: Processing Oradebug command 'dump library_cache_object 16 0x000000019F21AB90' ****************************************************** LibraryHandle: Address=19f21ab90 Hash=21c355e5 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD ObjectName: Name=select count(*) from t1 where owner = 'TEST' FullHashValue=af5a86c48febcd9b9bef4bbd21c355e5 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=0 ContainerUid=0 Identifier=566449637 OwnerIdn=97 Statistics: InvalidationCount=0 ExecutionCount=2 LoadCount=3 ActiveLocks=0 TotalLockCount=2 TotalPinCount=1 Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=2 Version=0 BucketInUse=1 HandleInUse=1 HandleReferenceCount=0 Concurrency: DependencyMutex=19f21ac40(0, 2, 0, 0) Mutex=19f21acd8(175, 43, 0, 6) Flags=RON/PIN/TIM/PN0/DBN/[10012841] Flags2=[0000] WaitersLists: Lock=19f21ac20[19f21ac20,19f21ac20] Pin=19f21ac00[19f21ac00,19f21ac00] LoadLock=19f21ac78[19f21ac78,19f21ac78] Timestamp: Current=10-12-2016 11:54:27 HandleReference: Address=19f21ad78 Handle=0 Flags=[00] ReferenceList: Reference: Address=19da8a228 Handle=19dfe1448 Flags=ROD[21] Reference: Address=19d9ec700 Handle=19da29ca8 Flags=ROD[21] LibraryObject: Address=19da6e9b8 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000] DataBlocks: Block: #='0' name=KGLH0^21c355e5 pins=0 Change=NONE Heap=19d837e78 Pointer=19da6ea80 Extent=19da6e910 Flags=I/-/P/A/-/-/- FreedLocation=0 Alloc=4.460938 Size=7.953125 LoadTime=2187432 ChildTable: size='16' Child: id='0' Table=19da6f838 Reference=19da6f2d0 Handle=19f21aa10 Child: id='1' Table=19da6f838 Reference=19db8bb78 Handle=19f19d9a8 Children: Child: childNum='0' LibraryHandle: Address=19f21aa10 Hash=0 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD Name: Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=0 Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=1 ActiveLocks=0 TotalLockCount=2 TotalPinCount=3 Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0 Concurrency: DependencyMutex=19f21aac0(0, 0, 0, 0) Mutex=19f21acd8(175, 43, 0, 6) Flags=RON/PIN/PN0/EXP/CHD/[10012111] Flags2=[0000] WaitersLists: Lock=19f21aaa0[19f21aaa0,19f21aaa0] Pin=19f21aa80[19f21aa80,19f21aa80] LoadLock=19f21aaf8[19f21aaf8,19f21aaf8] ReferenceList: Reference: Address=19da6f2d0 Handle=19f21ab90 Flags=CHL[02] LibraryObject: Address=19d9ed3a8 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000] Dependencies: count='2' size='16' table='19d9ee1b8' Dependency: num='0' Reference=19d9eda58 Position=0 Flags=DEP[0001] Handle=19db437b0 Type=NONE(255) Parent=TEST Dependency: num='1' Reference=19d9edb00 Position=21 Flags=DEP[0001] Handle=19db68b00 Type=TABLE(02) Parent=TEST.T1 ReadOnlyDependencies: count='1' size='16' ReadDependency: num='0' Table=19d9ee250 Reference=19d9ed958 Handle=19da29ca8 Flags=DEP/ROD/KPP[61] Accesses: count='1' size='16' Dependency: num='1' Type=0011 Translations: count='1' size='16' Translation: num='0' Original=19db68b00 Final=19db68b00 DataBlocks: Block: #='0' name=KGLH0^21c355e5 pins=0 Change=NONE Heap=19da29ef0 Pointer=19d9ed470 Extent=19d9ed300 Flags=I/-/-/A/-/-/- FreedLocation=0 Alloc=3.328125 Size=3.976562 LoadTime=2187433 Block: #='6' name=SQLA^21c355e5 pins=0 Change=NONE Heap=19da6f0b0 Pointer=19c9ab8b0 Extent=19c9aac90 Flags=I/-/-/A/-/-/E FreedLocation=0 Alloc=6.617188 Size=7.898438 LoadTime=0 NamespaceDump: Child Cursor: Heap0=19d9ed470 Heap6=19c9ab8b0 Heap0 Load Time=10-12-2016 11:54:27 Heap6 Load Time=10-12-2016 11:54:27 Child: childNum='1' LibraryHandle: Address=19f19d9a8 Hash=0 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD Name: Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=0 Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=1 ActiveLocks=0 TotalLockCount=1 TotalPinCount=2 Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0 Concurrency: DependencyMutex=19f19da58(0, 0, 0, 0) Mutex=19f21acd8(175, 43, 0, 6) Flags=RON/PIN/PN0/EXP/CHD/[10012111] Flags2=[0000] WaitersLists: Lock=19f19da38[19f19da38,19f19da38] Pin=19f19da18[19f19da18,19f19da18] LoadLock=19f19da90[19f19da90,19f19da90] ReferenceList: Reference: Address=19db8bb78 Handle=19f21ab90 Flags=CHL[02] LibraryObject: Address=19da8aed0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000] Dependencies: count='2' size='16' table='19da8bce0' Dependency: num='0' Reference=19da8b580 Position=0 Flags=DEP[0001] Handle=19db437b0 Type=NONE(255) Parent=TEST Dependency: num='1' Reference=19da8b628 Position=21 Flags=DEP[0001] Handle=19db68b00 Type=TABLE(02) Parent=TEST.T1 ReadOnlyDependencies: count='1' size='16' ReadDependency: num='0' Table=19da8bd78 Reference=19da8b480 Handle=19dfe1448 Flags=DEP/ROD/KPP[61] Accesses: count='1' size='16' Dependency: num='1' Type=0011 Translations: count='1' size='16' Translation: num='0' Original=19db68b00 Final=19db68b00 DataBlocks: Block: #='0' name=KGLH0^21c355e5 pins=0 Change=NONE Heap=19d811140 Pointer=19da8af98 Extent=19da8ae28 Flags=I/-/-/A/-/-/- FreedLocation=0 Alloc=3.328125 Size=3.976562 LoadTime=2194931 Block: #='6' name=SQLA^21c355e5 pins=0 Change=NONE Heap=19db8ba18 Pointer=19c9908b0 Extent=19c98fc90 Flags=I/-/-/A/-/-/E FreedLocation=0 Alloc=6.617188 Size=7.898438 LoadTime=0 NamespaceDump: Child Cursor: Heap0=19da8af98 Heap6=19c9908b0 Heap0 Load Time=10-12-2016 11:54:35 Heap6 Load Time=10-12-2016 11:54:35 NamespaceDump: Parent Cursor: sql_id=9rvubrnhw6pg5 parent=19da6ea80 maxchild=2 plk=n ppn=n CursorDiagnosticsNodes: ChildNode: ChildNumber=0 ID=45 reason=NLS Settings(2) size=2x568 NLS_LANGUAGE='AMERICAN'->'GERMAN' NLS_DATE_LANGUAGE='AMERICAN'->'GERMAN' NLS_SORT='BINARY'->'GERMAN' ****************************************************** Oradebug command 'dump library_cache_object 16 0x000000019F21AB90' console output: