[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) http://www.tanelpoder.com
--
-- 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');
