[20161228]sql语句父子游标的堆转储2.txt
--以前仅仅看了父游标堆0的堆转储,链接:http://blog.itpub.net/267265/viewspace-2076605/
--尽然当时没有做子游标堆0,6的堆转储,今天测试看看.
1.环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--sql_id=4xamnunv51w9j
2.测试:
SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0
old 17: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 17: WHERE kglobt03 = '4xamnunv51w9j' or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007D1A64B0 000000007CA66EE8 select * from dept where deptno=10 000000007BC6FAB0 000000007D8A78D8 4528 12144 3067 19739 19739 911274289 4xamnunv51w9j 0
父游标句柄地址 000000007CA66EE8 000000007CA66EE8 select * from dept where deptno=10 000000007D5F5BA8 00 4720 0 0 4720 4720 911274289 4xamnunv51w9j 65535
--转储子游标堆0信息:
SYS@book> alter session set events 'immediate trace name heapdump_addr level 2,addr 0x000000007BC6FAB0';
Session altered.
--转储文件:
******************************************************
HEAP DUMP heap name="KGLH0^3650f131" desc=0x7bc6fab0
extent sz=0xfe8 alt=32767 het=56 rec=9 flg=2 opc=0
parent=0x60001190 owner=0x7bc6fa60 nex=(nil) xsz=0xfd0 heap=(nil)
fl2=0x26, nex=(nil), dsxvers=1, dsxflg=0x0
dsx first ext=0x7b4bc348
EXTENT 0 addr=0x7b4bc348
Chunk 07b4bc358 sz= 80 perm "perm " alo=80
Dump of memory from 0x000000007B4BC358 to 0x000000007B4BC3A8
07B4BC350 00000051 40B38F00 [Q......@]
07B4BC360 00000000 00000000 00000000 00000000 [................]
07B4BC370 00000050 00000000 00000001 C0B38F00 [P...............]
07B4BC380 00000000 00000000 7B4BCDD8 00000000 [..........K{....]
07B4BC390 7BC6FB28 00000000 00000001 00000000 [(..{............]
07B4BC3A0 7B4BC348 00000000 [H.K{....]
Chunk 07b4bc3a8 sz= 2592 perm "perm " alo=1920
...
----很长截取其中1部分.
$ grep "^ Chunk" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_8501.trc
Chunk 07b4bc358 sz= 80 perm "perm " alo=80
Chunk 07b4bc3a8 sz= 2592 perm "perm " alo=1920
Chunk 07b4bcdc8 sz= 600 free " "
Chunk 07b4bd020 sz= 152 freeable "kgltbtab "
Chunk 07b4bd0b8 sz= 152 freeable "kgltbtab "
Chunk 07b4bd150 sz= 152 freeable "kgltbtab "
Chunk 07b4bd1e8 sz= 152 freeable "kgltbtab "
Chunk 07b4bd280 sz= 152 freeable "kgltbtab "
Chunk 07b4bcdc8 sz= 600 free " "
Chunk 07b4bc378 sz= 0 kghdsx
Chunk 07b4bc3a8 sz= 2592 perm "perm " alo=1920
Chunk 07b4bc358 sz= 80 perm "perm " alo=80
--//注:最后参数0表示 ,child_number=0.
SYS@book> @ &r/tpt/curheaps 911274289 0
old 20: KGLNAHSH in (&1)
new 20: KGLNAHSH in (911274289)
old 21: and KGLOBT09 like ('&2')
new 21: and KGLOBT09 like ('0')
KGLNAHSH KGLHDPAR CHILD# KGLHDADR KGLOBHD0 SIZE0 SIZE1 SIZE2 SIZE3 KGLOBHD4 SIZE4 SIZE5 KGLOBHD6 SIZE6 SIZE7 STATUS
---------- ---------------- ---------- ---------------- ---------------- -------- -------- -------- -------- ---------------- -------- -------- ---------------- -------- -------- ----------
911274289 000000007CA66EE8 0 000000007D1A64B0 000000007BC6FAB0 4528 0 0 0 00 0 0 000000007D8A78D8 12144 0 1
old 10: KSMCHDS = hextoraw('&v_curheaps_kglobhd0')
new 10: KSMCHDS = hextoraw('000000007BC6FAB0')
HEAP CLASS ALLOC_COMMENT BYTES CHUNKS
----- -------- ---------------- ---------- ----------
HEAP0 perm permanent memor 2672 2
HEAP0 freeabl kgltbtab 760 5
HEAP0 free free memory 600 1
--//说明:2个perm chunk,实际上看前面的地址是重复的,也就是使用的空间占用1个. 2592+80=2672 ,结果也可以对上.
old 10: KSMCHDS = hextoraw('&v_curheaps_kglobhd4')
new 10: KSMCHDS = hextoraw('00')
no rows selected
old 10: KSMCHDS = hextoraw('&v_curheaps_kglobhd6')
new 10: KSMCHDS = hextoraw('000000007D8A78D8')
HEAP CLASS ALLOC_COMMENT BYTES CHUNKS
----- -------- ---------------- ---------- ----------
HEAP6 free free memory 3416 1
HEAP6 freeabl ctxdef:kksLoadC 936 1
HEAP6 freeabl kccdef: qkxrMem 896 4
HEAP6 freeabl opn: qkexrInitO 688 6
HEAP6 freeabl qerixs : rixalo 536 1
HEAP6 freeabl kctdef : qcdlgo 400 1
HEAP6 freeabl opixpop:kctdef 400 1
HEAP6 freeabl idndef : qcuAll 368 9
HEAP6 freeabl qertbs:qertbIAl 360 1
HEAP6 freeabl idndef*[]: qkex 280 5
HEAP6 freeabl pqctx:kkfdParal 232 1
HEAP6 freeabl kggsmInitCompac 200 5
HEAP6 freeabl qertbAllocatePa 160 2
HEAP6 freeabl kafco : qkacol 152 1
HEAP6 freeabl qcctx : kkmqccr 152 1
HEAP6 freeabl qeSel: qkxrXfor 144 3
HEAP6 freeabl kggsmCommonInit 144 1
HEAP6 freeabl audRegFro:audta 136 1
HEAP6 freeabl kksol : kkscuf 128 2
HEAP6 freeabl qeeOpt: qeesCre 128 1
HEAP6 freeabl kafco[]: qkaPru 120 1
HEAP6 freeabl KGHSC_ALLOC_BUF 112 1
HEAP6 freeabl opiprwd : opitc 104 1
HEAP6 freeabl qeeRwo: qeeCrea 104 2
HEAP6 freeabl qkaapd : qkaqkn 96 1
HEAP6 freeabl ctxPlanSig:qksc 88 1
HEAP6 freeabl kkpoxgii: Alloc 80 1
HEAP6 perm permanent memor 80 1
HEAP6 freeabl chedef : qcuatc 80 2
HEAP6 freeabl qcpctx: kkmqccr 80 1
HEAP6 freeabl qeKey[]: qkxrXf 80 2
HEAP6 freeabl ctxqrol : kkqsr 80 1
HEAP6 freeabl kggsmInit:sm 80 1
HEAP6 freeabl qesmaInitTblCtx 80 2
HEAP6 freeabl qcsctx: kkmqccr 72 1
HEAP6 freeabl qesmaInitIdxCtx 72 1
HEAP6 freeabl qksmm: qksmmCs 72 1
HEAP6 freeabl kksol : kksnsg 64 1
HEAP6 freeabl cxach : opiSem 64 1
HEAP6 freeabl unmdef in opipr 64 1
HEAP6 freeabl qcmemctx : kkmq 56 1
HEAP6 freeabl kggac: kggacCre 56 1
HEAP6 freeabl qksrcMarkQB:qks 48 1
HEAP6 freeabl kksoff : opitca 48 1
HEAP6 freeabl ktamd : ktagmd 48 1
HEAP6 freeabl qctctx: kkmqccr 48 1
HEAP6 freeabl xplGenXpl:planL 40 1
HEAP6 freeabl qcptgc: kkmqccr 40 1
HEAP6 freeabl qkaEnableWide:c 40 1
HEAP6 freeabl opixfalo:froaty 32 1
HEAP6 freeabl opixfalo:ctxkct 32 1
HEAP6 freeabl kafco : rixalo 32 1
HEAP6 freeabl ub1[]: qkexrXfo 32 1
HEAP6 freeabl kobjn : kkdcchs 32 1
HEAP6 freeabl kafco* : qkanin 32 1
55 rows selected.
--//子光标的堆6就复杂多了,可以发现HEAP6 free free memory 3416 1.
--//大部分都是freeabl类型.
3.再做一次转储子游标堆6信息:
SYS@book> alter session set events 'immediate trace name heapdump_addr level 2,addr 0x000000007D8A78D8';
Session altered.
******************************************************
HEAP DUMP heap name="SQLA^3650f131" desc=0x7d8a78d8
extent sz=0xfe8 alt=32767 het=368 rec=0 flg=2 opc=2
parent=0x60001190 owner=0x7d8a7798 nex=(nil) xsz=0xfe8 heap=(nil)
fl2=0x27, nex=(nil), dsxvers=1, dsxflg=0x0
dsx first ext=0x7c4bce10
EXTENT 0 addr=0x7cf9f238
Chunk 07cf9f248 sz= 3416 free " "
Dump of memory from 0x000000007CF9F248 to 0x000000007CF9FFA0
07CF9F240 00000D59 C0B38F00 [Y.......]
07CF9F250 00000000 00000000 7D8A79B0 00000000 [.........y.}....]
07CF9F260 7D8A79B0 00000000 00000000 00000000 [.y.}............]
07CF9F270 00000000 00000000 00000050 00017FFF [........P.......]
07CF9F280 00000001 C0B38F00 00000000 00000000 [................]
07CF9F290 7CF9FE90 00000000 7D7BC218 00000000 [...|......{}....]
07CF9F2A0 00000001 00000000 7CF9F250 00000000 [........P..|....]
07CF9F2B0 00000BD1 40B38F00 7CF9F260 00000000 [.......@`..|....]
07CF9F2C0 7CF9F260 00000000 00000930 00000000 [`..|....0.......]
07CF9F2D0 7E5DCBF0 00000000 7CF9FAE8 00000000 [..]~.......|....]
07CF9F2E0 00000000 00000000 7D7BC150 00000000 [........P.{}....]
07CF9F2F0 00000000 00000000 00000000 00000000 [................]
....
Chunk 07cf9ffa0 sz= 56 freeable "kggsmInitCompac"
Dump of memory from 0x000000007CF9FFA0 to 0x000000007CF9FFD8
07CF9FFA0 00000039 00B38F00 7CF9F248 00000000 [9.......H..|....]
07CF9FFB0 0A940780 00000000 5F4B5007 54504544 [.........PK_DEPT]
07CF9FFC0 50454406 054F4E54 244C4553 45440431 [.DEPTNO.SEL$1.DE]
07CF9FFD0 53055450 54544F43 [PT.SCOTT]
--//这里可以看到执行计划.
$ grep "^ Chunk" /tmp/aa
Chunk 07cf9f248 sz= 3416 free " "
Chunk 07cf9ffa0 sz= 56 freeable "kggsmInitCompac"
Chunk 07cf9ffd8 sz= 40 freeable "kggsmInitCompac"
Chunk 07cfa0000 sz= 32 freeable "kggsmInitCompac"
Chunk 07cfa0020 sz= 40 freeable "kggsmInitCompac"
Chunk 07cfa0048 sz= 32 freeable "kggsmInitCompac"
Chunk 07cfa0068 sz= 144 freeable "kggsmCommonInit"
Chunk 07cfa00f8 sz= 80 freeable "kggsmInit:sm "
Chunk 07cfa0148 sz= 48 freeable "qeSel: qkxrXfor"
Chunk 07cfa0178 sz= 48 freeable "qeSel: qkxrXfor"
Chunk 07cfa01a8 sz= 120 freeable "opn: qkexrInitO"
Chunk 07cd7f5b0 sz= 48 freeable "idndef : qcuAll"
Chunk 07cd7f5e0 sz= 56 freeable "idndef*[]: qkex"
Chunk 07cd7f618 sz= 48 freeable "qeSel: qkxrXfor"
Chunk 07cd7f648 sz= 56 freeable "kggac: kggacCre"
Chunk 07cd7f680 sz= 48 freeable "kksoff : opitca"
Chunk 07cd7f6b0 sz= 104 freeable "opiprwd : opitc"
Chunk 07cd7f718 sz= 232 freeable "pqctx:kkfdParal"
Chunk 07cd7f800 sz= 88 freeable "ctxPlanSig:qksc"
Chunk 07cd7f858 sz= 112 freeable "KGHSC_ALLOC_BUF"
Chunk 07cd7f8c8 sz= 40 freeable "idndef : qcuAll"
Chunk 07cd7f8f0 sz= 56 freeable "idndef*[]: qkex"
Chunk 07cd7f928 sz= 120 freeable "opn: qkexrInitO"
Chunk 07cd7f9a0 sz= 40 freeable "idndef : qcuAll"
Chunk 07cd7f9c8 sz= 56 freeable "idndef*[]: qkex"
Chunk 07cd7fa00 sz= 120 freeable "opn: qkexrInitO"
Chunk 07cd7fa78 sz= 56 freeable "qeeRwo: qeeCrea"
Chunk 07cd7fab0 sz= 360 freeable "qertbs:qertbIAl"
Chunk 07cd7fc18 sz= 40 freeable "qeKey[]: qkxrXf"
Chunk 07cd7fc40 sz= 32 freeable "ub1[]: qkexrXfo"
Chunk 07cd7fc60 sz= 88 freeable "opn: qkexrInitO"
Chunk 07cd7fcb8 sz= 128 freeable "qeeOpt: qeesCre"
Chunk 07cd7fd38 sz= 40 freeable "qeKey[]: qkxrXf"
Chunk 07cd7fd60 sz= 40 freeable "idndef : qcuAll"
Chunk 07cd7fd88 sz= 56 freeable "idndef*[]: qkex"
Chunk 07cd7fdc0 sz= 120 freeable "opn: qkexrInitO"
Chunk 07cd7fe38 sz= 40 freeable "idndef : qcuAll"
Chunk 07cd7fe60 sz= 56 freeable "idndef*[]: qkex"
Chunk 07cd7fe98 sz= 120 freeable "opn: qkexrInitO"
Chunk 07cd7ff10 sz= 48 freeable "qeeRwo: qeeCrea"
Chunk 07cd7ff40 sz= 32 freeable "kafco : rixalo "
Chunk 07cd7ff60 sz= 536 freeable "qerixs : rixalo"
Chunk 07cd80178 sz= 72 freeable "qksmm: qksmmCs "
Chunk 07cd801c0 sz= 40 freeable "xplGenXpl:planL"
Chunk 07cd801e8 sz= 40 freeable "qkaEnableWide:c"
Chunk 07cd80210 sz= 120 freeable "kafco[]: qkaPru"
Chunk 07cd80288 sz= 40 freeable "qesmaInitTblCtx"
Chunk 07cd802b0 sz= 80 freeable "qertbAllocatePa"
Chunk 07cd80300 sz= 40 freeable "qesmaInitTblCtx"
Chunk 07cd80328 sz= 80 freeable "qertbAllocatePa"
Chunk 07cd80378 sz= 80 freeable "kkpoxgii: Alloc"
Chunk 07cd803c8 sz= 224 freeable "kccdef: qkxrMem"
Chunk 07cd804a8 sz= 224 freeable "kccdef: qkxrMem"
Chunk 07c4bce20 sz= 80 perm "perm " alo=80
Chunk 07c4bce70 sz= 72 freeable "qesmaInitIdxCtx"
Chunk 07c4bceb8 sz= 32 freeable "kafco* : qkanin"
Chunk 07c4bced8 sz= 96 freeable "qkaapd : qkaqkn"
Chunk 07c4bcf38 sz= 224 freeable "kccdef: qkxrMem"
Chunk 07c4bd018 sz= 224 freeable "kccdef: qkxrMem"
Chunk 07c4bd0f8 sz= 64 freeable "kksol : kksnsg "
Chunk 07c4bd138 sz= 152 freeable "kafco : qkacol "
Chunk 07c4bd1d0 sz= 400 freeable "opixpop:kctdef "
Chunk 07c4bd360 sz= 32 freeable "opixfalo:froaty"
Chunk 07c4bd380 sz= 32 freeable "opixfalo:ctxkct"
Chunk 07c4bd3a0 sz= 40 freeable "idndef : qcuAll"
Chunk 07c4bd3c8 sz= 40 freeable "idndef : qcuAll"
Chunk 07c4bd3f0 sz= 40 freeable "idndef : qcuAll"
Chunk 07c4bd418 sz= 40 freeable "chedef : qcuatc"
Chunk 07c4bd440 sz= 32 freeable "kobjn : kkdcchs"
Chunk 07c4bd460 sz= 64 freeable "cxach : opiSem "
Chunk 07c4bd4a0 sz= 80 freeable "ctxqrol : kkqsr"
Chunk 07c4bd4f0 sz= 48 freeable "qksrcMarkQB:qks"
Chunk 07c4bd520 sz= 48 freeable "ktamd : ktagmd "
Chunk 07c4bd550 sz= 40 freeable "chedef : qcuatc"
Chunk 07c4bd578 sz= 136 freeable "audRegFro:audta"
Chunk 07c4bd600 sz= 40 freeable "idndef : qcuAll"
Chunk 07c4bd628 sz= 400 freeable "kctdef : qcdlgo"
Chunk 07c4bd7b8 sz= 64 freeable "unmdef in opipr"
Chunk 07c4bd7f8 sz= 48 freeable "qctctx: kkmqccr"
Chunk 07c4bd828 sz= 72 freeable "qcsctx: kkmqccr"
Chunk 07c4bd870 sz= 40 freeable "qcptgc: kkmqccr"
Chunk 07c4bd898 sz= 80 freeable "qcpctx: kkmqccr"
Chunk 07c4bd8e8 sz= 56 freeable "qcmemctx : kkmq"
Chunk 07c4bd920 sz= 152 freeable "qcctx : kkmqccr"
Chunk 07c4bd9b8 sz= 64 freeable "kksol : kkscuf "
Chunk 07c4bd9f8 sz= 64 freeable "kksol : kkscuf "
Chunk 07c4bda38 sz= 936 freeable "ctxdef:kksLoadC"
Chunk 07c4bce40 sz= 0 kghdsx
Chunk 07cf9f248 sz= 3416 free " "
Chunk 07c4bce20 sz= 80 perm "perm " alo=80
--//太复杂看不懂.查看细节的视图是x$ksmhp,仅仅贴出看heap6脚本.
select
'HEAP6' heap
, ksmchcls class
, ksmchcom alloc_comment
, sum(ksmchsiz) bytes
, count(*) chunks
from
x$ksmhp
where
KSMCHDS = hextoraw('&v_curheaps_kglobhd6')
group by
'HEAP6'
, ksmchcls
, ksmchcom
order by
sum(ksmchsiz) desc
/