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

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

--昨天晚上看一些文件,發現可以透過alter session set events 'immediate trace name heapdump_addr level 2,addr 0xXXXXXXXX';
--後面加堆地址,轉儲裡面的內容.自己測試看看:

--另外補充說明一下: 我看了jonathan lewis <oracle核心技術> 裡面提到ds 應該是 data segment.

1.環境:

SCOTT@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

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
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 000000007C56A510 000000007CAB4BA8 select * from dept where deptno=10       000000007CE32168 000000007BE1A000       4488      12144       3067     19699      19699  911274289 4xamnunv51w9j          0
父遊標控制程式碼地址 000000007CAB4BA8 000000007CAB4BA8 select * from dept where deptno=10       000000007CB93CE0 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535

2.測試:

--轉儲父遊標堆0資訊:
SYS@book> select a.* from x$ksmsp a where a.ksmchpar='000000007CB93CE0';
ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F666D754BE0      19225          1          1          1 KGLH0^3650f131   000000007BE19890       4096 recr           4095 000000007CB93CE0

SYS@book> alter session set events 'immediate trace name heapdump_addr level 2,addr 0x7CB93CE0';
Session altered.

--轉儲檔案:
******************************************************
HEAP DUMP heap name="KGLH0^3650f131"  desc=0x7cb93ce0
extent sz=0xfe8 alt=32767 het=56 rec=9 flg=2 opc=0
parent=0x60001190 owner=0x7cb93c90 nex=(nil) xsz=0xfd0 heap=(nil)
fl2=0x26, nex=(nil), dsxvers=1, dsxflg=0x0
dsx first ext=0x7be198c0
EXTENT 0 addr=0x7be198c0
  Chunk        07be198d0 sz=       80    perm      "perm           "  alo=80
