[20161228]sql語句父子游標的堆轉儲2.txt

lfree發表於2016-12-28

[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
/

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2131506/,如需轉載,請註明出處,否則將追究法律責任。

相關文章