本文共 55908 字，大约阅读时间需要 186 分钟。
[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 - 64biSCOTT@test> select * from dept where deptno=10;
DEPTNO DNAME LOC ------------ -------------- ------------- 10 ACCOUNTING NEW YORKSCOTT@test> @&r/dpc '' ''
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------- SQL_ID 4xamnunv51w9j, child number 0 ------------------------------------- select * from dept where deptno=10Plan 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.trcSCOTT@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 2812SELECT * 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 timesChunk 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 times7.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 ------------ 13555SYS@test> select SHARABLE_MEM from v$sqlarea where sql_id='4xamnunv51w9j';
SHARABLE_MEM ------------ 13555SYS@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_childSYS@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 2812SYS@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 YORKSYS@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 281213.继续如果再产生一个子光标呢？
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 YORKSYS@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--可以发现如果语句不能共享，再产生新子光标。
--写的有点乱。^_^。转载地址：http://aiuml.baihongyu.com/