[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) 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');

时间： 2024-09-20 14:29:42

[20160407]sql语句父子游标的堆转储.txt的相关文章

[20161228]sql语句父子游标的堆转储2.txt

[20161228]sql语句父子游标的堆转储2.txt --以前仅仅看了父游标堆0的堆转储,链接:http://blog.itpub.net/267265/viewspace-2076605/ --尽然当时没有做子游标堆0,6的堆转储,今天测试看看. 1.环境: SYS@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -----------

[20120229]无效sql语句与shared pool的问题.txt

昨天遇到一些程序的bug,因为查询要显示1年的信息,因为2011年没有2月29号,导致查询出错.由此想到另外的问题,如果查询存在这些语句,会保留在共享池吗?自己做了一个测试: SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Relea

Oracle中解析SQL语句的过程

为了将用户写的SQL文本转化为Oracle认识的且可执行的语句,这个过程就叫做解析过程.解析分为硬解析和软解析.一条SQL语句在第一次被执行时必须进行硬解析. 当客户端发出一条SQL语句(也可以是一个存储过程或者一个匿名PL/SQL块)进入shared pool时(注意,我们从前面已经知道,Oracle对这些SQL不叫做SQL语句,而是称为游标.因为Oracle在处理SQL时,需要很多相关的辅助信息,这些辅助信息与SQL语句一起组成了游标), Oracle首先将SQL文本转化为ASCII值,然后

使用优化器性能视图获取SQL语句执行环境

    Oracle SQL语句的运行环境分为多个不同的层次,主要包括实例级别,会话级别,语句级别,其优先级依次递增.即语句级别的执行环境具有最高的优先权,会话级别次之,实例级别最低.反过来,实例级别的环境设置影响全局,而会话级别的则影响当前会话,语句级别的设置当然也就只影响当前语句.由此可知,运行环境中每一个环节的参数都对最终的数据库性能或所执行的SQL语句有直接的影响.因此在对数据库优化或调试SQL时,获得当前SQL语句运行环境显得尤为重要.为此,Oracle提供了三个重要的视图来获取不同级

[20171110]sql语句相同sql_id可以不同吗

[20171110]sql语句相同sql_id可以不同吗.txt --//提一个问题,就是sql语句相同sql_id可以不同吗? --//使用dbms_shared_pool.markhot就可以做到. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------

[20161230]查看父游标中sql语句.txt

[20161230]查看父游标中sql语句.txt --上午巡检完,无聊,测试使用oradebug下查看sql语句在父游标中的内容.sql语句在执行第一次硬解析时生成父子游标,其中父游标chunk --中保存sql语句,测试通过oradebug下如何查看: 1.环境: SCOTT@book> select * from dept where deptno=10;     DEPTNO DNAME          LOC ---------- -------------- -----------

[20160215]超长sql语句与父子光标.txt

[20160215]超长sql语句与父子光标.txt --看<oracle内核技术揭秘>提到sql语句不会进入保留池, 要进入保留池,chunk的大小必须大于_shared_pool_reserved_min_alloc. --而实际上许多sql语句一般最大4096字节.而且这些内存分配的原则是首先从共享池分配,不行并且大于 --shared_pool_reserved_min_alloc才会从保留池申请. --作者视乎忘记一种特殊情况就是超长sql语句,这样父游标保存sql的语句chunk可

SQL语句查询结果集中的动态修改案例(临时表+游标)

本文转载:http://www.cnblogs.com/Charles2008/archive/2008/03/04/1090314.html 曾经一位朋友问我这样一个问题:怎样在查询出来的结果集中增加一个新列(有规律)? 如:数据库中的结构和数据如下: (tableName : People)Name           Age                                                                                    

20150213关于共享池4-SQL内存结构父子游标

[20150213]关于共享池4x-SQL内存结构父子游标.txt --这个主要和recr和freeabl类似. --1.节约内存 --2.减少检索链表的时间. --3.oracle的算法规定,sql语句必须至少是一父一子的情况.很多情况下都是一父多子.也就是说,每个游标,oracle都会为它设置个父游标 --  如果有sql文本相同,但无法共享执行计划的情况出现,那就会出现一父多子的情况. --注意除了sql对象,共享池中其它类型的对象都没有父子游标的概念. --自己按照的介绍,重复测试一遍,