ITPub博客

首页 > 数据库 > Oracle > 如何定位SQL语句在共享池里用到了哪些chunks

如何定位SQL语句在共享池里用到了哪些chunks

原创 Oracle 作者:oliseh 时间:2016-02-06 10:48:13 0 删除 编辑

一条SQL语句执行后,如何找到其在shared pool里占用的chunks,做了一个简单的测试:


---先清空shared pool
alter system flush shared_pool;


---执行sql语句
select * from scott.t1 where username='SYS';


USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
SYS                                     0 20141110 21:16:12


---这时我们可以看到一个父游标和一个子游标生成了
set linesize 180 pagesize 100 word wrapped
col kglnaobj format a100 word_wrapped
select kglhdadr,kglhdpar,kglnaobj from x$kglob where kglnaobj like 'select * from scott.t1 where username=%';


KGLHDADR         KGLHDPAR         KGLNAOBJ
---------------- ---------------- ----------------------------------------------------------------------------------------------------
07000001B84F85E8 07000001B8670420 select * from scott.t1 where username='SYS'      <---KGLHDADR!=KGLHDPAR,07000001B84F85E8是存放子游标handle的地址
07000001B8670420 07000001B8670420 select * from scott.t1 where username='SYS'      <---KGLHDADR=KGLHDPAR,07000001B8670420是存放父游标handle的地址


---以上查出的结果中都是父/子游标handle所在的地址,因为handle会最终指向父/子游标,handle所在的地址也可以看成是指向handle的指针,我们通过shared pool的dump可以找到07000001B8670420这个指向handle的指针,其位于起始地址为7000001b86703f0所在的chunk内,该chunk正是父游标的handle
alter session set tracefile_identifier='level2dmp.txt';
alter system set events 'immediate trace name heapdump level 2';


***tstdb1_ora_3670294_level2dmp.txt trace文件里的信息,我们能看到07000001B8670420这一地址位于"sga heap(1,1)"的EXTENT 1段落中,每个extent的大小称为一个granule size,这里的granule size为16777216 bytes
HEAP DUMP heap name="sga heap(1,1)"  desc=700000000056950
 extent sz=0xfe0 alt=248 het=32767 rec=9 flg=-126 opc=0
 parent=0 owner=0 nex=0 xsz=0x1000000 heap=0
 fl2=0x20, nex=0, dsxvers=1, dsxflg=0x0
 dsx first ext=0xb8000000
 latch set 1 of 4
 durations enabled for this heap
 reserved granules for root 11 (granule size 16777216)
EXTENT 0 addr=70000019b000000
。。。。省略部分内容


EXTENT 1 addr=7000001b8000000
  Chunk  7000001b8000058 sz=       80    perm      "perm           "  alo=80
  Chunk  7000001b80000a8 sz=       48  R-freeable  "reserved stoppe"
  Chunk  7000001b80000d8 sz=   839416  R-free      "               "
。。。。省略部分内容
  Chunk  7000001b866fb90 sz=     1072    recreate  "KQR PO         "  latch=7000001b0d5edb0
  Chunk  7000001b866ffc0 sz=     1072    recreate  "KQR PO         "  latch=7000001b0d5edb0
  Chunk  7000001b86703f0 sz=      584    recreate  "KGLHD          "  latch=0                            <---父游标handle的地址(也即指向父游标handle的指针)包含在起始地址为7000001b86703f0的chunk里,这个chunk的大小为584bytes,类型为KGLHD
  Chunk  7000001b8670638 sz=      160    free      "               "
  Chunk  7000001b86706d8 sz=      528    recreate  "KGLHD          "  latch=0
。。。。省略部分内容


---通过父游标的handle所在地址能够关联出父游标heap 0的描述符地址
col KGLNAOBJ format a80
set linesize 190 pagesize 20
select kglhdadr,kglnaobj,kglobhd0 from x$kglob where kglhdadr='07000001B8670420';
KGLHDADR         KGLNAOBJ                                                                         KGLOBHD0
---------------- -------------------------------------------------------------------------------- ----------------
07000001B8670420 select * from scott.t1 where username='SYS'                                      070000019BB137F0    <---父游标heap 0的描述符(Descriptor)所在地址


