前言
数据库版本：Version 19.3.0.0.0
操作系统版本：CentOS Linux release 7.6.1810 (Core)
实验环境：PDB1
一、SQL内存结构父子游标解析
以下实验，是连接到pdb下，硬解析一条SQL的过程
二、SQL父游标解析
2.1、查看SQL的父游标句柄地址和堆0描述地址
x$kglob说明• x$kglob记录SQL的游标句柄信息
• KGLHDADR 游标句柄地址
• KGLNAOBJ 游标对应SQL信息
• KGLOBHD0 父游标堆0对应的地址，其实它是父游标堆0的描述地址，记录父游标堆0下有哪些chunk
2.2 做一个共享池全量dump
2.3、查看父游标句柄对应的chunk
根据上面1.1中查的父游标句柄地址KGLHDADR=00000000724BC848
通过724BC840去1.3中生成的trc文件中搜索
trc日志解析：
• recrPT003表示它是一个recreate类型的chunk
• sz表示它的大小为816字节
• KGLHD表示它是KGL句柄
• 同时它是一个独立的CHUNK，不属于任何子堆
通过多次实验可以确认，句柄头的地址与句柄地址相关48字节
2.4、查看父游标DS对应的chunk
查看DS=000000006FB55C80 CHUNK信息，用6FB55C8去搜索
可以确认DS对应的chunk是freeable，大小为504字节，和父游标堆0不相邻
通过多次实验可以确认，DS头的地址与DS地址相差104字节
2.5、查看父游标堆0下的的chunk
KSMCHPTR=000000006F6A0E98就是父游标堆0真正的CHUNK了，继续找
ds 06fb55c80就是上面查的父游标DS地址
三、SQL子游标解析
3.1、子游标句柄
通过地址6F68D8D去找子游标句柄
Chunk 06f68d8a8 sz= 560 recrPT003 "KGLHD " Dump of memory from 0x000000006F68D8A8 to 0x000000006F68DAD8 06F68D8A0 00000231 80B38F00 [1.......] 06F68D8B0 6F68D690 00000000 00000000 00000000 [..ho............] 06F68D8C0 00000000 00000000 00000000 00000000 [................] 06F68D8D0 00000003 41080050 6F68D8D8 00000000 [....P..A..ho....] #06F68D8E0 6F68D8D8 00000000 6F779848 00000000 [..ho....H.wo....] 06F68D8F0 00000000 00000000 00010000 10012111 [.............!..] 06F68D900 00000001 00000001 00010001 00000001 [................] 06F68D910 00000000 00000001 00010000 00000000 [................]
子游标句柄也是recretable类型，KGLHD表示它是句柄，大小560字节
同时子游标句柄在父游标堆0也可以找到对应的地址
3.2、查看子游标堆0描述地址、堆6描述地址
这里因为共享池设置的太小，导致子游标的堆0和堆6被覆盖了,进一步验证freeable最容易被覆盖
研究的方案和父游标一样，这样可以计算出一条SQL占用了哪些CHUNK，对共享池优化有进一步的帮助。
3.3 子游标堆0 DS信息
KGLOBHD0=000000006DC82F68
3.4 子游标堆6 DS信息
KGLOBHD0=000000006F6A1680
这里查到堆6在父游标堆6地址里 000000006F6A1680 在0x000000006F6A0E98 to 0x000000006F6A1E98 从dump信息也可以验证 06F6A0E90 00000000 00000000 [........] Chunk 06f6a0e98 sz= 4096 recrPC003 "KGLH0^e12ae359 " ds 06fb55c80 sz= 4096 ct= 1 Dump of memory from 0x000000006F6A0E98 to 0x000000006F6A1E98 06F6A0E90 00001001 80B38F00 [........] 06F6A0EA0 6F69FE98 00000000 00000000 00000000 [..io............] 06F6A0EB0 00000000 00000000 00000000 00000000 [................] 06F6A0EC0 00000003 000A0FFF 6FB55C80 00000000 [..........o....] 06F6A0ED0 00000000 00000000 00000079 40B38F00 [........y......@] 。。 06F6A1650 6F779848 00000000 6F371960 00000000 [H.wo.....7o....] 06F6A1660 00000001 00000000 00000000 00000000 [................] 06F6A1670 00000000 00000000 00000000 00000000 [................] #06F6A1680 6016FF60 00000000 00000000 00000000 [..............] 子游标堆6描述地址在这 06F6A1690 6F6A1530 00000000 6A796D20 00000000 [0.jo.... myj....] 06F6A16A0 6F371970 00000000 00000000 00000000 [p.7o............] 。。
3.5 查看子游标堆0下的chunk
dupm相关信息
#000000006F779768 Chunk 06f779768 sz= 4096 recrPC003 "KGLH0^e12ae359 " ds 06dc82f68 sz= 8192 ct= 2 06f487900 sz= 4096 Dump of memory from 0x000000006F779768 to 0x000000006F77A768 06F779760 00001001 80B38F00 [........] 06F779770 6F778768 00000000 00000000 00000000 [h.wo............] 06F779780 00000000 00000000 00000000 00000000 [................] 06F779790 00000003 400A0FFF 6DC82F68 00000000 [.......@h/.m....] 06F7797A0 00000000 00000000 00000079 40B38F00 [........y......@] #000000006F487900 Chunk 06f487900 sz= 4096 freeable "KGLH0^e12ae359 " ds=0x6dc82f68 Dump of memory from 0x000000006F487900 to 0x000000006F488900 06F487900 00001001 00B38F00 6F486CE8 00000000 [.........lHo....] 06F487910 601718B8 00000000 6DC82F68 00000000 [...`....h/.m....]
3.6 查看子游标堆6下的chunk
dupm相关信息
#000000006F371930 Chunk 06f371930 sz= 4096 recrUM003 "SQLA^e12ae359 " D08:33:23 ds 06f6a1680 sz= 36896 ct= 9 06a796d08 sz= 4096 06a797d08 sz= 4096 071238f28 sz= 4128 070207ea0 sz= 4096 071865a08 sz= 4096 071e82f88 sz= 4096 06c613668 sz= 4096 070226e78 sz= 4096 Dump of memory from 0x000000006F371930 to 0x000000006F372930 06F371930 00001001 80B38F00 6F370930 00000000 [........0.7o....] 06F371940 725DACF8 00000000 7294D050 00000000 [..]r....P..r....] 06F371950 72A70FC8 00000000 02000003 00020FFF [...r............] 06F371960 6F6A1680 00000000 00000000 00000000 [..jo............] #000000006A797D08 Chunk 06a797d08 sz= 4096 freeableU "SQLA^e12ae359 " ds=0x6f6a1680 Dump of memory from 0x000000006A797D08 to 0x000000006A798D08 06A797D00 00001001 00B38F00 [........] 06A797D10 6A796D08 00000000 601761C0 00000000 [.myj.....a.....] 06A797D20 6F6A1680 00000000 71238F40 00000000 [..jo....@.#q....] #000000006A796D08 Chunk 06a796d08 sz= 4096 freeableU "SQLA^e12ae359 " ds=0x6f6a1680 Dump of memory from 0x000000006A796D08 to 0x000000006A797D08 06A796D00 00001001 00B38F00 [........] 06A796D10 6A795D08 00000000 601761C0 00000000 [.]yj.....a.....] #000000006C613668 Chunk 06c613668 sz= 4096 freeableU "SQLA^e12ae359 " ds=0x6f6a1680 Dump of memory from 0x000000006C613668 to 0x000000006C614668 06C613660 00001001 00B38F00 [........] 06C613670 6C612F00 00000000 601761C0 00000000 [./al.....a.....] 06C613680 6F6A1680 00000000 70226E90 00000000 [..jo.....n"p....] #0000000070226E78 Chunk 070226e78 sz= 4096 freeableU "SQLA^e12ae359 " ds=0x6f6a1680 Dump of memory from 0x0000000070226E78 to 0x0000000070227E78 070226E70 00001001 00B38F00 [........] #0000000070207EA0 Chunk 070207ea0 sz= 4096 freeableU "SQLA^e12ae359 " ds=0x6f6a1680 Dump of memory from 0x0000000070207EA0 to 0x0000000070208EA0 070207EA0 00001001 00B38F00 70207038 00000000 [........8p p....] #0000000071238F28 71238F20 00000001 00000000 [........] Chunk 071238f28 sz= 4128 freeableU "SQLA^e12ae359 " ds=0x6f6a1680 #这个chunk只用了4128字节 Dump of memory from 0x0000000071238F28 to 0x0000000071239F48 071238F20 00001021 00B38F00 [!.......] #0000000071865A08 1865A00 00000000 00300030 [....0.0.] Chunk 071865a08 sz= 4096 freeableU "SQLA^e12ae359 " ds=0x6f6a1680 Dump of memory from 0x0000000071865A08 to 0x0000000071866A08 071865A00 00001001 00B38F00 [........] #0000000071E82F88 Chunk 071e82f88 sz= 4096 freeableU "SQLA^e12ae359 " ds=0x6f6a1680 Dump of memory from 0x0000000071E82F88 to 0x0000000071E83F88 071E82F80 00001001 00B38F00 [........]
注：本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。