Dump of memory from 0x000000007BE198D0 to 0x000000007BE19920
07BE198D0 00000051 40B38F00 00000000 00000000  [Q......@........]
07BE198E0 00000000 00000000 00000050 00000000  [........P.......]
07BE198F0 00000001 C0B38F00 00000000 00000000  [................]
07BE19900 7BE1A508 00000000 7CB93D58 00000000  [...{....X=.|....]
07BE19910 00000001 00000000 7BE198C0 00000000  [...........{....]
  Chunk        07be19920 sz=     3032    perm      "perm           "  alo=2360
Dump of memory from 0x000000007BE19920 to 0x000000007BE1A4F8
07BE19920 00000BD9 40B38F00 7BE198D0 00000000  [.......@...{....]
07BE19930 7BE198D0 00000000 00000938 00000000  [...{....8.......]
07BE19940 7CAB4BA8 00000000 7BE1A160 00000000  [.K.|....`..{....]
07BE19950 00000000 00000000 7CB93C90 00000000  [.........<.|....]
07BE19960 00000000 00000000 00000000 00000000  [................]
...

----很長擷取其中1部分.

$ grep "Chunk"  /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_44313.trc
  Chunk        07be198d0 sz=       80    perm      "perm           "  alo=80
  Chunk        07be19920 sz=     3032    perm      "perm           "  alo=2360
  Chunk        07be1a4f8 sz=      736    free      "               "
  Chunk        07be1a7d8 sz=      152    freeable  "kgltbtab       "
  Chunk        07be1a870 sz=       32    freeable  "kksfbc:hash1   "
  Chunk        07be1a4f8 sz=      736    free      "               "
  Chunk        07be198f0 sz=        0    kghdsx
  Chunk        07be19920 sz=     3032    perm      "perm           "  alo=2360
  Chunk        07be198d0 sz=       80    perm      "perm           "  alo=80

--使用大師tanelpoder的包看看裡面個個部分大小:

SYS@book>  @ &r/tpt/curheaps 911274289 %
old  20:        KGLNAHSH in (&1)
new  20:        KGLNAHSH in (911274289)
old  21: and    KGLOBT09 like ('&2')
new  21: and    KGLOBT09 like ('%')
  KGLNAHSH KGLHDPAR             CHILD# KGLHDADR         KGLOBHD0            SIZE0    SIZE1    SIZE2    SIZE3 KGLOBHD4            SIZE4    SIZE5 KGLOBHD6            SIZE6    SIZE7     STATUS
---------- ---------------- ---------- ---------------- ---------------- -------- -------- -------- -------- ---------------- -------- -------- ---------------- -------- -------- ----------
911274289 000000007CAB4BA8          0 000000007C56A510 000000007CE32168     4488        0        0        0 00                      0        0 000000007BE1A000    12144        0          1
911274289 000000007CAB4BA8      65535 000000007CAB4BA8 000000007CB93CE0     4720        0        0        0 00                      0        0 00                      0        0          1

old  10:     KSMCHDS = hextoraw('&v_curheaps_kglobhd0')
new  10:     KSMCHDS = hextoraw('000000007CB93CE0')
HEAP  CLASS    ALLOC_COMMENT         BYTES     CHUNKS
----- -------- ---------------- ---------- ----------
HEAP0 perm     permanent memor        3112          2
HEAP0 free     free memory             736          1
HEAP0 freeabl  kgltbtab                152          1
HEAP0 freeabl  kksfbc:hash1             32          1

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('00')

no rows selected

--可以發現裡面的大小都可以對上. 3112=3032+80. 
-- 80+3032+736+152+32=4032 , 4096-4032=64 相差64位元組.
-- 不過 select a.* from x$ksmsp a where a.ksmchpar='000000007CB93CE0'; 的輸出 KSMCHPTR= 000000007BE19890 ,如果你看下面的輸出最小KSMCHPTR=000000007BE198D0.
-- 比較後面2位 0xd0  = 208  0x90 = 144, 208-144=64 正好相差64位元組. 也就是輸出沒有包括頭部的64位元組.

--訪問x$ksmhp有點奇怪,如果直接打入:
SYS@book> select * from x$ksmhp;
no rows selected

SYS@book> select * from x$ksmhp where KSMCHDS = hextoraw('000000007CB93CE0');
ADDR                   INDX    INST_ID KSMCHDS          KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHOWN
---------------- ---------- ---------- ---------------- ---------------- ---------------- ---------- -------- ---------- ---------------- ----------------
00007F666D73B838          0          1 000000007CB93CE0 kksfbc:hash1     000000007BE1A870         32 freeabl           0 00               000000007CB93C90
00007F666D73B7E0          1          1 000000007CB93CE0 kgltbtab         000000007BE1A7D8        152 freeabl           0 00               000000007CB93C90
00007F666D73B788          2          1 000000007CB93CE0 free memory      000000007BE1A4F8        736 free              0 00               000000007CB93C90
00007F666D73B730          3          1 000000007CB93CE0 permanent memor  000000007BE19920       3032 perm              0 00               000000007CB93C90
00007F666D73B6D8          4          1 000000007CB93CE0 permanent memor  000000007BE198D0         80 perm              0 00               000000007CB93C90

--附上curheaps.sql的指令碼.
--------------------------------------------------------------------------------
--
-- File name:   curheaps.sql
-- Purpose:     Show main cursor data block heap sizes and their contents
--              (heap0 and heap6)
--
-- Author:      Tanel Poder
-- Copyright:   (c)
--
-- Usage:       @curheaps <hash_value> <child#>
--
--              @curheaps 942515969 %   -- shows a summary of cursor heaps
--              @curheaps 942515969 0   -- shows detail for child cursor 0
--
-- Other:       "Child" cursor# 65535 is actually the parent cursor
--
--------------------------------------------------------------------------------

col curheaps_size0 heading SIZE0 for 9999999
col curheaps_size1 heading SIZE1 for 9999999
col curheaps_size2 heading SIZE2 for 9999999
col curheaps_size3 heading SIZE3 for 9999999
col curheaps_size4 heading SIZE4 for 9999999
col curheaps_size5 heading SIZE5 for 9999999
col curheaps_size6 heading SIZE6 for 9999999
col curheaps_size7 heading SIZE7 for 9999999

col KGLOBHD0 new_value v_curheaps_kglobhd0 print
col KGLOBHD1 new_value v_curheaps_kglobhd1 noprint
col KGLOBHD2 new_value v_curheaps_kglobhd2 noprint
col KGLOBHD3 new_value v_curheaps_kglobhd3 noprint
col KGLOBHD4 new_value v_curheaps_kglobhd4 print
col KGLOBHD5 new_value v_curheaps_kglobhd5 noprint
col KGLOBHD6 new_value v_curheaps_kglobhd6 print
col KGLOBHD7 new_value v_curheaps_kglobhd7 noprint

select
    KGLNAHSH,
    KGLHDPAR,
    kglobt09 CHILD#,
    KGLHDADR,
    KGLOBHD0, KGLOBHS0 curheaps_size0,
    KGLOBHD1, KGLOBHS1 curheaps_size1,
    KGLOBHD2, KGLOBHS2 curheaps_size2,
    KGLOBHD3, KGLOBHS3 curheaps_size3,
    KGLOBHD4, KGLOBHS4 curheaps_size4,
    KGLOBHD5, KGLOBHS5 curheaps_size5,
    KGLOBHD6, KGLOBHS6 curheaps_size6,
    KGLOBHD7, KGLOBHS7 curheaps_size7,
--  KGLOBT00 CTXSTAT,
    KGLOBSTA STATUS
from
    X$KGLOB
--  X$KGLCURSOR_CHILD
where
    KGLNAHSH in (&1)
and KGLOBT09 like ('&2')
order by
        KGLOBT09 ASC
/

-- Cursor data block summary
select
   'HEAP0'        heap
  , ksmchcls      class
  , ksmchcom      alloc_comment
  , sum(ksmchsiz) bytes
  , count(*)      chunks
from
    x$ksmhp
where
    KSMCHDS = hextoraw('&v_curheaps_kglobhd0')
group by
   'HEAP0'
  , ksmchcls
  , ksmchcom
order by
    sum(ksmchsiz) desc
/

select
   'HEAP4'        heap
  , ksmchcls      class
  , ksmchcom      alloc_comment
  , sum(ksmchsiz) bytes
  , count(*)      chunks
from
    x$ksmhp
where
    KSMCHDS = hextoraw('&v_curheaps_kglobhd4')
group by
   'HEAP4'
  , ksmchcls
  , ksmchcom
order by
    sum(ksmchsiz) desc
/

 

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
/
-- Cursor data block details

-- select * from x$ksmhp where KSMCHDS = hextoraw('&v_curheaps_kglobhd0');
-- select * from x$ksmhp where KSMCHDS = hextoraw('&v_curheaps_kglobhd6');