[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語句正好吻合

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

[20161230]檢視父遊標中sql語句.txt

相關文章