---根据上一步得出的heap 0 描述符,找到父游标heap 0,heap 0存放有我们熟悉的object name、dependency table等内容
SYS@tstdb1-SQL> select ksmchcom,ksmchptr,ksmchsiz,ksmchcls,ksmchpar from x$ksmsp where ksmchpar='070000019BB137F0';


KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS KSMCHPAR
---------------- ---------------- ---------- -------- ----------------
KGLH0^f6439b10   0700000195D40F60       4096 recr     070000019BB137F0    <---由此可见父游标heap 0存在于0700000195D40F60地址开始的chunk中,大小为4096 bytes


---为了能看清我们父游标handle与父游标heap 0在内存中的结构,我们使用更为详尽的level对sga进行dump,这个dump命令可没有那么快的完成,dump所耗的时间与你的shared pool大小成正比
alter session set tracefile_identifier='level2050dmp.txt';
alter system set events 'immediate trace name heapdump level 2050';


***从tstdb1_ora_15270042_level2050dmp.txt文件中
>>>>>>>>父游标heap 0所在的chunk信息
。。。。省略部分内容
  Chunk  700000195d40f60 sz=     4096    recreate  "KGLH0^f6439b10 "  latch=0   <---heap 0大小为4096 bytes仅包含一个chunk,由描述符ds=70000019bb137f0指向这个chunk
     ds  70000019bb137f0 sz=     4096 ct=        1
