分享程序员开发的那些事...
更新时间：2022-09-13 10:40:42
[20150213]关于共享池4x-SQL内存结构父子游标.txt
--这个主要和recr和freeabl类似.
--1.节约内存
--2.减少检索链表的时间.
--3.oracle的算法规定,sql语句必须至少是一父一子的情况.很多情况下都是一父多子.也就是说,每个游标,oracle都会为它设置个父游标
-- 如果有sql文本相同,但无法共享执行计划的情况出现,那就会出现一父多子的情况.
--注意除了sql对象,共享池中其它类型的对象都没有父子游标的概念.
--自己按照的介绍,重复测试一遍,加强理解:
1.测试环境:
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SCOTT@test> select * from dept where deptno=10;
DEPTNO DNAME LOC
------------ -------------- -------------
10 ACCOUNTING NEW YORK
SCOTT@test> @&r/dpc '' ''
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
SQL_ID 4xamnunv51w9j, child number 0
-------------------------------------
select * from dept where deptno=10
Plan hash value: 2852011669
---------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 (0)|
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 0 (0)|
---------------------------------------------------------------------
--sql_id=4xamnunv51w9j
2.sql的内存结构:父游标句柄
--做一个共享池2050级的dump,会将共享池的每个字节内容都dump出来.(注意如何很大,需要时间很长,磁盘空间消耗也很大).
--在我的测试240M的共享池子,转储780M.
SCOTT@test> alter session set events'immediate trace name heapdump level 2050';
Session altered.
SCOTT@test> @&r/pp
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/admin/test/udump/test_ora_8738.trc
SCOTT@test> host ls -l /u01/app/oracle/admin/test/udump/test_ora_8738.trc
-rw-r----- 1 oracle oinstall 786093610 Feb 15 08:26 /u01/app/oracle/admin/test/udump/test_ora_8738.trc
--父游标句柄:
$ cat shp4.sql
SELECT DECODE (kglhdadr,
kglhdpar, '父游标句柄地址',
'子游标句柄地址')
text,
kglhdadr,
kglhdpar,
kglnaobj,
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16
FROM x$kglob
WHERE kglobt03 = '&1';
SYS@test> column KGLNAOBJ format a40
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j
old 13: WHERE kglobt03 = '&1'
new 13: WHERE kglobt03 = '4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ------------ ------------ ------------ --------------------------
子游标句柄地址 0000000072B96820 0000000076390710 select * from dept where deptno=10 0000000076115D70 0000000073039BB8 3664 8088 1803 13555
父游标句柄地址 0000000076390710 0000000076390710 select * from dept where deptno=10 0000000072B7C3D8 00 2812 0 0 2812
--可以发现KGLHDADR=0000000076390710是父游标句柄地址,直接使用它查询x$ksmsp的ksmchptr是不行的.存在一个偏移量
--正常是偏移0x30(48字节)是父游标句柄开始的chunk地址(00000000763906E0):
SYS@test> select * from x$ksmsp where ksmchptr='00000000763906E0';
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ------------ ------------ ------------ ------------ ---------------- ---------------- ------------ -------- ------------ ----------------
00007F58BF7CF778 47722 1 1 2 KGL handles 00000000763906E0 528 recr 48 00
--我自己写一个脚本：
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ;
SYS@test> SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&y', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ;
Enter value for y: 0000000076390710
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ------------ ------------ ------------ ------------ ---------------- ---------------- ------------ -------- ------------ ----------------
00007F58BF7CF778 47722 1 1 2 KGL handles 00000000763906E0 528 recr 48 00
--好像也是正确的！
Chunk 0763906e0 sz= 528 recreate "KGL handles " latch=0x79547ae0
Dump of memory from 0x00000000763906E0 to 0x00000000763908F0
0763906E0 00000211 80B38F00 76390638 00000000 [........8.9v....]
0763906F0 74D56D70 00000000 74E82148 00000000 [pm.t....H!.t....]
076390700 79547AE0 00000000 00000000 00080030 [.zTy........0...]
076390710 796928F0 00000000 796928F0 00000000 [.(iy.....(iy....]
076390720 00000000 00000000 00000000 00000000 [................]
Repeat 3 times
076390760 76390860 00000000 00000000 00000000 [`.9v............]
076390770 76390770 00000000 76390770 00000000 [p.9v....p.9v....]
076390780 76390780 00000000 76390780 00000000 [..9v......9v....]
076390790 76390790 00000000 76390790 00000000 [..9v......9v....]
0763907A0 00010002 00000000 77EEA428 00000000 [........(..w....]
0763907B0 77EEA428 00000000 763907B8 00000000 [(..w......9v....]
0763907C0 763907B8 00000000 763907C8 00000000 [..9v......9v....]
0763907D0 763907C8 00000000 00000002 00000001 [..9v............]
0763907E0 00000004 120100D0 763907E8 00000000 [..........9v....]
0763907F0 763907E8 00000000 00000000 00000000 [..9v............]
076390800 76390800 00000000 76390800 00000000 [..9v......9v....]
076390810 76390810 00000000 76390810 00000000 [..9v......9v....]
076390820 77059D78 00000000 00000001 00010001 [x..w............]
076390830 00000000 00000002 00000000 00000000 [................]
076390840 00000001 00000000 00000002 00000000 [................]
076390850 00000000 00000000 00000000 00000000 [................]
076390860 1431C45D BDDBB9E7 4EAA74D5 3650F131 [].1......t.N1.P6]
076390870 00000000 02737801 0719090F 00000000 [.....xs.........]
076390880 00000000 00000000 00000023 00000000 [........#.......]
076390890 00000000 00000000 00000000 00000000 [................]
0763908A0 763908B0 00000000 00000000 00000000 [..9v............]
0763908B0 656C6573 2A207463 6F726620 6564206D [select * from de]
0763908C0 77207470 65726568 70656420 3D6F6E74 [pt where deptno=]
0763908D0 00003031 00000000 00000000 00000000 [10..............]
0763908E0 00000000 00000000 00000000 00000000 [................]
--从父游标句柄里面可以发现执行sql的文本.
--另外可以发现父游标句柄的chunk类型是recr.大小528字节.
3.SQL的chunk:父游标堆0:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j
TEXT KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ------------ ------------ ------------ --------------------------
子游标句柄地址 0000000072B96820 0000000076390710 select * from dept where deptno=10 0000000076115D70 0000000073039BB8 3664 8088 1803 13555
父游标句柄地址 0000000076390710 0000000076390710 select * from dept where deptno=10 0000000072B7C3D8 00 2812 0 0 2812
--注意父游标句柄地址那行:KGLOBHD0 = 0000000072B7C3D8 就是父游标堆0描述符(DS)地址.
SYS@test> select a.* from x$ksmsp a where a.ksmchpar='0000000072B7C3D8';
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ------------ ------------ ------------ ------------ ---------------- ---------------- ------------ -------- ------------ ----------------
00007F58BF3DDCC8 25612 1 1 3 CCursor 0000000077059D18 1112 recr 4095 0000000072B7C3D8
00007F58BF2E8308 35759 1 1 3 CCursor 00000000730398E0 1112 freeabl 0 0000000072B7C3D8
00007F58BF2E82B0 35760 1 1 3 CCursor 0000000073039488 1112 freeabl 0 0000000072B7C3D8
--注意查询条件是a.ksmchpar='0000000072B7C3D8'。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--可以发现父游标堆0的chunk有3个，1个是recr类型，两个freeabl类型，大小1112.
Chunk 077059d18 sz= 1112 recreate "CCursor " latch=(nil)
ds 072b7c3d8 sz= 3336 ct= 3
073039488 sz= 1112
0730398e0 sz= 1112
Dump of memory from 0x0000000077059D18 to 0x000000007705A170
077059D10 00000459 80B38F00 [Y.......]
077059D20 770598C0 00000000 00000000 00000000 [...w............]
077059D30 00000000 00000000 00000000 00000000 [................]
077059D40 00000000 400A0FFF 72B7C3D8 00000000 [.......@...r....]
077059D50 00000000 00000000 00000309 40B38F00 [...............@]
077059D60 00000000 00000000 00000000 00000000 [................]
077059D70 00000308 00000000 76390710 00000000 [..........9v....]
077059D80 77059D80 00000000 77059D80 00000000 [...w.......w....]
077059D90 77059D90 00000000 77059D90 00000000 [...w.......w....]
077059DA0 7705A080 00000000 00000000 00010001 [...w............]
077059DB0 77059E50 00000000 00000000 00000000 [P..w............]
077059DC0 00000000 00000000 00000000 00000000 [................]
Repeat 8 times
077059E50 72B7C3D8 00000000 00000000 00000000 [...r............]
077059E60 77059E90 00000000 00000000 00000000 [...w............]
077059E70 78326570 00000000 000008B0 000004C0 [pe2x............]
077059E80 00000000 00000000 77059D48 00000000 [........H..w....]
077059E90 00030382 00000000 10008100 00000000 [................]
077059EA0 00000000 00000020 00000000 00000000 [.... ...........]
077059EB0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
077059ED0 00000000 00000000 73039AC0 00000000 [...........s....]
077059EE0 00000001 00000000 2A4C5153 73756C50 [........SQL*Plus]
077059EF0 72616369 33676465 4E542820 31562053 [icaredg3 (TNS V1]
077059F00 2933562D 00000000 00000000 00000000 [-V3)............]
077059F10 00000000 00000000 00000008 00000000 [................]
077059F20 DABEFA60 244D4C52 43545645 4E41454C [`...RLM$EVTCLEAN]
077059F30 20655055 69746341 656E6E6F 00000000 [UPe Actionne....]
077059F40 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
077059F60 7705A010 00000000 73039AA8 00000000 [...w.......s....]
077059F70 00000000 00000000 1896BE79 00000000 [........y.......]
077059F80 00000000 00000000 00000007 00000000 [................]
077059F90 3650F131 00000000 CC44B83C 00000000 [1.P6....077059FA0 72B7C3D8 00000000 00000001 00000000 [...r............]
077059FB0 00000000 00000000 00000000 00000000 [................]
Repeat 3 times
077059FF0 3650F131 00000000 00000001 00000020 [1.P6........ ...]
07705A000 00000000 00000000 00000000 00000000 [................]
07705A010 73039928 00000000 00000000 00000000 [(..s............]
07705A020 00000000 00000000 00000000 00000000 [................]
Repeat 2 times
07705A050 00000001 0000000D 00000001 00000000 [................]
07705A060 000000F1 40B38F00 77059D58 00000000 [.......@X..w....]
07705A070 73039908 00000000 000000F0 00000000 [...s............]
07705A080 00000000 00000000 00000000 00000000 [................]
07705A090 00000000 00000000 7705A130 00000000 [........0..w....]
07705A0A0 00000010 00000000 00000004 00000000 [................]
07705A0B0 00000000 00000000 00000000 00000000 [................]
Repeat 7 times
07705A130 73039860 00000000 00000000 00000000 [`..s............]
07705A140 00000000 00000000 00000000 00000000 [................]
07705A150 00000021 10B38F00 7705A060 00000000 [!.......`..w....]
07705A160 0562ADDC 00000000 00000000 00000000 [..b.............]
Chunk 073039488 sz= 1112 freeable "CCursor " ds=0x72b7c3d8
Dump of memory from 0x0000000073039488 to 0x00000000730398E0
073039480 00000459 00B38F00 [Y.......]
073039490 73039030 00000000 60037D80 00000000 [0..s.....}.`....]
0730394A0 72B7C3D8 00000000 730398F8 00000000 [...r.......s....]
0730394B0 00000399 40B38F00 00000000 00000000 [.......@........]
0730394C0 7705A060 00000000 00000048 00000000 [`..w....H.......]
0730394D0 72B968F8 00000000 72B968F8 00000000 [.h.r.....h.r....]
0730394E0 72B96820 00000000 00000004 00000002 [ h.r............]
0730394F0 76390710 00000000 0000000A 0000000D [..9v............]
073039500 00000012 0000001B 00000021 00000022 [........!..."...]
073039510 00000029 0000002B 00000032 0000000C [)...+...2.......]
073039520 0000000E 0000000F 00000010 00000016 [................]
073039530 00000017 0000001C 0000001D 0000001E [................]
073039540 0000001F 00000020 00000026 0000002A [.... ...&...*...]
073039550 00000030 00000033 00000034 00000037 [0...3...4...7...]
073039560 73039590 00000000 74CCC400 00000000 [...s.......t....]
073039570 00000001 00000001 00000080 00300030 [............0.0.]
073039580 056B8080 00000000 00000000 00000000 [..k.............]
073039590 73039598 00000000 00000302 00000016 [...s............]
0730395A0 00000000 01206001 00000000 00000000 [.....` .........]
0730395B0 00000000 00000000 00000000 00000000 [................]
0730395C0 00000018 00000000 73039678 00000000 [........x..s....]
0730395D0 00000010 00000000 00000004 00000000 [................]
0730395E0 00000000 00000000 00000000 00000000 [................]
Repeat 2 times
073039610 730397A0 00000000 00000010 00000000 [...s............]
073039620 00000004 00000000 00000000 00000000 [................]
073039630 00000000 00000000 00000000 00000000 [................]
Repeat 3 times
073039670 00000000 00000000 73039860 00000000 [........`..s....]
073039680 00000000 00000000 00000000 00000000 [................]
073039690 00000000 00000000 73039698 00000000 [...........s....]
0730396A0 73039698 00000000 00000000 00000000 [...s............]
0730396B0 00000002 00000001 704AFEA0 00000000 [..........Jp....]
0730396C0 00000139 00000001 00000000 00000000 [9...............]
0730396D0 00000000 00000000 00000000 00000000 [................]
0730396E0 00000000 02737800 1E1C170D 00000000 [.....xs.........]
0730396F0 730396F0 00000000 730396F0 00000000 [...s.......s....]
073039700 00000000 00000000 00000002 00000041 [............A...]
073039710 704AFEA0 00000000 00000000 00000001 [..Jp............]
073039720 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
073039740 00000000 00000000 73039748 00000000 [........H..s....]
073039750 73039748 00000000 00000000 00000000 [H..s............]
073039760 00000002 00000041 704AFEA0 00000000 [....A.....Jp....]
073039770 00000000 00000001 00000000 00000000 [................]
073039780 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
0730397A0 71B9AC40 00000000 00000000 00000000 [@..q............]
0730397B0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
0730397D0 02000000 00000000 20DD3559 DF70F8AD [........Y5. ..p.]
0730397E0 20DD3559 00000000 20DD3559 DF70F8AD [Y5. ....Y5. ..p.]
0730397F0 20DD3559 00000007 00000000 00000000 [Y5. ............]
073039800 00000000 00000000 00000000 00000000 [................]
073039810 00000000 00000000 00000031 C0B38F00 [........1.......]
073039820 730394B0 00000000 73039B70 00000000 [...s....p..s....]
073039830 704B0270 00000000 00000000 00000000 [p.Kp............]
073039840 00000000 00000000 00000099 10B38F00 [................]
073039850 730394B0 00000000 05AF8EB0 00000000 [...s............]
073039860 730394D0 00000000 00000000 00000000 [...s............]
073039870 00000000 00000000 00000000 00000000 [................]
Repeat 6 times
Chunk 0730398e0 sz= 1112 freeable "CCursor " ds=0x72b7c3d8
Dump of memory from 0x00000000730398E0 to 0x0000000073039D38
0730398E0 00000459 00B38F00 73039488 00000000 [Y..........s....]
0730398F0 60037D80 00000000 72B7C3D8 00000000 [.}.`.......r....]
073039900 77059D48 00000000 00000431 50B38F00 [H..w....1......P]
073039910 00000000 00000000 77059D58 00000000 [........X..w....]
073039920 00000410 00000000 73039B10 00000000 [...........s....]
073039930 73039B10 00000000 00010001 00000000 [...s............]
073039940 73039940 00000000 73039940 00000000 [@..s....@..s....]
073039950 00000000 00000000 73039958 00000000 [........X..s....]
073039960 73039958 00000000 00000000 00000000 [X..s............]
073039970 73039970 00000000 73039970 00000000 [p..s....p..s....]
073039980 00000000 00000000 73039988 00000000 [...........s....]
073039990 73039988 00000000 00000000 00000000 [...s............]
0730399A0 730399A0 00000000 730399A0 00000000 [...s.......s....]
0730399B0 00000000 00000000 730399B8 00000000 [...........s....]
0730399C0 730399B8 00000000 00000000 00000000 [...s............]
0730399D0 730399D0 00000000 730399D0 00000000 [...s.......s....]
0730399E0 00000000 00000000 730399E8 00000000 [...........s....]
0730399F0 730399E8 00000000 00000000 00000000 [...s............]
073039A00 73039A00 00000000 73039A00 00000000 [...s.......s....]
073039A10 00000000 00000000 73039A18 00000000 [...........s....]
073039A20 73039A18 00000000 00000000 00000000 [...s............]
073039A30 73039A30 00000000 73039A30 00000000 [0..s....0..s....]
073039A40 00000000 00000000 73039A48 00000000 [........H..s....]
073039A50 73039A48 00000000 00000000 00000000 [H..s............]
073039A60 73039A60 00000000 73039A60 00000000 [`..s....`..s....]
073039A70 00000000 00000000 73039A78 00000000 [........x..s....]
073039A80 73039A78 00000000 00000000 00000000 [x..s............]
073039A90 73039A90 00000000 73039A90 00000000 [...s.......s....]
073039AA0 00000000 00000000 73039AA8 00000000 [...........s....]
073039AB0 73039AA8 00000000 00020002 00000000 [...s............]
073039AC0 00000000 00000000 0000000E 00000000 [................]
073039AD0 3650F131 00000000 00000000 00000000 [1.P6............]
073039AE0 00000018 00000000 00000000 00000000 [................]
073039AF0 00000000 00000000 00000000 00000000 [................]
073039B00 7705A168 00000000 00000000 00000000 [h..w............]
073039B10 73039928 00000000 73039928 00000000 [(..s....(..s....]
073039B20 00000000 00000000 73039B40 00000000 [........@..s....]
073039B30 77059E90 00000000 72B96820 00000000 [...w.... h.r....]
073039B40 73039BB8 00000000 00000000 00000000 [...s............]
073039B50 6FCE3B48 00000000 00000180 00000000 [H;.o............]
073039B60 00000000 00000000 00001F98 00001CF8 [................]
073039B70 73039090 00000000 6FCE2E58 00000000 [...s....X..o....]
073039B80 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
073039BA0 73039BA0 00000000 73039BA0 00000000 [...s.......s....]
073039BB0 00000000 00000000 60000058 00000000 [........X..`....]
073039BC0 00000FE8 00000000 73039AC0 00000000 [...........s....]
073039BD0 6FD16F58 00000000 00000000 00000000 [Xo.o............]
073039BE0 00000000 00000000 00000000 00000000 [................]
073039BF0 020A0200 00000000 00000000 00000000 [................]
073039C00 00000FE8 206C7173 61657261 00000000 [....sql area....]
073039C10 07000000 01387FFF 7FFF7FFF 00000000 [......8.........]
073039C20 00000401 00000000 00000098 00000000 [................]
073039C30 73039C30 00000000 73039C30 00000000 [0..s....0..s....]
073039C40 00000118 00000000 73039C48 00000000 [........H..s....]
073039C50 73039C48 00000000 00000218 00000000 [H..s............]
073039C60 6FD16F78 00000000 6FD16F78 00000000 [xo.o....xo.o....]
073039C70 00000418 00000000 73039C78 00000000 [........x..s....]
073039C80 73039C78 00000000 00000818 00000000 [x..s............]
073039C90 73039C90 00000000 73039C90 00000000 [...s.......s....]
073039CA0 00001018 00000000 73039CA8 00000000 [...........s....]
073039CB0 73039CA8 00000000 00001030 00000000 [...s....0.......]
073039CC0 73039CC0 00000000 73039CC0 00000000 [...s.......s....]
073039CD0 00001048 00000000 73039CD8 00000000 [H..........s....]
073039CE0 73039CD8 00000000 00001060 00000000 [...s....`.......]
073039CF0 73039CF0 00000000 73039CF0 00000000 [...s.......s....]
073039D00 00001078 00000000 73039D08 00000000 [x..........s....]
073039D10 73039D08 00000000 54DE73EC 00000008 [...s.....s.T....]
073039D20 00000007 000000D4 0000063F 00000000 [........?.......]
073039D30 00000001 00000000 [........]
4.SQL的chunk:父游标堆0的DS:
--再来看看父游标堆0描述符(DS)地址 0000000072B7C3D8
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j
TEXT KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ------------ ------------ ------------ --------------------------
子游标句柄地址 0000000072B96820 0000000076390710 select * from dept where deptno=10 0000000076115D70 0000000073039BB8 3664 8088 1803 13555
父游标句柄地址 0000000076390710 0000000076390710 select * from dept where deptno=10 0000000072B7C3D8 00 2812 0 0 2812
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000072B7C3D8', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ;
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ------------ ------------ ------------ ------------ ---------------- ---------------- ------------ -------- ------------ ----------------
00007F58BFACE6B0 62246 1 1 2 library cache 0000000072B7C3C0 168 freeabl 0 00
--父游标堆0的DS. chunk是freeable类型.大小168.
Chunk 072b7c3c0 sz= 168 freeable "library cache "
Dump of memory from 0x0000000072B7C3C0 to 0x0000000072B7C468
072B7C3C0 000000A9 00B38F00 72B7C1B0 00000000 [...........r....]
072B7C3D0 7C3DC7C0 00000000 60000058 00000000 [..=|....X..`....]
072B7C3E0 00000440 00000000 77290EF8 00000000 [@.........)w....]
072B7C3F0 730394A0 00000000 730394B0 00000000 [...s.......s....]
072B7C400 00000000 00000000 00000000 00000000 [................]
072B7C410 00010209 00000000 00000000 00000000 [................]
072B7C420 00000440 72754343 00726F73 00000000 [@...CCursor.....]
072B7C430 02000000 00207FFF 7FFF7FFF 00000000 [...... .........]
072B7C440 00000301 00000000 00000000 00000000 [................]
072B7C450 72B7C450 00000000 72B7C450 00000000 [P..r....P..r....]
072B7C460 60000058 00000000 [X..`....]
5.SQL的chunk:子游标句柄:
SYS@test> @&r/sharepool/shp4 4xamnunv51w9j
old 13: WHERE kglobt03 = '&1'
new 13: WHERE kglobt03 = '4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ------------ ------------ ------------ --------------------------
子游标句柄地址 0000000072B96820 0000000076390710 select * from dept where deptno=10 0000000076115D70 0000000073039BB8 3664 8088 1803 13555
父游标句柄地址 0000000076390710 0000000076390710 select * from dept where deptno=10 0000000072B7C3D8 00 2812 0 0 2812
--子游标句柄地址 KGLHDADR=0000000072B96820
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000072B96820', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ------------ ------------ ------------ ------------ ---------------- ---------------- ------------ -------- ------------ ----------------
00007F58BFADA390 61932 1 1 2 KGL handles 0000000072B967F0 384 recr 48 00
--偏移0x30(48字节),猜测还是正确的。
Chunk 072b967f0 sz= 384 recreate "KGL handles " latch=0x79547ae0
Dump of memory from 0x0000000072B967F0 to 0x0000000072B96970
072B967F0 00000181 80B38F00 72B96420 00000000 [........ d.r....]
072B96800 00000000 00000000 72B962B0 00000000 [.........b.r....]
072B96810 79547AE0 00000000 00000000 00080030 [.zTy........0...]
072B96820 70B9BD58 00000000 79560688 00000000 [X..p......Vy....]
072B96830 00000000 00000000 00000000 00000000 [................]
Repeat 4 times
072B96880 72B96880 00000000 72B96880 00000000 [.h.r.....h.r....]
072B96890 72B96890 00000000 72B96890 00000000 [.h.r.....h.r....]
072B968A0 72B968A0 00000000 72B968A0 00000000 [.h.r.....h.r....]
072B968B0 0001FFFE 00000000 77F1D770 00000000 [........p..w....]
072B968C0 77F1D770 00000000 72B968C8 00000000 [p..w.....h.r....]
072B968D0 72B968C8 00000000 72B968D8 00000000 [.h.r.....h.r....]
072B968E0 72B968D8 00000000 0000FFFE 00000001 [.h.r............]
072B968F0 00000004 10010100 730394D0 00000000 [...........s....]
072B96900 730394D0 00000000 00000000 00000000 [...s............]
072B96910 72B96910 00000000 72B96910 00000000 [.i.r.....i.r....]
072B96920 72B96920 00000000 72B96920 00000000 [ i.r.... i.r....]
072B96930 73039090 00000000 00000001 00000001 [...s............]
072B96940 00000000 00000001 00000000 00000007 [................]
072B96950 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
--另外可以发现子游标句柄的chunk类型是recr.大小384字节.
6.SQL的chunk:子游标堆0:
SYS@test> @&r/sharepool/shp4 4xamnunv51w9j
old 13: WHERE kglobt03 = '&1'
new 13: WHERE kglobt03 = '4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ------------ ------------ ------------ --------------------------
子游标句柄地址 0000000072B96820 0000000076390710 select * from dept where deptno=10 0000000076115D70 0000000073039BB8 3664 8088 1803 13555
父游标句柄地址 0000000076390710 0000000076390710 select * from dept where deptno=10 0000000072B7C3D8 00 2812 0 0 2812
--注意子游标句柄地址那行:
KGLOBHD0=0000000076115D70,就是子游标堆0描述符(DS)地址.
KGLOBHD6=0000000073039BB8,就是子游标堆6描述符(DS)地址.而且这个地址在0730398E0-0730398E0+1112(10进制)之间.在父游标句柄堆0里面.
--
SYS@test> select a.* from x$ksmsp a where a.ksmchpar='0000000076115D70';
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ------------ ------------ ------------ ------------ ---------------- ---------------- ------------ -------- ------------ ----------------
00007F58BF3DB3A8 25521 1 1 3 CCursor 0000000077068EF8 1112 freeabl 0 0000000076115D70
00007F58BF2E7558 35681 1 1 3 CCursor 0000000073039030 1112 recr 4095 0000000076115D70
00007F58BF2E7500 35682 1 1 3 CCursor 0000000073038BD8 1112 freeabl 0 0000000076115D70
--可以发现子游标堆0的chunk有3个，1个是recr类型，两个freeabl类型，大小1112.
Chunk 073039030 sz= 1112 recreate "CCursor " latch=0x79547ae0
Dump of memory from 0x0000000073039030 to 0x0000000073039488
073039030 00000459 80B38F00 73038BD8 00000000 [Y..........s....]
073039040 77069360 00000000 7708D668 00000000 [`..w....h..w....]
073039050 79547AE0 00000000 00000001 400E0FFF [.zTy...........@]
073039060 76115D70 00000000 00000000 00000000 [p].v............]
073039070 000003E1 40B38F00 00000000 00000000 [.......@........]
073039080 00000000 00000000 000003E0 00000000 [................]
073039090 72B96820 00000000 73039098 00000000 [ h.r.......s....]
0730390A0 73039098 00000000 730390A8 00000000 [...s.......s....]
0730390B0 730390A8 00000000 77068F40 00000000 [...s....@..w....]
0730390C0 00000000 00010001 73039168 00000000 [........h..s....]
0730390D0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
0730390F0 00000000 00000000 73039B40 00000000 [........@..s....]
073039100 00000000 00000000 00000000 00000000 [................]
Repeat 5 times
073039160 00000000 00000000 76115D70 00000000 [........p].v....]
073039170 00000000 00000000 730391A8 00000000 [...........s....]
073039180 00000000 00000000 00000000 00000000 [................]
073039190 00000C78 00000BA0 00000000 00000000 [x...............]
0730391A0 73039060 00000000 0000001A 00000039 [`..s........9...]
0730391B0 10008110 00000000 00000000 00000000 [................]
0730391C0 00000000 00000000 00000000 00000000 [................]
0730391D0 00000000 00000000 00000000 00000006 [................]
0730391E0 00000998 000009B0 00000003 00000013 [................]
0730391F0 00000013 544F4353 00000054 00000000 [....SCOTT.......]
073039200 00000000 00000000 00000000 00000000 [................]
073039210 00050000 00000000 73038E58 00000000 [........X..s....]
073039220 00000000 00000000 00000039 544F4353 [........9...SCOT]
073039230 00000054 00000000 00000000 00000000 [T...............]
073039240 00000000 00000000 00050000 00000000 [................]
073039250 71B95F18 00000000 A9FE3E95 00000000 [._.q.....>......]
073039260 00000000 00000000 00000000 00000000 [................]
073039270 0F027378 00071909 00000000 00000000 [xs..............]
073039280 00000000 00000000 00000000 00000000 [................]
Repeat 2 times
0730392B0 00000000 00000000 0000FFFF FFFFFFFF [................]
0730392C0 00000000 00000000 0F027378 00071909 [........xs......]
0730392D0 00000000 00000000 00000000 00000000 [................]
0730392E0 00200080 00000000 770690D0 00000000 [.. ........w....]
0730392F0 00000000 00000000 08002800 00000000 [.........(......]
073039300 00000000 00000000 00000000 2F7B1096 [..............{/]
073039310 73038C20 00000000 00000000 00000000 [ ..s............]
073039320 76115D70 00000000 00000000 00000000 [p].v............]
073039330 73039AC0 00000000 00000FA0 00000000 [...s............]
073039340 73039378 00000000 00000000 00000000 [x..s............]
073039350 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
073039370 00000000 00000000 00000001 00000000 [................]
073039380 00000000 00000000 00000000 00000000 [................]
073039390 00000001 00000000 00000000 00000000 [................]
0730393A0 02001018 00025800 00032000 00000440 [.....X... ..@...]
0730393B0 00000000 00000000 0000005A 0098E4A4 [........Z.......]
0730393C0 00000005 00020000 00100000 00010000 [................]
0730393D0 00000000 00000000 00002000 01000002 [......... ......]
0730393E0 000007D0 00380101 000000F8 00000099 [......8.........]
0730393F0 00007800 00012C00 00000000 0098E4A4 [.x...,..........]
073039400 EFA664E5 00032393 07FFF100 2F3FAC00 [.d...#........?/]
073039410 F2501D67 00B275B2 00020000 0001817D [g.P..u......}...]
073039420 781B0000 001FE06D 00000000 0000000B [...xm...........]
073039430 0000000A 000007DE 00000000 000C2940 [............@)..]
073039440 00000000 00007C0A 00000000 00000000 [.....|..........]
073039450 00000039 D0B38F00 73039070 00000000 [9.......p..s....]
073039460 73038EB8 00000000 76115DE8 00000000 [...s.....].v....]
073039470 73039378 00000000 0562ADDC 00000000 [x..s......b.....]
073039480 00000000 00000000 [........]
Chunk 077068ef8 sz= 1112 freeable "CCursor " ds=0x76115d70
Dump of memory from 0x0000000077068EF8 to 0x0000000077069350
077068EF0 00000459 00B38F00 [Y.......]
077068F00 77068AA0 00000000 60037D80 00000000 [...w.....}.`....]
077068F10 76115D70 00000000 73038BF0 00000000 [p].v.......s....]
077068F20 00000269 40B38F00 00000000 00000000 [i......@........]
077068F30 73038C00 00000000 00000220 00000000 [...s.... .......]
077068F40 77068FF0 00000000 00000010 00000001 [...w............]
077068F50 00000004 00000000 00000000 00000000 [................]
077068F60 00000000 00000000 00000000 00000000 [................]
077068F70 77069110 00000000 00000010 00000001 [...w............]
077068F80 00100004 00000010 77069098 00000000 [...........w....]
077068F90 00000010 00000001 00000004 00000000 [................]
077068FA0 77069068 00000000 00000010 00000001 [h..w............]
077068FB0 00000004 00000000 00000000 00000000 [................]
077068FC0 00000000 00000000 00000000 00000000 [................]
Repeat 2 times
077068FF0 770692D0 00000000 00000000 00000000 [...w............]
077069000 00000000 00000000 00000000 00000000 [................]
077069010 763553B0 00000000 763553B0 00000000 [.S5v.....S5v....]
077069020 7BD1EAC0 00000000 00000004 00000001 [...{............]
077069030 73039090 00000000 0000000E 00000001 [...s............]
077069040 00000000 00000000 00000000 00000000 [................]
077069050 00000000 00000000 00000000 036C7800 [.............xl.]
077069060 2213020C 00000000 77069238 00000000 [..."....8..w....]
077069070 00000000 00000000 00000000 00000000 [................]
077069080 00000000 00000000 7BD1EAC0 00000000 [...........{....]
077069090 7BD1EAC0 00000000 770691A0 00000000 [...{.......w....]
0770690A0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
0770690C0 00000000 00000000 02000000 00000000 [................]
0770690D0 36775402 1CCA5BC3 36775402 00000000 [.Tw6.[...Tw6....]
0770690E0 4D2C487B DD48976B 4D2C487B 00000007 [{H,Mk.H.{H,M....]
0770690F0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
077069110 73038FB0 00000000 00000000 00000000 [...s............]
077069120 00000000 00000000 00000000 00000000 [................]
077069130 00000000 00000039 00000200 00000000 [....9...........]
077069140 00000000 00000000 00000000 00000000 [................]
077069150 77069150 00000000 77069150 00000000 [P..w....P..w....]
077069160 00000000 00000000 00000001 00000041 [............A...]
077069170 7575B6F8 00000000 00000000 00000001 [..uu............]
077069180 00000000 00000000 00000099 00B38F00 [................]
077069190 77068F20 00000000 05AF8EB0 00000000 [ ..w............]
0770691A0 770690B8 00000000 00000000 00000000 [...w............]
0770691B0 00000000 00000000 00000000 00000000 [................]
Repeat 6 times
077069220 00000099 00B38F00 77069188 00000000 [...........w....]
077069230 05AF8EB0 00000000 77069088 00000000 [...........w....]
077069240 00000000 00000000 00000000 00000000 [................]
Repeat 6 times
0770692B0 00000000 00000000 00000099 10B38F00 [................]
0770692C0 77069220 00000000 05AF8EB0 00000000 [ ..w............]
0770692D0 77069010 00000000 00000000 00000000 [...w............]
0770692E0 00000000 00000000 00000000 00000000 [................]
Repeat 6 times
Chunk 073038bd8 sz= 1112 freeable "CCursor " ds=0x76115d70
Dump of memory from 0x0000000073038BD8 to 0x0000000073039030
073038BD0 00000459 00B38F00 [Y.......]
073038BE0 73038780 00000000 60037D80 00000000 [...s.....}.`....]
073038BF0 76115D70 00000000 73039060 00000000 [p].v....`..s....]
073038C00 000002A9 40B38F00 00000000 00000000 [.......@........]
073038C10 73039070 00000000 000002A8 00000000 [p..s............]
073038C20 73038E08 00000000 73038E08 00000000 [...s.......s....]
073038C30 00010001 00000000 73038C38 00000000 [........8..s....]
073038C40 73038C38 00000000 00000000 00000000 [8..s............]
073038C50 73038C50 00000000 73038C50 00000000 [P..s....P..s....]
073038C60 00000000 00000000 73038C68 00000000 [........h..s....]
073038C70 73038C68 00000000 00000000 00000000 [h..s............]
073038C80 73038C80 00000000 73038C80 00000000 [...s.......s....]
073038C90 00000000 00000000 73038C98 00000000 [...........s....]
073038CA0 73038C98 00000000 00000000 00000000 [...s............]
073038CB0 73038CB0 00000000 73038CB0 00000000 [...s.......s....]
073038CC0 00000000 00000000 73038CC8 00000000 [...........s....]
073038CD0 73038CC8 00000000 00000000 00000000 [...s............]
073038CE0 73038CE0 00000000 73038CE0 00000000 [...s.......s....]
073038CF0 00000000 00000000 73038CF8 00000000 [...........s....]
073038D00 73038CF8 00000000 00000000 00000000 [...s............]
073038D10 73038D10 00000000 73038D10 00000000 [...s.......s....]
073038D20 00000000 00000000 73038D28 00000000 [........(..s....]
073038D30 73038D28 00000000 00000000 00000000 [(..s............]
073038D40 73038D40 00000000 73038D40 00000000 [@..s....@..s....]
073038D50 00000000 00000000 73038D58 00000000 [........X..s....]
073038D60 73038D58 00000000 00000000 00000000 [X..s............]
073038D70 73038D70 00000000 73038D70 00000000 [p..s....p..s....]
073038D80 00000000 00000000 73038D88 00000000 [...........s....]
073038D90 73038D88 00000000 00000000 00000000 [...s............]
073038DA0 73038DA0 00000000 73038DA0 00000000 [...s.......s....]
073038DB0 00020002 00000000 00000000 00000000 [................]
073038DC0 00000001 00000000 B2AC7917 00000000 [.........y......]
073038DD0 00000000 00000000 00000019 00000000 [................]
073038DE0 00000000 00000000 00000000 00000000 [................]
073038DF0 00000000 00000000 73039480 00000000 [...........s....]
073038E00 00000000 00000000 73038C20 00000000 [........ ..s....]
073038E10 73038C20 00000000 00000000 00000000 [ ..s............]
073038E20 73038E38 00000000 730391A8 00000000 [8..s.......s....]
073038E30 729298F8 00000000 73038EB0 00000000 [...r.......s....]
073038E40 00000000 00000000 00000000 00000000 [................]
073038E50 00000080 00000000 00000001 00000039 [............9...]
073038E60 00000002 00000003 00000004 00000006 [................]
073038E70 00000014 00000007 00000008 00000009 [................]
073038E80 0000000A 0000000D 00000012 0000001B [................]
073038E90 00000021 00000022 00000029 0000002B [!..."...)...+...]
073038EA0 00000032 00000000 000000F1 C0B38F00 [2...............]
073038EB0 73038C00 00000000 76115DE8 00000000 [...s.....].v....]
073038EC0 73039460 00000000 00000000 00000000 [`..s............]
073038ED0 00000161 D0B38F00 73038EA8 00000000 [a..........s....]
073038EE0 0562A168 00000000 00000000 00000000 [h.b.............]
073038EF0 00000000 00000000 00000000 00000000 [................]
073038F00 00000000 00000000 00000000 00002C10 [.............,..]
073038F10 00000C78 00001F98 00000000 00000000 [x...............]
073038F20 00000000 00000000 00000000 00000000 [................]
Repeat 6 times
073038F90 00000000 00000000 00000099 10B38F00 [................]
073038FA0 73038EA8 00000000 05AF8EB0 00000000 [...s............]
073038FB0 77069130 00000000 00000000 00000000 [0..w............]
073038FC0 00000000 00000000 00000000 00000000 [................]
Repeat 6 times
7.SQL的chunk:子游标堆0的DS:
SYS@test> @&r/sharepool/shp4 4xamnunv51w9j
old 13: WHERE kglobt03 = '&1'
new 13: WHERE kglobt03 = '4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ------------ ------------ ------------ --------------------------
子游标句柄地址 0000000072B96820 0000000076390710 select * from dept where deptno=10 0000000076115D70 0000000073039BB8 3664 8088 1803 13555
父游标句柄地址 0000000076390710 0000000076390710 select * from dept where deptno=10 0000000072B7C3D8 00 2812 0 0 2812
--注意子游标句柄地址 KGLOBHD0＝0000000076115D70
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000076115D70', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ;
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ------------ ------------ ------------ ------------ ---------------- ---------------- ------------ -------- ------------ ----------------
00007F58BF764B98 52009 1 1 2 library cache 0000000076115D58 168 freeabl 0 00
--可以发现子游标堆0的堆描述符在KSMCHPTR=0000000076115D58的chunk。
Chunk 076115d58 sz= 168 freeable "library cache "
Dump of memory from 0x0000000076115D58 to 0x0000000076115E00
076115D50 000000A9 00B38F00 [........]
076115D60 76115B48 00000000 7C3DC7C0 00000000 [H[.v......=|....]
076115D70 60000058 00000000 00000440 00000000 [X..`....@.......]
076115D80 73039090 00000000 77068F10 00000000 [...s.......w....]
076115D90 77068F20 00000000 00000000 00000000 [ ..w............]
076115DA0 00000000 00000000 00010209 00000000 [................]
076115DB0 00000000 00000000 00000440 72754343 [........@...CCur]
076115DC0 00726F73 00000000 02000000 00207FFF [sor........... .]
076115DD0 7FFF7FFF 00000000 00000301 00000000 [................]
076115DE0 00000000 00000000 73039460 00000000 [........`..s....]
076115DF0 73038EB8 00000000 60000058 00000000 [...s....X..`....]
--子游标堆0的DS. chunk是freeable类型.大小168.
8.SQL的chunk:子游标堆6:
SYS@test> @&r/sharepool/shp4 4xamnunv51w9j
old 13: WHERE kglobt03 = '&1'
new 13: WHERE kglobt03 = '4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ------------ ------------ ------------ --------------------------
子游标句柄地址 0000000072B96820 0000000076390710 select * from dept where deptno=10 0000000076115D70 0000000073039BB8 3664 8088 1803 13555
父游标句柄地址 0000000076390710 0000000076390710 select * from dept where deptno=10 0000000072B7C3D8 00 2812 0 0 2812
--注意子游标句柄地址 堆6的DS地址：KGLOBHD6=0000000073039BB8
select a.* from x$ksmsp a where a.ksmchpar='0000000073039BB8'
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ------------ ------------ ------------ ------------ ---------------- ---------------- ------------ -------- ------------ ----------------
00007F58BF433FE8 20993 1 1 4 sql area 000000006FD16F40 4096 freeabl 0 0000000073039BB8
00007F58BF437C38 21096 1 1 4 sql area 000000006FCE2E28 4096 recr 4095 0000000073039BB8
--可以发现子游标堆6的chunk有2个，1个是recr类型，1个freeabl类型，大小4096.
Chunk 06fce2e28 sz= 4096 recreate "sql area " latch=(nil)
ds 073039bb8 sz= 8192 ct= 2
06fd16f40 sz= 4096
Dump of memory from 0x000000006FCE2E28 to 0x000000006FCE3E28
06FCE2E20 00001001 80B38F00 [........]
06FCE2E30 6FCE1E28 00000000 7638FD18 00000000 [(..o......8v....]
06FCE2E40 75E6B520 00000000 00000000 00000000 [ ..u............]
06FCE2E50 00000001 D0040FFF 73039BB8 00000000 [...........s....]
06FCE2E60 00000000 00000000 00000049 00B38F00 [........I.......]
06FCE2E70 00000000 00000000 055BF3E4 00000000 [..........[.....]
06FCE2E80 00000002 00000000 00000004 0100000B [................]
06FCE2E90 0000C96A 0000C96A 00030008 00001FE8 [j...j...........]
06FCE2EA0 00000000 00FF0001 12FA280A 00020327 [.........(..'...]
06FCE2EB0 00000049 00B38F00 6FCE2E68 00000000 [I.......h..o....]
06FCE2EC0 055BF390 00000000 00000002 00000000 [..[.............]
06FCE2ED0 00000004 0100000B 0000C96A 0000C96A [........j...j...]
06FCE2EE0 00030008 00001FE8 00000000 00FF0001 [................]
...
Chunk 06fd16f40 sz= 4096 freeable "sql area " ds=0x73039bb8
Dump of memory from 0x000000006FD16F40 to 0x000000006FD17F40
06FD16F40 00001001 00B38F00 6FD15F40 00000000 [........@_.o....]
06FD16F50 600395D8 00000000 73039BB8 00000000 [...`.......s....]
06FD16F60 6FCE2E58 00000000 000002A1 C0B38F00 [X..o............]
06FD16F70 00000000 00000000 73039C60 00000000 [........`..s....]
06FD16F80 73039C60 00000000 6F8591C8 00000000 [`..s.......o....]
06FD16F90 00000051 C0B38F00 00000000 00000000 [Q...............]
06FD16FA0 6FD03EA0 00000000 6FD13F30 00000000 [.>.o....0?.o....]
06FD16FB0 75AE0E20 00000000 00000081 00B38F00 [ ..u............]
06FD16FC0 6FD16F90 00000000 0555C97C 00000000 [.o.o....|.U.....]
06FD16FD0 00000000 00000000 00000002 00000000 [................]
06FD16FE0 00000091 00B38F00 6FD16F90 00000000 [.........o.o....]
06FD16FF0 0556BA68 00000000 00000201 00000000 [h.V.............]
06FD17000 00000000 00000000 00008100 00000000 [................]
06FD17010 00030041 00000000 00000016 00000000 [A...............]
06FD17020 00000000 00000000 6F85BCE8 00000000 [...........o....]
06FD17030 04000008 00000000 6F862B98 00000000 [.........+.o....]
06FD17040 00000000 00000000 00000000 00000000 [................]
06FD17050 00000000 00000000 6F8742B8 00000000 [.........B.o....]
06FD17060 6F86AA00 00000000 00000000 00000000 [...o............]
06FD17070 00000029 00B38F00 6FD16FE0 00000000 [)........o.o....]
06FD17080 05AFC338 00000000 00000000 00000000 [8...............]
06FD17090 6F8742B8 00000000 00000029 00B38F00 [.B.o....).......]
06FD170A0 6FD17070 00000000 05AFC338 00000000 [pp.o....8.......]
06FD170B0 00000000 00000000 6F8742B8 00000000 [.........B.o....]
06FD170C0 00000029 00B38F00 6FD17098 00000000 [)........p.o....]
06FD170D0 05AFC338 00000000 00000000 00000000 [8...............]
06FD170E0 6F8742B8 00000000 000000D9 00B38F00 [.B.o............]
06FD170F0 6FD170C0 00000000 05AE74E8 00000000 [.p.o.....t......]
06FD17100 00000000 00000000 6FD171C0 00000000 [.........q.o....]
06FD17110 6FD170E0 00000000 0555C798 00000000 [.p.o......U.....]
06FD17120 00000000 00000000 00000000 00000000 [................]
Repeat 2 times
06FD17150 00000029 00B38F00 6FD17108 00000000 [)........q.o....]
06FD17160 05AFC338 00000000 00000000 00000000 [8...............]
06FD17170 6FD17238 00000000 00000029 00B38F00 [8r.o....).......]
06FD17180 6FD17150 00000000 0555CB18 00000000 [Pq.o......U.....]
06FD17190 75AE12A8 00000000 75AE12A8 00000000 [...u.......u....]
06FD171A0 00000081 00B38F00 6FD17178 00000000 [........xq.o....]
06FD171B0 0555C97C 00000000 00000000 00000000 [|.U.............]
06FD171C0 00000071 00B38F00 6FD170E8 00000000 [q........p.o....]
06FD171D0 05AE75AC 00000000 00000000 00000000 [.u..............]
06FD171E0 00180018 00000000 6F87BA30 00000000 [........0..o....]
06FD171F0 00000000 00000000 6FD17110 00000000 [.........q.o....]
06FD17200 6FD171C0 00000000 00000039 00B38F00 [.q.o....9.......]
06FD17210 6FD16F68 00000000 05AE7F8C 00000000 [ho.o............]
06FD17220 5F4B5007 54504544 50454406 054F4E54 [.PK_DEPT.DEPTNO.]
06FD17230 244C4553 45440431 53055450 54544F43 [SEL$1.DEPT.SCOTT]
06FD17240 00000029 00B38F00 6FD17208 00000000 [)........r.o....]
06FD17250 05AE7F5C 00000000 0015001A 0008000F [\...............]
06FD17260 00000000 00000000 00000021 00B38F00 [........!.......]
....
--可以发现执行计划在堆6中,类型freeabl。
9.SQL的chunk:子游标堆6的DS:
SYS@test> @&r/sharepool/shp4 4xamnunv51w9j
old 13: WHERE kglobt03 = '&1'
new 13: WHERE kglobt03 = '4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ------------ ------------ ------------ --------------------------
子游标句柄地址 0000000072B96820 0000000076390710 select * from dept where deptno=10 0000000076115D70 0000000073039BB8 3664 8088 1803 13555
父游标句柄地址 0000000076390710 0000000076390710 select * from dept where deptno=10 0000000072B7C3D8 00 2812 0 0 2812
--注意子游标句柄地址 堆6的DS地址：KGLOBHD6=0000000073039BB8
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000073039BB8', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ;
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ------------ ------------ ------------ ------------ ---------------- ---------------- ------------ -------- ------------ ----------------
00007F58BF2E73F8 35679 1 1 3 CCursor 00000000730398E0 1112 freeabl 0 0000000072B7C3D8
--注意父游标句柄地址那行:KGLOBHD0 = 0000000072B7C3D8 就是父游标堆0描述符(DS)地址.
SYS@test> select a.* from x$ksmsp a where a.ksmchpar='0000000072B7C3D8';
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ------------ ------------ ------------ ------------ ---------------- ---------------- ------------ -------- ------------ ----------------
00007F58BF3DDCC8 25612 1 1 3 CCursor 0000000077059D18 1112 recr 4095 0000000072B7C3D8
00007F58BF2E8308 35759 1 1 3 CCursor 00000000730398E0 1112 freeabl 0 0000000072B7C3D8
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
00007F58BF2E82B0 35760 1 1 3 CCursor 0000000073039488 1112 freeabl 0 0000000072B7C3D8
--可以发现子游标堆6描述符(DS)地址在 父游标的堆0中。注意～的行。
10.做一个总结画图不是很方便，列出来：
父游标句柄的chunk类型是recr.大小528字节.
父游标堆0的chunk有3个，1个是recr类型,2个freeabl类型，大小1112.
父游标堆0的DS. chunk是freeable类型.大小168.
--父游标 需要5个chunk，2个类型recr，3个类型freeabl。
子游标句柄的chunk类型是recr.大小384字节.
子游标堆0的chunk有3个，1个是recr类型，2个freeabl类型，大小1112.
子游标堆0的DS. chunk是freeable类型.大小168.
子游标堆6的chunk有2个，1个是recr类型，1个freeabl类型，大小4096.
子游标堆6的DS 在父游标的堆0中(不做计算)。类型freeabl。
--子游标 需要6个chunk，3个类型recr，3个类型freeabl。
--说明一下测试与vage存在许多不同，也许是oracle版本问题，我使用的是10g。我在11g也做了测试，确实与他的一致。
11。占用共享池的大小：
SYS@test> select SHARABLE_MEM from v$sql where sql_id='4xamnunv51w9j';
SHARABLE_MEM
------------
13555
SYS@test> select SHARABLE_MEM from v$sqlarea where sql_id='4xamnunv51w9j';
SHARABLE_MEM
------------
13555
SYS@test> @&r/sharepool/shp4 4xamnunv51w9j
old 13: WHERE kglobt03 = '&1'
new 13: WHERE kglobt03 = '4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ------------ ------------ ------------ --------------------------
子游标句柄地址 0000000072B96820 0000000076390710 select * from dept where deptno=10 0000000076115D70 0000000073039BB8 3664 8088 1803 13555
父游标句柄地址 0000000076390710 0000000076390710 select * from dept where deptno=10 0000000072B7C3D8 00 2812 0 0 2812
--对比可以发现与查询x$kglob的子游标句柄地址那行KGLOBHS0+KGLOBHS6+KGLOBT16一致。也就是v$sql视图占用SHARABLE_MEM内存不计算父游标。
--按照上面的计算 子游标 384+1112*3+2*4096=11912.
SYS@test> column view_definition format a160
SYS@test> select * from V$FIXED_VIEW_DEFINITION where view_name='GV$SQL';
VIEW_NAME VIEW_DEFINITION
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
GV$SQL select inst_id,kglnaobj,kglfnobj,kglobt03, kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16, kglobt08+kglobt11, kglobt10, kglobt01, decod
e(kglobhs6,0,0,1), decode(kglhdlmd,0,0,1), kglhdlkc, kglobt04, kglobt05, kglobt48, kglobt35, kglobpc6, kglhdldc, substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'
),1,19), kglhdivc, kglobt12, kglobt13, kglobwdw, kglobt14, kglobwap, kglobwcc, kglobwcl, kglobwui, kglobt42, kglobt43, kglobt15, kglobt02, decode(kglobt32,
0, 'NONE', 1, 'ALL_ROWS', 2, 'FIRST_ROWS', 3, 'RULE', 4, 'CHOOSE', 'UNKNOWN'), kglobtn0, kglobcce, kglobcceh, kglobt17,
kglobt18, kglobts4, kglhdkmk, kglhdpar, kglobtp0, kglnahsh, kglobt46, kglobt30, kglobt09, kglobts5, kglobt48, kglobts0, kglobt19, kglobts1, kglobt20, kglobt21,
kglobts2, kglobt06, kglobt07, decode(kglobt28, 0, to_number(NULL), kglobt28), kglhdadr, kglobt29, decode(bitand(kglobt00,64),64, 'Y', 'N'), decode(kglobsta,
1, 'VALID', 2, 'VALID_AUTH_ERROR', 3, 'VALID_COMPILE_ERROR', 4, 'VALID_UNAUTH', 5, 'INVALID_UNAUTH', 6, 'INVALID'), kglo
bt31, substr(to_char(kglobtt0,'YYYY-MM-DD/HH24:MI:SS'),1,19), decode(kglobt33, 1, 'Y', 'N'), kglhdclt, kglobts3, kglobt44, kglobt45, kglobt47, kglobt49, kglobc
la, kglobcbca, kglobt22 from x$kglcursor_child
SYS@test> select * from V$FIXED_VIEW_DEFINITION where view_name='V$SQL';
VIEW_NAME VIEW_DEFINITION
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
V$SQL select SQL_TEXT , SQL_FULLTEXT , SQL_ID, SHARABLE_MEM , PERSISTENT_MEM , RUNTIME_MEM , SORTS , LOADED_VERSIONS , OPEN_VERSIONS , USERS_OPENING , FETCHES , EXE
CUTIONS , PX_SERVERS_EXECUTIONS , END_OF_FETCH_COUNT, USERS_EXECUTING , LOADS , FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS , DISK_READS , DIRECT_WRITES , BUFFE
R_GETS , APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME, ROWS_PROCESSED , COMMAND_TYPE , OP
TIMIZER_MODE , OPTIMIZER_COST, OPTIMIZER_ENV, OPTIMIZER_ENV_HASH_VALUE, PARSING_USER_ID , PARSING_SCHEMA_ID , PARSING_SCHEMA_NAME, KEPT_VERSIONS , ADDRESS , TYP
E_CHK_HEAP , HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, SERVICE, SERVICE_HASH, MODULE, MODULE_HASH , ACTION , ACTION_HASH , SERIALIZABLE_ABORTS
, OUTLINE_CATEGORY, CPU_TIME, ELAPSED_TIME, OUTLINE_SID, CHILD_ADDRESS, SQLTYPE, REMOTE, OBJECT_STATUS, LITERAL_HASH_VALUE, LAST_LOAD_TIME, IS_OBSOLETE, CHILD_
LATCH, SQL_PROFILE, PROGRAM_ID, PROGRAM_LINE#, EXACT_MATCHING_SIGNATURE, FORCE_MATCHING_SIGNATURE, LAST_ACTIVE_TIME, BIND_DATA, TYPECHECK_MEM from GV$SQL where
inst_id = USERENV('Instance')
--可以发现查询的基表是 x$kglcursor_child,而SHARABLE_MEM 对应的是kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16。
SELECT DECODE (kglhdadr,
kglhdpar, '父游标句柄地址',
'子游标句柄地址')
text,
kglhdadr,
kglhdpar,
kglnaobj,
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20
FROM x$kglob
WHERE kglobt03 = '4xamnunv51w9j';
TEXT KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ------------ ------------ ------------ -------------------------- ------------
子游标句柄地址 0000000072B96820 0000000076390710 select * from dept where deptno=10 0000000076115D70 0000000073039BB8 3664 8088 1803 13555 13555
父游标句柄地址 0000000076390710 0000000076390710 select * from dept where deptno=10 0000000072B7C3D8 00 2812 0 0 2812 2812
--因为其它的值是0,所以两者是相等的。
--可以发现这样1条sql语句需要占用16K上下，如果在oltp系统没有使用绑定，消耗共享内存很大的，而且导致共享内存出现大量碎片，非常容易触发ora-4031错误。
12.刷新共享池看看。
SYS@test> @&r/sharepool/shp4 4xamnunv51w9j
old 14: WHERE kglobt03 = '&1'
new 14: WHERE kglobt03 = '4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ------------ ------------ ------------ -------------------------- ------------
子游标句柄地址 0000000072B96820 0000000076390710 select * from dept where deptno=10 0000000076115D70 0000000073039BB8 3664 8088 1803 13555 13555
父游标句柄地址 0000000076390710 0000000076390710 select * from dept where deptno=10 0000000072B7C3D8 00 2812 0 0 2812 2812
SYS@test> alter system flush shared_pool;
System altered.
SYS@test> @&r/sharepool/shp4 4xamnunv51w9j
old 14: WHERE kglobt03 = '&1'
new 14: WHERE kglobt03 = '4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ------------ ------------ ------------ -------------------------- ------------
子游标句柄地址 0000000072B96820 0000000076390710 select * from dept where deptno=10 00 00 0 0 1803 1803 1803
父游标句柄地址 0000000076390710 0000000076390710 select * from dept where deptno=10 0000000072B7C3D8 00 2812 0 0 2812 2812
--可以发现父游标占用内存没有回收，仅仅回收了大部分子游标占用内存。
--注意看子游标句柄地址 那行，KGLOBHD0 KGLOBHD6 =0 ,也就是清除了子游标的堆0与堆6. 子游标句柄没有清除。
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000072B96820', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ;
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ------------ ------------ ------------ ------------ ---------------- ---------------- ------------ -------- ------------ ----------------
00007F58BFC59110 17328 1 1 2 KGL handles 0000000072B967F0 384 recr 48 00
--再次执行一次查询：
SCOTT@test> select * from dept where deptno=10;
DEPTNO DNAME LOC
------------ -------------- -------------
10 ACCOUNTING NEW YORK
SYS@test> @&r/sharepool/shp4 4xamnunv51w9j
old 14: WHERE kglobt03 = '&1'
new 14: WHERE kglobt03 = '4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ------------ ------------ ------------ -------------------------- ------------
子游标句柄地址 0000000072B96820 0000000076390710 select * from dept where deptno=10 0000000076115D70 0000000073039BB8 3664 8088 1803 13555 13555
父游标句柄地址 0000000076390710 0000000076390710 select * from dept where deptno=10 0000000072B7C3D8 00 2812 0 0 2812 2812
13.继续如果再产生一个子光标呢？
SCOTT@test> alter session set optimizer_index_caching =10;
Session altered.
SCOTT@test> select * from dept where deptno=10;
DEPTNO DNAME LOC
------------ -------------- -------------
10 ACCOUNTING NEW YORK
SYS@test> @&r/sharepool/shp4 4xamnunv51w9j
old 14: WHERE kglobt03 = '&1'
new 14: WHERE kglobt03 = '4xamnunv51w9j'
TEXT KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ------------ ------------ ------------ -------------------------- ------------
子游标句柄地址 0000000072B96820 0000000076390710 select * from dept where deptno=10 0000000076115D70 0000000073039BB8 3664 8088 1803 13555 13555
子游标句柄地址 00000000729450E8 0000000076390710 select * from dept where deptno=10 000000007BDB5D98 00000000730395F0 3664 8088 1803 13555 13555
父游标句柄地址 0000000076390710 0000000076390710 select * from dept where deptno=10 0000000072B7C3D8 00 2812 0 0 2812 2812
--可以发现如果语句不能共享，再产生新子光标。
--写的有点乱。^_^。