[20161230]查看父游标中sql语句.txt

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

上一篇:JAVA轻量级文件监控


下一篇:用JavaMelody来监控我们的web应用