Dump of memory from 0x0700000195D40F60 to 0x0700000195D41F60
700000195D40F60 80B38F00 00001001 07000001 95D3FF60  [...............`]
700000195D40F70 00000000 00000000 00000000 00000000  [................]
700000195D40F80 00000000 00000000 00000000 0FFF0AE0  [................]
700000195D40F90 07000001 9BB137F0 00000000 00000000  [......7.........]
700000195D40FA0 40B38F00 00000051 00000000 00000000  [@......Q........]
700000195D40FB0 00000000 00000000 00000050 95D413F8  [...........P....]
700000195D40FC0 C0B38F00 00000001 00000000 00000000  [................]
700000195D40FD0 07000001 95D41BE0 07000001 9BB13868  [..............8h]
700000195D40FE0 00000001 00000000 07000001 95D40F90  [................]
700000195D40FF0 40B38F00 00000BE1 07000001 95D40FA0  [@...............]
700000195D41000 07000001 95D40FA0 00000950 00000000  [...........P....]
700000195D41010 07000001 B8670420 07000001 95D41848  [.....g. .......H]
700000195D41020 00000000 00000000 07000001 9BB137A0  [..............7.]
700000195D41030 00000000 00000000 00000000 00000000  [................]
        Repeat 7 times
700000195D410B0 82030003 00000000 10008100 00000000  [................]
700000195D410C0 00000000 00000020 00000000 00000000  [....... ........]
。。。。省略部分内容
        Repeat 6 times
700000195D41F40 10B38F00 00000021 07000001 95D41EA8  [.......!........]
700000195D41F50 00000001 09D3E808 00000000 00000000  [................]
  Chunk  700000195d41f60 sz=     4096    recreate  "KGLH0^5bcbf644 "  latch=0


>>>>>>>>父游标heap 0描述符所在的chunk信息
。。。。省略部分内容
  Chunk  70000019bb13788 sz=      240    freeable  "KGLDA          "        <---描述符ds=70000019bb137f0地址存在于以70000019bb13788为起始地址的chunk中
Dump of memory from 0x070000019BB13788 to 0x070000019BB13878
70000019BB13780                   00B38F00 000000F1          [........]
70000019BB13790 07000001 9BB13730 00000001 09B01958  [......70.......X]
70000019BB137A0 07000001 9BB137F0 07000001 95D410B0  [......7.........]
70000019BB137B0 00000000 01000300 00000000 00000000  [................]
70000019BB137C0 00000FE8 000009D8 07000001 95D41010  [................]
70000019BB137D0 07000001 95D40F90 00000000 00000000  [................]
70000019BB137E0 00000003 0C1F2D58 07000001 B54C6120  [......-X.....La ]
70000019BB137F0 07000000 00000198 00000FE8 00000000  [................]
70000019BB13800 07000001 9BB137A0 07000001 95D40F90  [......7.........]
70000019BB13810 07000001 95D40FF0 00000000 00000000  [................]
70000019BB13820 00000000 00000000 09020100 00000000  [................]
70000019BB13830 00000000 00000000 00000FD0 4B474C48  [............KGLH]
70000019BB13840 305E6636 34333962 31300026 7FFF0038  [0^f6439b10.&...8]
70000019BB13850 00307FFF 00000000 01030000 00000000  [.0..............]
70000019BB13860 00000000 00000000 07000001 95D40FD0  [................]
70000019BB13870 07000001 95D41BE0                    [........]
  Chunk  70000019bb13878 sz=      560    recreate  "KQR PO         "  latch=7000001b0d5f050
Dump of memory from 0x070000019BB13878 to 0x070000019BB13AA8


---通过指向子游标handle地址的指针07000001B84F85E8能够关联出子游标heap 0的描述符地址
col KGLNAOBJ format a80
set linesize 190 pagesize 20
select kglhdadr,kglnaobj,kglobhd0 from x$kglob where kglhdadr='07000001B84F85E8';
KGLHDADR         KGLNAOBJ                                                                         KGLOBHD0
---------------- -------------------------------------------------------------------------------- ----------------
07000001B84F85E8 select * from scott.t1 where username='SYS'                                      07000001B84F8530   <---子游标heap 0的描述符(Descriptor)所在chunk起始地址


***在tstdb1_ora_15270042_level2050dmp.txt文件中可以找到包含07000001B84F8530和07000001B84F85E8地址所在的段落:
  Chunk  7000001b84f84c8 sz=      240    freeable  "KGLDA          "        <---子游标heap 0描述符的地址所在chunk起始地址
Dump of memory from 0x07000001B84F84C8 to 0x07000001B84F85B8
7000001B84F84C0                   00B38F00 000000F1          [........]
7000001B84F84D0 07000001 B84F83D8 00000001 09B01958  [.....O.........X]
7000001B84F84E0 07000001 B84F8530 07000001 95D400B0  [.....O.0........]
7000001B84F84F0 00000000 01000300 07000001 A885E598  [................]
7000001B84F8500 00000FE8 00000A60 07000001 95D40010  [.......`........]
7000001B84F8510 07000001 95D3FF90 00000000 00000000  [................]
7000001B84F8520 00000003 0C1F2D58 07000001 B54C6120  [......-X.....La ]
7000001B84F8530 07000000 00000198 00000FE8 00000000  [................]
7000001B84F8540 07000001 B84F84E0 07000001 95D3FF90  [.....O..........]
7000001B84F8550 07000001 95D3FFF0 00000000 00000000  [................]
7000001B84F8560 00000000 00000000 09020100 00000000  [................]
7000001B84F8570 00000000 00000000 00000FD0 4B474C48  [............KGLH]
7000001B84F8580 305E6636 34333962 31300026 7FFF0038  [0^f6439b10.&...8]
7000001B84F8590 00307FFF 00000000 01030000 00000000  [.0..............]
7000001B84F85A0 00000000 00000000 07000001 95D3FFD0  [................]
7000001B84F85B0 07000001 95D40A10                    [........]
  Chunk  7000001b84f85b8 sz=      352    recreate  "KGLHD          "  latch=0   <---指向子游标handle地址的指针,它和上面的描述符恰巧在相邻的两个chunk内
