[20220117]超长sql语句.txt

来源：这里教程网时间：2026-03-03 17:26:19 作者：

[20220117]超长sql语句.txt --//最近一直在做一个优化工作,发现一些sql语句超长.主要有两种情况: 1.第一种不知道那个开发写sql代码都是一行,而且字段与字段之间还有很多空格,导致语句很长.导致我抽取后给格式化,然后在考虑优化问题.我甚至要设置set linesize 32767,不然会出现换行问题.导致抽取的语句无法执行.而且sqlplus语句有最长239字符. 2.另外一个问题,也是不知道那位开发写sql代码在每个字段输出的后面,写入大量注解说明.这样即使一条很短的sql语句也被他写的很长, 总之我最近遇到的问题主要都是超长sql语句的问题.我个人适当的注解无何非议,但是大量注解感觉有点多余. --//我以前也做过一些超长sql语句的探究,实际上sql语句执行后,建立父子光标,父光标下保存了sql语句.这样如果sql语句很长,必然会 --//导致父游标的占用空间变大,消耗共享池内存空间,如果开发在编写sql语句时又不使用绑定变量,这样问题也许跟严重.当然现在的机 --//器内存超级大,我想现在很少有人买128G以下内存的服务器.我曾经跟别人说这也许现在性能问题越来越少的原因.实际上是拿良好的 --//硬件掩盖开发应用拙劣的设计,这些问题仅仅是时间不到,时间一到问题会大量爆发,也许到时候会买更先进的机器再来掩盖问题,如此 --//循环反复,推动..........此处省略... --//顺便把以前写的帖子做一个汇总: --//[20191213]共享池绑定变量的值在哪里.txt => http://blog.itpub.net/267265/viewspace-2668705/ --//基本可以确定绑定变量的值在子光标的堆0里面,里面有一点点问题,我不知道查看子光标的堆0描述符,应该仅仅看到1个chunk,我前面 --//的测试看到2个,测试太久远了,不过绑定变量值在子光标堆0里面应该没有问题. --//[20211221]提示precompute_subquery补充2.txt =>http://blog.itpub.net/267265/viewspace-2848575/ --//通过提示导致过滤的条件中值很多,导致子光标堆6占用空间增大. --//我仔细看了以前的帖子,发现以前一些没有注意的细节问题,特别是超长sql语句的多个chunk如何连接在一起的.通过例子说明. 1.环境: SCOTT@book> @ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.建立脚本: $ cat bb.txt select /* &&1 zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz .... zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz */ * from dept where deptno=&&1; $ ls -l bb.txt -rw-r--r-- 1 oracle oinstall 34140 2022-01-18 17:04:07 bb.txt --//测试前重启数据库,主要避免共享池出现zzzzzz字符. 3.测试: SCOTT@book> @ bb.txt 10 DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//执行多次. SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ------------------- ----------- 2214508776 47du9nf1zxf78 0 47336 2852011669 2022-01-18 17:08:02 16777219 --//2214508776 = 0x83feb8e8 --//观察: SYS@book> @ sharepool/shp4 47du9nf1zxf78 0 KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 --------------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address 000000007C2ADEE8 select /* 10 zzzzzzzzzzzzzzzzzzzzzzzzzzz 1 0 0 000000007C2AC9B0 000000007C2AD538 4528 12144 37168 53840 53840 2214508776 47du9nf1zxf78 0 parent handle address 000000007C2ADEE8 select /* 10 zzzzzzzzzzzzzzzzzzzzzzzzzzz 1 0 0 000000007C2ADE30 00 38816 0 0 38816 38816 2214508776 47du9nf1zxf78 65535 */ SYS@book> @ fcha 000000007C2ADEE8 Find in which heap (UGA, PGA or Shared Pool) the memory address 000000007C2ADEE8 resides... Press ENTER to continue, CTRL+C to cancel... LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- SGA 000000007C2ADEB8 1 1 KGLHD 34656 recr 80 00 --//似乎建立的chunk能容纳整个sql语句. SYS@book> @ sharepool/shp3 000000007C2ADE30 select a.* from x$ksmsp a where a.ksmchptr=hextoraw('000000007C2ADE30') no rows selected select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007C2ADE30') ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007F85FD989020 17786 1 1 1 KGLH0^83feb8e8 000000007C2ACDC8 4096 recr 4095 000000007C2ADE30 --//父堆0的大小4096. SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007C2ADE30', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1 ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007F85FDAA9B40 17785 1 1 1 KGLDA 000000007C2ADDC8 240 freeabl 0 00 --//堆描述符大小. 4.做一个转储: SYS@book> @ ti New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_51949_0001.trc SYS@book> oradebug setmypid Statement processed. SYS@book> oradebug dump heapdump 2050; Statement processed. --//注意很慢如果共享池内存占用很大的情况,我自己的测试环境也不快. Dump of memory from 0x000000007C2ADEB8 to 0x000000007C2B6618 07C2ADEB0 00008761 80B38F00 [a.......] 07C2ADEC0 7C2ADDC8 00000000 00000000 00000000 [..*|............] 07C2ADED0 00000000 00000000 00000000 00000000 [................] 07C2ADEE0 00000000 00080050 80F77D10 00000000 [....P....}......] 07C2ADEF0 80F77D10 00000000 7C2ACE78 00000000 [.}......x.*|....] 07C2ADF00 7C2AE040 00000000 00010000 10012841 [@.*|........A(..] 07C2ADF10 00000001 00000001 00010001 00000002 [................] 07C2ADF20 00000000 00000004 00010000 00000000 [................] 07C2ADF30 00000000 00000000 7C2B65E8 00000000 [.........e+|....] 07C2ADF40 00000001 00000000 7C2ADF48 00000000 [........H.*|....] 07C2ADF50 7C2ADF48 00000000 7C2ADF58 00000000 [H.*|....X.*|....] 07C2ADF60 7C2ADF58 00000000 7C3A9EA8 00000000 [X.*|......:|....] 07C2ADF70 7C3A9EA8 00000000 7C2ADF78 00000000 [..:|....x.*|....] 07C2ADF80 7C2ADF78 00000000 7C2AAA18 00000000 [x.*|......*|....] 07C2ADF90 7C2AAA18 00000000 00000000 00000000 [..*|............] 07C2ADFA0 00000001 00000000 43B749A3 00000000 [.........I.C....] 07C2ADFB0 0000B8E8 00000000 7C2AE028 00000000 [........(.*|....] 07C2ADFC0 7C2ADFC0 00000000 7C2ADFC0 00000000 [..*|......*|....] 07C2ADFD0 7C2ADFD0 00000000 7C2ADFD0 00000000 [..*|......*|....] 07C2ADFE0 00000000 00000000 80640F68 00000000 [........h.d.....] 07C2ADFF0 00000000 00000000 00000000 00000000 [................] 07C2AE000 00000003 00000001 00020002 00000000 [................] 07C2AE010 00000001 00000000 00000000 00000000 [................] 07C2AE020 00000000 00000000 00000000 00000000 [................] 07C2AE030 00000025 00000000 83FEB8E8 00000000 [%...............] 07C2AE040 062F37E0 046E540A 43B749A3 83FEB8E8 [.7/..Tn..I.C....] 07C2AE050 00000000 12017A78 003C0812 00000000 [....xz....<.....] 07C2AE060 00000000 00000000 00008558 00000000 [........X.......] 07C2AE070 00000000 00000000 00000000 00000000 [................] 07C2AE080 7C2AE090 00000000 83FEB8E8 00000053 [..*|........S...] --//0x7C2AE090 sql语句开始的地址.在sql语句前偏移16字节处. --//0x83FEB8E8 = 2214508776 ++++++++=> hash_value,在sql语句前偏移8字节处. 07C2AE090 656C6573 2F207463 3031202A 7A7A7A0A [select /* 10.zzz] 07C2AE0A0 7A7A7A7A 7A7A7A7A 7A7A7A7A 7A7A7A7A [zzzzzzzzzzzzzzzz] Repeat 5 times 07C2B6560 7A7A7A7A 7A7A7A7A 7A7A7A7A 7A7A7A0A [zzzzzzzzzzzz.zzz] 07C2B6570 7A7A7A7A 7A7A7A7A 7A7A7A7A 7A7A7A7A [zzzzzzzzzzzzzzzz] Repeat 4 times .... 07C2B65C0 7A7A7A7A 7A7A7A7A 0A2F2A0A 7266202A [zzzzzzzz.*/.* fr] 07C2B65D0 64206D6F 20747065 72656877 65642065 [om dept where de] 07C2B65E0 6F6E7470 0030313D 00000000 00000000 [ptno=10.........] 07C2B65F0 00000000 00000000 00000000 00000000 [................] Repeat 1 times 07C2B6610 00000000 00000000 [........] --//正好在一个chunk里面. --//开始处 0x00008761 = 34657.开始地址保存的内容(0x00008761)正好等于chunk大小+1,我估计不是巧合. --//0x7C2B6618-0x7C2ADEB8 = 34656. --//chunk的结束地址减去 chunk的开始地址 = 前面查询看到的chunk大小34656. --//隐含说明chunk 开始地址记录的是chunk的大小-1. --//另外说明重启数据库后,开始执行sql语句很少占用共享内存也很少,执行一条超长sql语句可以很容易找到1个很大的chunk放入. --//执行一些非绑定变量语句占用共享内存段.再将bb.txt的里面z换成y.继续测试: SCOTT@book> @ bb.txt 10 DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ------------------- ----------- 1364863633 6k3bn8x8pnanj 0 10897 2852011669 2022-01-18 17:18:02 16777222 --//1364863633 = 0x515a2a91 SYS@book> @ sharepool/shp4 6k3bn8x8pnanj 0 KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 --------------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address 000000007C083188 select /* 10 yyyyyyyyyyyyyyyyyyyyyyyyyyy 1 0 0 000000007C076568 000000007C08D450 4528 12144 37168 53840 53840 1364863633 6k3bn8x8pnanj 0 parent handle address 000000007C083188 select /* 10 yyyyyyyyyyyyyyyyyyyyyyyyyyy 1 0 0 000000007CAD1918 00 38817 0 0 38817 38817 1364863633 6k3bn8x8pnanj 65535 */ SYS@book> @ sharepool/shp3 000000007C083188 select a.* from x$ksmsp a where a.ksmchptr=hextoraw('000000007C083188') no rows selected select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007C083188') no rows selected SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007C083188', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1 ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007F85FDAA9F08 18702 1 1 1 KGLHD 000000007C083158 2136 recr 80 00 --//KSMCHSIZ=2136,这次就无法容纳这个sql语句.看看这次chunk的分布. SYS@book> @ ti New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_51949_0002.trc SYS@book> oradebug dump heapdump 2050; Statement processed. --//查看转储,检索yyyyyy: Dump of memory from 0x000000007C07EA18 to 0x000000007C07F068 07C07EA10 00000651 00B38F00 [Q.......] 07C07EA20 7C07E8A8 00000000 0A996C98 00000000 [...|.....l......] 07C07EA30 00000000 00000000 0000061F 00000000 [................] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 07C07EA40 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy] Repeat 1 times 07C07EA60 79797979 79797979 0A797979 79797979 [yyyyyyyyyyy.yyyy] 07C07EA70 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy] ... Repeat 3 times 07C07F030 79797979 79797979 79797979 0A797979 [yyyyyyyyyyyyyyy.] 07C07F040 2A0A2F2A 6F726620 6564206D 77207470 [*/.* from dept w] 07C07F050 65726568 70656420 3D6F6E74 00003031 [here deptno=10..] 07C07F060 00000000 00000000 [........] --//这个chunk 应该是对应sql语句的结束部分. --//0x00000651 = 1617. --//0x7C07F068-0x7C07EA18+1 = 1617,正好对上. --//在sql语句前偏移8字节处. 0x61F = 1567,我猜测对上该chunk的sql语句长度. --//0x7C07F05d - 0x7C07EA40 +1 = 1566, 加上结尾\0,正好1567. --//在sql语句前偏移16字节处. 0x00000000. SYS@book> @ fcha 000000007C07EA18 Find in which heap (UGA, PGA or Shared Pool) the memory address 000000007C07EA18 resides... Press ENTER to continue, CTRL+C to cancel... LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- SGA 000000007C07EA18 1 1 KGLNA 1616 freeabl 0 00 SYS@book> @sharepool/shp3 000000007C07EA18 select a.* from x$ksmsp a where a.ksmchptr=hextoraw('000000007C07EA18') ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007F08850700E0 18658 1 1 1 KGLNA 000000007C07EA18 1616 freeabl 0 00 select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007C07EA18') no rows selected SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007C07EA18', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1 ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007F08850700E0 18663 1 1 1 KGLNA 000000007C07EA18 1616 freeabl 0 00 Dump of memory from 0x000000007C083158 to 0x000000007C0839B0 07C083150 00000859 80B38F00 [Y.......] --//0x0859 = 2137. --//SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007C083188', 'xxxxxxxxxxxxxxxx') --//between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1 --//的查询能对上. SYS@book> @ calc 0x000000007C0839B0 - 0x000000007C083158 DEC HEX ----------------------------------- -------------------- 2136.000000 858 --//大小也能对上. 07C083160 7C083068 00000000 00000000 00000000 [h0.|............] 07C083170 00000000 00000000 00000000 00000000 [................] 07C083180 00000000 C0080050 80ADB5F0 00000000 [....P...........] 07C083190 80ADB5F0 00000000 7C08CD90 00000000 [...........|....] 07C0831A0 7C0832E0 00000000 00010000 10012841 [.2.|........A(..] 07C0831B0 00000001 00000001 00010001 00000002 [................] 07C0831C0 00000000 00000007 00010000 00000000 [................] 07C0831D0 00000000 00000000 7C083980 00000000 [.........9.|....] 07C0831E0 00000001 00000000 7C0831E8 00000000 [.........1.|....] 07C0831F0 7C0831E8 00000000 7C0831F8 00000000 [.1.|.....1.|....] 07C083200 7C0831F8 00000000 7C48DE68 00000000 [.1.|....h.H|....] 07C083210 7C48DE68 00000000 7C083218 00000000 [h.H|.....2.|....] 07C083220 7C083218 00000000 7C08B0A0 00000000 [.2.|.......|....] 07C083230 7C08B0A0 00000000 00000000 00000000 [...|............] 07C083240 00000001 00000000 690D7447 00000000 [........Gt.i....] 07C083250 00002A91 00000000 7C0832C8 00000000 [.*.......2.|....] 07C083260 7C083260 00000000 7C083260 00000000 [`2.|....`2.|....] 07C083270 7C083270 00000000 7C083270 00000000 [p2.|....p2.|....] 07C083280 00000000 00000000 80640F68 00000000 [........h.d.....] 07C083290 00000000 00000000 00000000 00000000 [................] 07C0832A0 00000003 00000001 00020002 00000000 [................] 07C0832B0 00000001 00000000 00000000 00000000 [................] 07C0832C0 00000000 00000000 00000000 00000000 [................] 07C0832D0 00000025 00000000 515A2A91 00000000 [%........*ZQ....] 07C0832E0 ECA06AFD 0137D357 690D7447 515A2A91 [.j..W.7.Gt.i.*ZQ] 07C0832F0 00000000 12017A78 003B1212 00000000 [....xz....;.....] 07C083300 00000000 00000000 00000650 00000000 [........P.......] 07C083310 00000000 00000000 7C08ED98 00000000 [...........|....] $$$$$$$$ 07C083320 7C083330 00000000 515A2A91 00000053 [03.|.....*ZQS...] --//0x515A2A91 = 1364863633 ++++++++=> hash_value,在sql语句前偏移8字节处. --//0x7C083330 sql语句开始的地址.在sql语句前偏移16字节处. --//注意 $$$$$$$$ 对应的地址.在sql语句前偏移24字节处.我猜测下一个chunk的位置. 07C083330 656C6573 2F207463 3031202A 7979790A [select /* 10.yyy] 07C083340 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy] Repeat 5 times ... 07C083850 7979790A 79797979 79797979 79797979 [.yyyyyyyyyyyyyyy] 07C083860 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy] Repeat 4 times 07C0838B0 79797979 7979790A 79797979 79797979 [yyyy.yyyyyyyyyyy] 07C0838C0 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy] Repeat 4 times 07C083910 79797979 79797979 7979790A 79797979 [yyyyyyyy.yyyyyyy] 07C083920 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy] Repeat 4 times 07C083970 79797979 79797979 79797979 7979790A [yyyyyyyyyyyy.yyy] 07C083980 00000000 00000000 00000000 00000000 [................] Repeat 2 times --//SQL语句的开始部分 --//继续查询yyyyyy: Dump of memory from 0x000000007C08ED80 to 0x000000007C096698 07C08ED80 00007919 00B38F00 7C08DCE0 00000000 [.y.........|....] 07C08ED90 0A996C98 00000000 7C07EA30 00000000 [.l......0..|....] ~~~~~~~~ 07C08EDA0 000078E9 00000000 79797979 79797979 [.x......yyyyyyyy] ++++++++ 07C08EDB0 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy] Repeat 4 times .... 07C096600 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy] Repeat 4 times 07C096650 79797979 79797979 7979790A 79797979 [yyyyyyyy.yyyyyyy] 07C096660 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy] Repeat 2 times 07C096690 00000079 00000000 [y.......] --//SQL语句的中间部分 --//0x7919 = 31001. -//0x7C096698-0x7C08ED80+1 = 31001 --//0x78E9 = 30953,sql语句的长度,对应前面的+++++,在sql语句前偏移8字节处. --//0x7C07EA30 , 在sql语句前偏移16字节处. --//注意看开始的地址内容,正好表示chunk长度+1,我估计不是巧合,至于如何连接起来的,注意sql语句前偏移16字节处就可以知道. SYS@book> @fcha 000000007C08ED80 Find in which heap (UGA, PGA or Shared Pool) the memory address 000000007C08ED80 resides... Press ENTER to continue, CTRL+C to cancel... LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- SGA 000000007C08ED80 1 1 KGLNA 31000 freeabl 0 00 --//我把这3个chunk放在一起观察,按照语句顺序,注意看开始sql语句的前24字节处,以及后续的sql语句的前偏移的16字节处. --//chunk 1 Dump of memory from 0x000000007C083158 to 0x000000007C0839B0 07C083150 00000859 80B38F00 [Y.......] 07C083160 7C083068 00000000 00000000 00000000 [h0.|............] 07C083170 00000000 00000000 00000000 00000000 [................] 07C083180 00000000 C0080050 80ADB5F0 00000000 [....P...........] 07C083190 80ADB5F0 00000000 7C08CD90 00000000 [...........|....] 07C0831A0 7C0832E0 00000000 00010000 10012841 [.2.|........A(..] 07C0831B0 00000001 00000001 00010001 00000002 [................] 07C0831C0 00000000 00000007 00010000 00000000 [................] 07C0831D0 00000000 00000000 7C083980 00000000 [.........9.|....] 07C0831E0 00000001 00000000 7C0831E8 00000000 [.........1.|....] 07C0831F0 7C0831E8 00000000 7C0831F8 00000000 [.1.|.....1.|....] 07C083200 7C0831F8 00000000 7C48DE68 00000000 [.1.|....h.H|....] 07C083210 7C48DE68 00000000 7C083218 00000000 [h.H|.....2.|....] 07C083220 7C083218 00000000 7C08B0A0 00000000 [.2.|.......|....] 07C083230 7C08B0A0 00000000 00000000 00000000 [...|............] 07C083240 00000001 00000000 690D7447 00000000 [........Gt.i....] 07C083250 00002A91 00000000 7C0832C8 00000000 [.*.......2.|....] 07C083260 7C083260 00000000 7C083260 00000000 [`2.|....`2.|....] 07C083270 7C083270 00000000 7C083270 00000000 [p2.|....p2.|....] 07C083280 00000000 00000000 80640F68 00000000 [........h.d.....] 07C083290 00000000 00000000 00000000 00000000 [................] 07C0832A0 00000003 00000001 00020002 00000000 [................] 07C0832B0 00000001 00000000 00000000 00000000 [................] 07C0832C0 00000000 00000000 00000000 00000000 [................] 07C0832D0 00000025 00000000 515A2A91 00000000 [%........*ZQ....] 07C0832E0 ECA06AFD 0137D357 690D7447 515A2A91 [.j..W.7.Gt.i.*ZQ] 07C0832F0 00000000 12017A78 003B1212 00000000 [....xz....;.....] 07C083300 00000000 00000000 00000650 00000000 [........P.......] 07C083310 00000000 00000000 7C08ED98 00000000 [...........|....] --//0x7C08ED98 => 下一个chunk的位置(看下面的+++++++) 07C083320 7C083330 00000000 515A2A91 00000053 [03.|.....*ZQS...] --// ~~~~~~~~=> 0x7C083330 指向sql语句开始位置,0x515A2A91 = 1364863633 ,hash_value. 07C083330 656C6573 2F207463 3031202A 7979790A [select /* 10.yyy] 07C083340 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy] ... 07C083970 79797979 79797979 79797979 7979790A [yyyyyyyyyyyy.yyy] 07C083980 00000000 00000000 00000000 00000000 [................] --//chunk 2 Dump of memory from 0x000000007C08ED80 to 0x000000007C096698 07C08ED80 00007919 00B38F00 7C08DCE0 00000000 [.y.........|....] 07C08ED90 0A996C98 00000000 7C07EA30 00000000 [.l......0..|....] --// ++++++++ --//0x7C07EA30,下一个chunk的位置(看下面的!!!!!!!) 07C08EDA0 000078E9 00000000 79797979 79797979 [.x......yyyyyyyy] --//000078E9 = 30953 ,对应该chunk,sql语句占用的长度. 07C08EDB0 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy] Repeat 4 times .... 07C096600 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy] Repeat 4 times 07C096650 79797979 79797979 7979790A 79797979 [yyyyyyyy.yyyyyyy] 07C096660 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy] Repeat 2 times 07C096690 00000079 00000000 [y.......] --//chunk 3 Dump of memory from 0x000000007C07EA18 to 0x000000007C07F068 07C07EA10 00000651 00B38F00 [Q.......] 07C07EA20 7C07E8A8 00000000 0A996C98 00000000 [...|.....l......] 07C07EA30 00000000 00000000 0000061F 00000000 [................] --// !!!!!!!!,结束0x00000000. 07C07EA40 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy] Repeat 1 times 07C07EA60 79797979 79797979 0A797979 79797979 [yyyyyyyyyyy.yyyy] 07C07EA70 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy] ... Repeat 3 times 07C07F030 79797979 79797979 79797979 0A797979 [yyyyyyyyyyyyyyy.] 07C07F040 2A0A2F2A 6F726620 6564206D 77207470 [*/.* from dept w] 07C07F050 65726568 70656420 3D6F6E74 00003031 [here deptno=10..] 07C07F060 00000000 00000000 [........] --//当然是不是这样连接在一起的我不知道,只能自己乱猜测. --//也就是第1个chunk sql语句前24字节处记录了下一个chunk的位置,前16字节记录sql语句的开始,前8字节记录hash_value. --//第2个chunk语句前16字节处记录了下一个chunk的位置,前8字节记录该chunk sql语句占用长度. --//最后一个chunk语句前16字节处记录0x00000000,表示结束.补充也许应该是0x00000000 00000000.前8字节记录该chunk sql语句剩余长度. --//猜测这些纯粹想了解超长sql语句如何连接在一起的. 6.附上脚本: $ cat shp4.sql column N0_6_16 format 99999999 SELECT DECODE (kglhdadr, kglhdpar, 'parent handle address', 'child handle address') -- text, kglhdadr, kglhdpar, substr(kglnaobj,1,40) c40, KGLHDLMD, KGLHDPMD, kglhdivc, kglobhd0, kglobhd6, kglobhs0,kglobhs6,kglobt16, kglobhs0+kglobhs6+kglobt16 N0_6_16, kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20, kglnahsh, kglobt03 , kglobt09 FROM x$kglob WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2; $ cat shp3.sql prompt prompt select a.* from x$ksmsp a where a.ksmchptr=hextoraw('&1'); prompt --select a.*,dump(a.KSMCHCOM,16) c60 from x$ksmsp a where a.ksmchptr=hextoraw('&1'); select a.* from x$ksmsp a where a.ksmchptr=hextoraw('&1'); prompt prompt select a.* from x$ksmsp a where a.ksmchpar=hextoraw('&&1'); prompt --select a.*,dump(a.KSMCHCOM,16) c60 from x$ksmsp a where a.ksmchpar=hextoraw('&&1'); select a.* from x$ksmsp a where a.ksmchpar=hextoraw('&&1'); --select * from x$ksmsp where ksmchptr in ( --SELECT x -- FROM (SELECT a.ksmchptr, lag (a.ksmchptr, 1) OVER (ORDER BY a.ksmchptr) x -- FROM x$ksmsp a ) -- WHERE '&1' between x and ksmchptr); -- oracle do not support raw compare --SELECT * FROM x$ksmsp WHERE '&1' between KSMCHPTR and TO_CHAR(TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ,'0xxxxxxxxxxxxxxx'); prompt prompt SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1; prompt SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1;