[20220120]超长sql语句补充4.txt

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

[20220120]超长sql语句补充4.txt --//昨天通过oradebug peek 查看超长sql语句父游标句柄的chunk如何将sql语句连接在一起的, --//我当时查看时有点吃惊,开始每个chunk的sql语句长度仅仅0x201=513字节,这样34140/513 = 66个chunk,需要不断地oradebug peek --//才能完成,我差点想放弃,当时就想写一个脚步完成测试,今天尝试写一个脚本完成看看. 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 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx .... xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx */ * from dept where deptno=&1; $ ls -l bb.txt -rw-r--r-- 1 oracle oinstall 34137 2022-01-21 16:55:53 bb.txt 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 ---------- ------------- ------------ ---------- --------------- ------------------- ----------- 4181801068 g1zfa6gwn2f3c 0 79980 2852011669 2022-01-21 16:56:19 16777222 --//4181801068 = 0xf941386c 4.看看sql语句如何串在一起的: SYS@book> @ sharepool/shp4 g1zfa6gwn2f3c 0 KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 --------------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address 000000007DC5B408 select /* 10 xxxxxxxxxxxxxxxxxxxxxxxxxxx 1 0 0 000000007DA84278 000000007BE7A698 4528 12144 37168 53840 53840 4181801068 g1zfa6gwn2f3c 0 parent handle address 000000007DC5B408 select /* 10 xxxxxxxxxxxxxxxxxxxxxxxxxxx 1 0 0 000000007DE63B70 00 38817 0 0 38817 38817 4181801068 g1zfa6gwn2f3c 65535 */ SYS@book> @ fcha 000000007DC5B408 Find in which heap (UGA, PGA or Shared Pool) the memory address 000000007DC5B408 resides... Press ENTER to continue, CTRL+C to cancel... LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- SGA 000000007DC5B3D8 1 1 KGLHD 1032 recr 80 00 SYS@book> @ ttt tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4702.trc SYS@book> oradebug setmypid Statement processed. SYS@book> oradebug peek 0x000000007DC5B3D8 1032 1 [07DC5B3D8, 07DC5B7E0) = 00000409 80B38F00 7DC5B268 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00080050 8044B900 00000000 ... --//查看转储: *** 2022-01-21 16:59:18.850 Processing Oradebug command 'peek 0x000000007DC5B3D8 1032 1' [07DC5B3D8, 07DC5B7E0) = 00000409 80B38F00 7DC5B268 00000000 00000000 ... Dump of memory from 0x07DC5B3EC to 0x07DC5B7E0 07DC5B3E0 00000000 [....] 07DC5B3F0 00000000 00000000 00000000 00000000 [................] 07DC5B400 00000000 00080050 8044B900 00000000 [....P.....D.....] 07DC5B410 8044B900 00000000 7BE79FD8 00000000 [..D........{....] 07DC5B420 7DC5B560 00000000 00010000 10012841 [`..}........A(..] 07DC5B430 00000001 00000001 00010001 00000002 [................] 07DC5B440 00000000 00000007 00010000 00000000 [................] 07DC5B450 00000000 00000000 7DC5B7B0 00000000 [...........}....] 07DC5B460 00000001 00000000 7DC5B468 00000000 [........h..}....] 07DC5B470 7DC5B468 00000000 7DC5B478 00000000 [h..}....x..}....] 07DC5B480 7DC5B478 00000000 7E373228 00000000 [x..}....(27~....] 07DC5B490 7E373228 00000000 7DC5B498 00000000 [(27~.......}....] 07DC5B4A0 7DC5B498 00000000 7D6441D8 00000000 [...}.....Ad}....] 07DC5B4B0 7D6441D8 00000000 00000000 00000000 [.Ad}............] 07DC5B4C0 00000001 00000000 F0FDCA33 00000000 [........3.......] 07DC5B4D0 0001386C 00000000 7DC5B548 00000000 [l8......H..}....] 07DC5B4E0 7DC5B4E0 00000000 7DC5B4E0 00000000 [...}.......}....] 07DC5B4F0 7DC5B4F0 00000000 7DC5B4F0 00000000 [...}.......}....] 07DC5B500 00000000 00000000 80640F68 00000000 [........h.d.....] 07DC5B510 00000000 00000000 00000000 00000000 [................] 07DC5B520 00000003 00000001 00020002 00000000 [................] 07DC5B530 00000001 00000000 00000000 00000000 [................] 07DC5B540 00000000 00000000 00000000 00000000 [................] 07DC5B550 00000025 00000000 F941386C 00000000 [%.......l8A.....] 07DC5B560 BBACAB7F 74523C42 F0FDCA33 F941386C [....B<Rt3...l8A.] 07DC5B570 00000000 15017A78 00123911 00000000 [....xz...9......] 07DC5B580 00000000 00000000 00000200 00000000 [................] ~~~~~~~~ 07DC5B590 00000000 00000000 7CAB8D88 00000000 [...........|....] 07DC5B5A0 7DC5B5B0 00000000 F941386C 00000053 [...}....l8A.S...] 07DC5B5B0 656C6573 2F207463 3031202A 7878780A [select /* 10.xxx] 07DC5B5C0 78787878 78787878 78787878 78787878 [xxxxxxxxxxxxxxxx] Repeat 5 times 07DC5B620 7878780A 78787878 78787878 78787878 [.xxxxxxxxxxxxxxx] 07DC5B630 78787878 78787878 78787878 78787878 [xxxxxxxxxxxxxxxx] Repeat 4 times 07DC5B680 78787878 7878780A 78787878 78787878 [xxxx.xxxxxxxxxxx] 07DC5B690 78787878 78787878 78787878 78787878 [xxxxxxxxxxxxxxxx] Repeat 4 times 07DC5B6E0 78787878 78787878 7878780A 78787878 [xxxxxxxx.xxxxxxx] 07DC5B6F0 78787878 78787878 78787878 78787878 [xxxxxxxxxxxxxxxx] Repeat 4 times 07DC5B740 78787878 78787878 78787878 7878780A [xxxxxxxxxxxx.xxx] 07DC5B750 78787878 78787878 78787878 78787878 [xxxxxxxxxxxxxxxx] Repeat 5 times 07DC5B7B0 00000000 00000000 00000000 00000000 [................] Repeat 2 times *** 2022-01-21 16:59:18.851 --// 07DC5B590 00000000 00000000 7CAB8D88 00000000 [...........|....] 07DC5B5A0 7DC5B5B0 00000000 F941386C 00000053 [...}....l8A.S...] 07DC5B5B0 656C6573 2F207463 3031202A 7878780A [select /* 10.xxx] --//24字节偏移 0x7CAB8D88 对应下一个chunk的包含sql语句的其它部分. --//16字节偏移 0x7DC5B5B0 对应select的开始位置 --// 8字节偏移 0xF941386C 对应sql语句的hash_value. --//sql语句的开始地址是0x07DC5B7B0,结束的下一个地址是0x07DC5B5B0 ,两者相减0x07DC5B7B0-0x07DC5B5B0 = 512. --//多次测试应该下划线0x00000200表示sql语句长度,位于sql前面偏移40字节处. --//写脚本如下: $ cat bb.sh #! /bin/bash addr=$1 while [ $addr != '0000000000000000' ] do sqlplus -s -l / as sysdba <<EOF | tee >(grep '^\[' | awk '{print $5$4}' >|/tmp/xx.tmp) oradebug setmypid oradebug peek 0x${addr} 60 EOF addr=$(cat /tmp/xx.tmp) done $ . bb.sh 000000007CAB8D88 Statement processed. [07CAB8D88, 07CAB8DC4) = 7D350A48 00000000 00000201 00000000 7878780A 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 Statement processed. [07D350A48, 07D350A84) = 7CD2EC20 00000000 000002D1 00000000 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 Statement processed. [07CD2EC20, 07CD2EC5C) = 7DBEBEE8 00000000 000002F1 00000000 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 Statement processed. [07DBEBEE8, 07DBEBF24) = 7C48F138 00000000 000002F9 00000000 78787878 78787878 78787878 78780A78 78787878 78787878 78787878 78787878 78787878 78787878 78787878 Statement processed. [07C48F138, 07C48F174) = 7C4D3DE8 00000000 000002F9 00000000 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 Statement processed. [07C4D3DE8, 07C4D3E24) = 7BE83638 00000000 00000319 00000000 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 Statement processed. [07BE83638, 07BE83674) = 7E0A8FB8 00000000 00000319 00000000 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 Statement processed. [07E0A8FB8, 07E0A8FF4) = 7E0B5BD8 00000000 00000381 00000000 78787878 78780A78 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 Statement processed. [07E0B5BD8, 07E0B5C14) = 7D5F6DB8 00000000 000003F1 00000000 78787878 78787878 7878780A 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 Statement processed. [07D5F6DB8, 07D5F6DF4) = 7D3FC160 00000000 00000401 00000000 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 Statement processed. [07D3FC160, 07D3FC19C) = 7CA4E7F0 00000000 00000631 00000000 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 Statement processed. [07CA4E7F0, 07CA4E82C) = 7C539D90 00000000 00000631 00000000 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 Statement processed. [07C539D90, 07C539DCC) = 7D937F48 00000000 00000631 00000000 78787878 7878780A 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 Statement processed. [07D937F48, 07D937F84) = 7C9DCFC8 00000000 00000631 00000000 78787878 78787878 78787878 78787878 0A787878 78787878 78787878 78787878 78787878 78787878 78787878 Statement processed. [07C9DCFC8, 07C9DD004) = 7E2954C8 00000000 00000651 00000000 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 780A7878 78787878 78787878 Statement processed. [07E2954C8, 07E295504) = 7BD12960 00000000 00000631 00000000 78787878 78787878 78787878 78787878 78780A78 78787878 78787878 78787878 78787878 78787878 78787878 Statement processed. [07BD12960, 07BD1299C) = 7E391378 00000000 00003939 00000000 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 7878780A 78787878 78787878 Statement processed. [07E391378, 07E3913B4) = 00000000 00000000 0000057F 00000000 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 78787878 --//保存为aa1.txt计算sql语句累加长度. $ grep "^\[" aa1.txt | awk '{print strtonum("0x"$6)}'| paste -sd+ |bc 33624 --//计算第1个chunk的sql语句长度. --//0x07DC5B7B0-0x07DC5B5B0 = 512 --//33624+512 = 34136 --//这样前面bb.txt文件长度是34137,扣除分号,\n字符,sql语句最后还有补一个\0,这样正好34137-2+1 = 34136,正好一直

编辑推荐：

返回列表

[20220120]超长sql语句补充4.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推