Dump of memory from 0x07000001B84F85B8 to 0x07000001B84F8718
7000001B84F85B0                   80B38F00 00000161          [.......a]
7000001B84F85C0 07000001 B84F84C8 00000000 00000000  [.....O..........]
7000001B84F85D0 00000000 00000000 00000000 00000000  [................]
7000001B84F85E0 00000000 00500800 07000001 B84F85E8  [.....P.......O..]
7000001B84F85F0 07000001 B84F85E8 07000001 95D40010  [.....O..........]
7000001B84F8600 00000000 00000000 00000000 10012111  [..............!.]
7000001B84F8610 00010000 00010000 00010001 00000001  [................]
7000001B84F8620 00000000 00000001 00000100 00000000  [................]
7000001B84F8630 00000000 00000000 00000000 00000000  [................]
7000001B84F8640 00000000 00000000 07000001 B84F8648  [.............O.H]
7000001B84F8650 07000001 B84F8648 07000001 B84F8658  [.....O.H.....O.X]
7000001B84F8660 07000001 B84F8658 07000001 B84F8668  [.....O.X.....O.h]
7000001B84F8670 07000001 B84F8668 07000001 B84F8678  [.....O.h.....O.x]
7000001B84F8680 07000001 B84F8678 07000001 95D41918  [.....O.x........]
7000001B84F8690 07000001 95D41918 00000000 00000000  [................]
7000001B84F86A0 00000000 00000000 00000000 00000000  [................]
7000001B84F86B0 00000000 00000000 07000001 B8670550  [.............g.P]
7000001B84F86C0 07000001 B84F86C0 07000001 B84F86C0  [.....O.......O..]
7000001B84F86D0 07000001 B84F86D0 07000001 B84F86D0  [.....O.......O..]
7000001B84F86E0 00000000 00000000 07000001 B0EA1A60  [...............`]
7000001B84F86F0 00000000 00000000 00000000 00000000  [................]
7000001B84F8700 00000001 00000002 00000000 00000000  [................]
7000001B84F8710 00000000 00000000                    [........]


---根据上一步得出的heap 0 描述符,找到子游标heap 0所在chunk            
SYS@tstdb1-SQL> select ksmchcom,ksmchptr,ksmchsiz,ksmchcls,ksmchpar from x$ksmsp where ksmchpar='07000001B84F8530';


KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS KSMCHPAR
---------------- ---------------- ---------- -------- ----------------
KGLH0^f6439b10   0700000195D3FF60       4096 recr     07000001B84F8530    <---由此可见子游标heap 0存在于0700000195D3FF60地址开始的chunk中,大小为4096 bytes


***在tstdb1_ora_15270042_level2050dmp.txt文件中可以找到包含700000195D3FF60这个地址的chunk
  Chunk  700000195d3ff60 sz=     4096    recreate  "KGLH0^f6439b10 "  latch=0
     ds  7000001b84f8530 sz=     4096 ct=        1
Dump of memory from 0x0700000195D3FF60 to 0x0700000195D40F60
700000195D3FF60 80B38F00 00001001 07000001 95D3EF60  [...............`]
700000195D3FF70 07000001 99E4EAC0 07000001 B89F3648  [..............6H]
700000195D3FF80 00000000 00000000 00000000 0FFF0200  [................]
700000195D3FF90 07000001 B84F8530 00000000 00000000  [.....O.0........]
700000195D3FFA0 40B38F00 00000051 00000000 00000000  [@......Q........]
700000195D3FFB0 00000000 00000000 00000050 00000000  [...........P....]
700000195D3FFC0 C0B38F00 00000001 00000000 00000000  [................]
700000195D3FFD0 07000001 95D40A10 07000001 B84F85A8  [.............O..]
700000195D3FFE0 00000001 00000000 07000001 95D3FF90  [................]
700000195D3FFF0 40B38F00 00000A11 07000001 95D3FFA0  [@...............]
700000195D40000 07000001 95D3FFA0 00000898 00000000  [................]
700000195D40010 07000001 B84F85E8 07000001 95D403D0  [.....O..........]
700000195D40020 00000000 00000000 07000001 B84F84E0  [.............O..]
。。。。省略部分内容


