20150213關於共享池4-SQL記憶體結構父子游標

lfree發表於2015-02-15

[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....<.d.....>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

--可以發現如果語句不能共享,再產生新子游標。
--寫的有點亂。^_^。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-1436541/,如需轉載,請註明出處,否則將追究法律責任。

相關文章