[20161230]查看父游标中sql语句.txt
--上午巡检完,无聊,测试使用oradebug下查看sql语句在父游标中的内容.sql语句在执行第一次硬解析时生成父子游标,其中父游标chunk
--中保存sql语句,测试通过oradebug下如何查看:
1.环境:
SCOTT@book> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--多执行几次,保持sql语句光标在共享池中,确定sql_id= 4xamnunv51w9j.
SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0
old 17: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 17: WHERE kglobt03 = '4xamnunv51w9j' or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007BE4DDB8 000000007D1B5D98 select * from dept where deptno=10 000000007CBCC6D0 000000007BA5D690 4560 12144 3067 19771 19771 911274289 4xamnunv51w9j 0
父游标句柄地址 000000007D1B5D98 000000007D1B5D98 select * from dept where deptno=10 000000007B5E7E88 00 4720 0 0 4720 4720 911274289 4xamnunv51w9j 65535
--父游标地址=000000007D1B5D98
2.看看范围:
> SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007D1B5D98', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FFD8A3FB4B8 9505 1 1 1 KGLHD 000000007D1B5D68 560 recr 80 00
--可以确定范围: 0x00007F6F855B1CB0 ,0x00007F6F855B1CB0+560.
--//先看看sql语句转储的内容:
SCOTT@book> select dump('select * from dept where deptno=10',16) from dual ;
DUMP('SELECT*FROMDEPTWHEREDEPTNO=10',16)
--------------------------------------------------------------------------------------------------------------------
Typ=96 Len=34: 73,65,6c,65,63,74,20,2a,20,66,72,6f,6d,20,64,65,70,74,20,77,68,65,72,65,20,64,65,70,74,6e,6f,3d,31,30
--//我使用的cpu是intel系列的,存在大小头问题,在内存中看到的应该4个字节4个字节颠倒.前面4个应该是65,6c,65,73(16进制).
--//BTW: 我的测试oradebug peek最多查询60个字节.
SYS@book> oradebug peek 0x000000007D1B5D98 64
[07D1B5D98, 07D1B5DD8) = 807CBD30 00000000 807CBD30 00000000 7BA5CFD0 00000000 7D1B5EF0 00000000 00010000 10012841 00000001 00000001 00010001 00000002 ...
SYS@book> oradebug peek 0x000000007D1B5D98 60
[07D1B5D98, 07D1B5DD4) = 807CBD30 00000000 807CBD30 00000000 7BA5CFD0 00000000 7D1B5EF0 00000000 00010000 10012841 00000001 00000001 00010001 00000002 00000000
3.继续测试:
SCOTT@book> select 'oradebug peek '|| to_char(TO_NUMBER ('000000007D1B5D98', 'xxxxxxxxxxxxxxxx')+(rownum-1)*60)||' 60;' c40 from dual connect by level<=10;
C40
----------------------------------------
oradebug peek 2098945432 60;
oradebug peek 2098945492 60;
oradebug peek 2098945552 60;
oradebug peek 2098945612 60;
oradebug peek 2098945672 60;
oradebug peek 2098945732 60;
oradebug peek 2098945792 60;
oradebug peek 2098945852 60;
oradebug peek 2098945912 60;
oradebug peek 2098945972 60;
10 rows selected.
--60*9+20=560,修改最好一行oradebug peek 2098945972 20;执行如下:
oradebug setmypid
spool aa.txt
oradebug peek 2098945432 60;
oradebug peek 2098945492 60;
oradebug peek 2098945552 60;
oradebug peek 2098945612 60;
oradebug peek 2098945672 60;
oradebug peek 2098945732 60;
oradebug peek 2098945792 60;
oradebug peek 2098945852 60;
oradebug peek 2098945912 60;
oradebug peek 2098945972 20;
spool off
$ cat aa.txt | grep -i 656c6573
[07D1B5F3C, 07D1B5F78) = 00000053 656C6573 2A207463 6F726620 6564206D 77207470 65726568 70656420 3D6F6E74 00003031 00000000 00000000 00000000 00000000 00000000
--//可以确定偏移量是0x07D1B5F40.
SYS@book> oradebug peek 0x07D1B5F40 60
[07D1B5F40, 07D1B5F7C) = 656C6573 2A207463 6F726620 6564206D 77207470 65726568 70656420 3D6F6E74 00003031 00000000 00000000 00000000 00000000 00000000 00000000
--//7D1B5F40 = 2098945856
--//7D1B5D68 = 2098945384
--//2098945856-2098945384=472
--偏移在472的位置.
$ echo "656C6573 2A207463 6F726620 6564206D 77207470 65726568 70656420 3D6F6E74 00003031"| xxd -r -p | od -t x4 | cut -c9- | xxd -r -p ;echo
select * from dept where deptno=10
--对比sql语句正好吻合
$ cut -f2 -d= aa.txt | sed 's/ //g'| xxd -r -p | od -t x4 -v | cut -c9- | xxd -r -p >| aa.bin
$ ls -l aa.bin
-rw-r--r-- 1 oracle oinstall 560 2016-12-30 09:28:32 aa.bin
$ xxd -c 32 aa.bin
0000000: 30bd 7c80 0000 0000 30bd 7c80 0000 0000 d0cf a57b 0000 0000 f05e 1b7d 0000 0000 0.|.....0.|.....邢.{....館.}....
0000020: 0000 0100 4128 0110 0100 0000 0100 0000 0100 0100 0200 0000 0000 0000 0d00 0000 ....A(..........................
0000040: 0000 0100 0000 0000 0000 0000 0000 0000 685f 1b7d 0000 0000 0100 0000 0000 0000 ................h_.}............
0000060: f85d 1b7d 0000 0000 f85d 1b7d 0000 0000 085e 1b7d 0000 0000 085e 1b7d 0000 0000 鴀.}....鴀.}.....^.}.....^.}....
0000080: 7069 2c7d 0000 0000 7069 2c7d 0000 0000 285e 1b7d 0000 0000 285e 1b7d 0000 0000 pi,}....pi,}....(^.}....(^.}....
00000a0: e008 617e 0000 0000 e008 617e 0000 0000 0000 0000 0000 0000 0100 0000 0000 0000 ?a~....?a~....................
00000c0: d574 aa4e 0000 0000 31f1 0000 0000 0000 d85e 1b7d 0000 0000 705e 1b7d 0000 0000 誸狽....1?.....豝.}....p^.}....
00000e0: 705e 1b7d 0000 0000 805e 1b7d 0000 0000 805e 1b7d 0000 0000 0000 0000 0000 0000 p^.}.....^.}.....^.}............
0000100: 68ff 0680 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0300 0000 0100 0000 h?..............................
0000120: 0200 0200 0000 0000 0100 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 ................................
0000140: 0000 0000 0000 0000 2e00 0000 0000 0000 31f1 5036 0000 0000 5dc4 3114 e7b9 dbbd ................1馪6....]?.??
0000160: d574 aa4e 31f1 5036 0000 0000 7874 0c1e 0921 2f00 0000 0000 0000 0000 0000 0000 誸狽1馪6....xt...!/.............
0000180: 2300 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 405f 1b7d 0000 0000 #.......................@_.}....
00001a0: 31f1 5036 5300 0000 7365 6c65 6374 202a 2066 726f 6d20 6465 7074 2077 6865 7265 1馪6S...select * from dept where
00001c0: 2064 6570 746e 6f3d 3130 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 deptno=10......................
00001e0: 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 ................................
0000200: 0102 0000 008f b380 685d 1b7d 0000 0000 f893 1e7d 0000 0000 3007 8b7d 0000 0000 ........h].}....?.}....0..}....
0000220: 0000 0000 0000 0000 0000 0000 5000 0000 ...........