---以同样的方法得到子游标heap 6所在chunk
select kglhdadr,kglnaobj,kglobhd6 from x$kglob where kglhdadr='07000001B84F85E8';
KGLHDADR         KGLNAOBJ                                                                         KGLOBHD6
---------------- -------------------------------------------------------------------------------- ----------------
07000001B84F85E8 select * from scott.t1 where username='SYS'                                      0700000195D416E8      <---子游标heap 6的描述符(Descriptor)所在地址


select ksmchcom,ksmchptr,ksmchsiz,ksmchcls,ksmchpar from x$ksmsp where ksmchpar='0700000195D416E8';


KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS KSMCHPAR
---------------- ---------------- ---------- -------- ----------------
SQLA^f6439b10    0700000199EA5AB0       4096 recr     0700000195D416E8      <---heap 6含有2个大小都是4096 bytes的chunk
SQLA^f6439b10    0700000199E9FAB0       4096 freeabl  0700000195D416E8


***在tstdb1_ora_15270042_level2050dmp.txt文件中可以找到包含0700000195D416E8 chunk的段落
  Chunk  700000199ea5ab0 sz=     4096    recreate  "SQLA^f6439b10  "  latch=0
     ds  700000195d416e8 sz=     8192 ct=        2
         700000199e9fab0 sz=     4096
Dump of memory from 0x0700000199EA5AB0 to 0x0700000199EA6AB0
700000199EA5AB0 80B38F00 00001001 07000001 99EA4AB0  [..............J.]
700000199EA5AC0 07000001 95D3DF70 07000001 B8796E90  [.......p.....yn.]
700000199EA5AD0 00000000 00000000 00000000 0FFF0279  [...............y]
700000199EA5AE0 07000001 95D416E8 00000000 00000000  [................]
700000199EA5AF0 40B38F00 00000051 00000000 00000000  [@......Q........]
700000199EA5B00 00000000 00000000 00000050 000000D8  [...........P....]
700000199EA5B10 C0B38F00 00000001 00000000 00000000  [................]
700000199EA5B20 07000001 95D41760 07000001 95D41760  [.......`.......`]
700000199EA5B30 00000001 00000000 07000001 99EA5AE0  [..............Z.]
700000199EA5B40 00B38F00 00000069 07000001 99EA5AF0  [.......i......Z.]
700000199EA5B50 00000001 0B018734 01000000 00000000  [.......4........]


简单总结一下:
定位到父游标heap 0:
指向父游标handle的指针:07000001B8670420(类型为KGLHD,x$kglob)->父游标heap 0的描述符070000019BB137F0(类型为KGLDA,x$kglob)->父游标heap 0所在chunk地址:0700000195D40F60(类型为KGLH0,x$ksmsp)


找到父游标下的子游标,可以使用:
select kglhdadr,kglnaobj from x$kglob where kglnaobj like 'select * from scott.t1 where username=%' and kglhdadr!=kglhdpar;


定位到子游标heap 0、heap 6:
子游标handle的指针:07000001B84F85E8(类型为KGLHD,x$kglob)->子游标heap 0的描述符07000001B84F8530(类型为KGLDA,x$kglob)->heap 0所在chunk地址:0700000195D3FF60(类型为KGLH0,x$ksmsp)
子游标handle的指针:07000001B84F85E8(类型为KGLHD,x$kglob)->子游标heap 6的描述符0700000195D416E8(包含在父游标的heap 0所在的chunk里,x$kglob)->heap 6所在chunk地址:0700000199EA5AB0、0700000199E9FAB0(类型为SQLA,x$ksmsp)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/53956/viewspace-1986897/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1637940