[20220120]超長sql語句補充4.txt

[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,正好一直