[20220117]超長sql語句.txt
[20220117]超長sql語句.txt
--//最近一直在做一個最佳化工作,發現一些sql語句超長.主要有兩種情況:
1.第一種不知道那個開發寫sql程式碼都是一行,而且欄位與欄位之間還有很多空格,導致語句很長.導致我抽取後給格式化,然後在考慮最佳化
問題.我甚至要設定set linesize 32767,不然會出現換行問題.導致抽取的語句無法執行.而且sqlplus語句有最長239字元.
2.另外一個問題,也是不知道那位開發寫sql程式碼在每個欄位輸出的後面,寫入大量註解說明.這樣即使一條很短的sql語句也被他寫的很長,
總之我最近遇到的問題主要都是超長sql語句的問題.我個人適當的註解無何非議,但是大量註解感覺有點多餘.
--//我以前也做過一些超長sql語句的探究,實際上sql語句執行後,建立父子游標,父游標下儲存了sql語句.這樣如果sql語句很長,必然會
--//導致父遊標的佔用空間變大,消耗共享池記憶體空間,如果開發在編寫sql語句時又不使用繫結變數,這樣問題也許跟嚴重.當然現在的機
--//器記憶體超級大,我想現在很少有人買128G以下記憶體的伺服器.我曾經跟別人說這也許現在效能問題越來越少的原因.實際上是拿良好的
--//硬體掩蓋開發應用拙劣的設計,這些問題僅僅是時間不到,時間一到問題會大量爆發,也許到時候會買更先進的機器再來掩蓋問題,如此
--//迴圈反覆,推動..........此處省略...
--//順便把以前寫的帖子做一個彙總:
--//[20191213]共享池繫結變數的值在哪裡.txt => http://blog.itpub.net/267265/viewspace-2668705/
--//基本可以確定繫結變數的值在子游標的堆0裡面,裡面有一點點問題,我不知道檢視子游標的堆0描述符,應該僅僅看到1個chunk,我前面
--//的測試看到2個,測試太久遠了,不過繫結變數值在子游標堆0裡面應該沒有問題.
--//[20211221]提示precompute_subquery補充2.txt =>http://blog.itpub.net/267265/viewspace-2848575/
--//透過提示導致過濾的條件中值很多,導致子游標堆6佔用空間增大.
--//我仔細看了以前的帖子,發現以前一些沒有注意的細節問題,特別是超長sql語句的多個chunk如何連線在一起的.透過例子說明.
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
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
....
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
*/
* from dept where deptno=&&1;
$ ls -l bb.txt
-rw-r--r-- 1 oracle oinstall 34140 2022-01-18 17:04:07 bb.txt
--//測試前重啟資料庫,主要避免共享池出現zzzzzz字元.
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
---------- ------------- ------------ ---------- --------------- ------------------- -----------
2214508776 47du9nf1zxf78 0 47336 2852011669 2022-01-18 17:08:02 16777219
--//2214508776 = 0x83feb8e8
--//觀察:
SYS@book> @ sharepool/shp4 47du9nf1zxf78 0
KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007C2ADEE8 select /* 10 zzzzzzzzzzzzzzzzzzzzzzzzzzz 1 0 0 000000007C2AC9B0 000000007C2AD538 4528 12144 37168 53840 53840 2214508776 47du9nf1zxf78 0
parent handle address 000000007C2ADEE8 select /* 10 zzzzzzzzzzzzzzzzzzzzzzzzzzz 1 0 0 000000007C2ADE30 00 38816 0 0 38816 38816 2214508776 47du9nf1zxf78 65535
*/
SYS@book> @ fcha 000000007C2ADEE8
Find in which heap (UGA, PGA or Shared Pool) the memory address 000000007C2ADEE8 resides...
Press ENTER to continue, CTRL+C to cancel...
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 000000007C2ADEB8 1 1 KGLHD 34656 recr 80 00
--//似乎建立的chunk能容納整個sql語句.
SYS@book> @ sharepool/shp3 000000007C2ADE30
select a.* from x$ksmsp a where a.ksmchptr=hextoraw('000000007C2ADE30')
no rows selected
select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007C2ADE30')
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F85FD989020 17786 1 1 1 KGLH0^83feb8e8 000000007C2ACDC8 4096 recr 4095 000000007C2ADE30
--//父堆0的大小4096.
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007C2ADE30', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F85FDAA9B40 17785 1 1 1 KGLDA 000000007C2ADDC8 240 freeabl 0 00
--//堆描述符大小.
4.做一個轉儲:
SYS@book> @ ti
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_51949_0001.trc
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug dump heapdump 2050;
Statement processed.
--//注意很慢如果共享池記憶體佔用很大的情況,我自己的測試環境也不快.
Dump of memory from 0x000000007C2ADEB8 to 0x000000007C2B6618
07C2ADEB0 00008761 80B38F00 [a.......]
07C2ADEC0 7C2ADDC8 00000000 00000000 00000000 [..*|............]
07C2ADED0 00000000 00000000 00000000 00000000 [................]
07C2ADEE0 00000000 00080050 80F77D10 00000000 [....P....}......]
07C2ADEF0 80F77D10 00000000 7C2ACE78 00000000 [.}......x.*|....]
07C2ADF00 7C2AE040 00000000 00010000 10012841 [@.*|........A(..]
07C2ADF10 00000001 00000001 00010001 00000002 [................]
07C2ADF20 00000000 00000004 00010000 00000000 [................]
07C2ADF30 00000000 00000000 7C2B65E8 00000000 [.........e+|....]
07C2ADF40 00000001 00000000 7C2ADF48 00000000 [........H.*|....]
07C2ADF50 7C2ADF48 00000000 7C2ADF58 00000000 [H.*|....X.*|....]
07C2ADF60 7C2ADF58 00000000 7C3A9EA8 00000000 [X.*|......:|....]
07C2ADF70 7C3A9EA8 00000000 7C2ADF78 00000000 [..:|....x.*|....]
07C2ADF80 7C2ADF78 00000000 7C2AAA18 00000000 [x.*|......*|....]
07C2ADF90 7C2AAA18 00000000 00000000 00000000 [..*|............]
07C2ADFA0 00000001 00000000 43B749A3 00000000 [.........I.C....]
07C2ADFB0 0000B8E8 00000000 7C2AE028 00000000 [........(.*|....]
07C2ADFC0 7C2ADFC0 00000000 7C2ADFC0 00000000 [..*|......*|....]
07C2ADFD0 7C2ADFD0 00000000 7C2ADFD0 00000000 [..*|......*|....]
07C2ADFE0 00000000 00000000 80640F68 00000000 [........h.d.....]
07C2ADFF0 00000000 00000000 00000000 00000000 [................]
07C2AE000 00000003 00000001 00020002 00000000 [................]
07C2AE010 00000001 00000000 00000000 00000000 [................]
07C2AE020 00000000 00000000 00000000 00000000 [................]
07C2AE030 00000025 00000000 83FEB8E8 00000000 [%...............]
07C2AE040 062F37E0 046E540A 43B749A3 83FEB8E8 [.7/..Tn..I.C....]
07C2AE050 00000000 12017A78 003C0812 00000000 [....xz....<.....]
07C2AE060 00000000 00000000 00008558 00000000 [........X.......]
07C2AE070 00000000 00000000 00000000 00000000 [................]
07C2AE080 7C2AE090 00000000 83FEB8E8 00000053 [..*|........S...]
--//0x7C2AE090 sql語句開始的地址.在sql語句前偏移16位元組處.
--//0x83FEB8E8 = 2214508776 ++++++++=> hash_value,在sql語句前偏移8位元組處.
07C2AE090 656C6573 2F207463 3031202A 7A7A7A0A [select /* 10.zzz]
07C2AE0A0 7A7A7A7A 7A7A7A7A 7A7A7A7A 7A7A7A7A [zzzzzzzzzzzzzzzz]
Repeat 5 times
07C2B6560 7A7A7A7A 7A7A7A7A 7A7A7A7A 7A7A7A0A [zzzzzzzzzzzz.zzz]
07C2B6570 7A7A7A7A 7A7A7A7A 7A7A7A7A 7A7A7A7A [zzzzzzzzzzzzzzzz]
Repeat 4 times
....
07C2B65C0 7A7A7A7A 7A7A7A7A 0A2F2A0A 7266202A [zzzzzzzz.*/.* fr]
07C2B65D0 64206D6F 20747065 72656877 65642065 [om dept where de]
07C2B65E0 6F6E7470 0030313D 00000000 00000000 [ptno=10.........]
07C2B65F0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
07C2B6610 00000000 00000000 [........]
--//正好在一個chunk裡面.
--//開始處 0x00008761 = 34657.開始地址儲存的內容(0x00008761)正好等於chunk大小+1,我估計不是巧合.
--//0x7C2B6618-0x7C2ADEB8 = 34656.
--//chunk的結束地址 減去 chunk的開始地址 = 前面查詢看到的chunk大小34656.
--//隱含說明chunk 開始地址記錄的是chunk的大小-1.
--//另外說明重啟資料庫後,開始執行sql語句很少佔用共享記憶體也很少,執行一條超長sql語句可以很容易找到1個很大的chunk放入.
--//執行一些非繫結變數語句佔用共享記憶體段.再將bb.txt的裡面z換成y.繼續測試:
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
---------- ------------- ------------ ---------- --------------- ------------------- -----------
1364863633 6k3bn8x8pnanj 0 10897 2852011669 2022-01-18 17:18:02 16777222
--//1364863633 = 0x515a2a91
SYS@book> @ sharepool/shp4 6k3bn8x8pnanj 0
KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007C083188 select /* 10 yyyyyyyyyyyyyyyyyyyyyyyyyyy 1 0 0 000000007C076568 000000007C08D450 4528 12144 37168 53840 53840 1364863633 6k3bn8x8pnanj 0
parent handle address 000000007C083188 select /* 10 yyyyyyyyyyyyyyyyyyyyyyyyyyy 1 0 0 000000007CAD1918 00 38817 0 0 38817 38817 1364863633 6k3bn8x8pnanj 65535
*/
SYS@book> @ sharepool/shp3 000000007C083188
select a.* from x$ksmsp a where a.ksmchptr=hextoraw('000000007C083188')
no rows selected
select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007C083188')
no rows selected
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007C083188', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F85FDAA9F08 18702 1 1 1 KGLHD 000000007C083158 2136 recr 80 00
--//KSMCHSIZ=2136,這次就無法容納這個sql語句.看看這次chunk的分佈.
SYS@book> @ ti
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_51949_0002.trc
SYS@book> oradebug dump heapdump 2050;
Statement processed.
--//檢視轉儲,檢索yyyyyy:
Dump of memory from 0x000000007C07EA18 to 0x000000007C07F068
07C07EA10 00000651 00B38F00 [Q.......]
07C07EA20 7C07E8A8 00000000 0A996C98 00000000 [...|.....l......]
07C07EA30 00000000 00000000 0000061F 00000000 [................]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
07C07EA40 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy]
Repeat 1 times
07C07EA60 79797979 79797979 0A797979 79797979 [yyyyyyyyyyy.yyyy]
07C07EA70 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy]
...
Repeat 3 times
07C07F030 79797979 79797979 79797979 0A797979 [yyyyyyyyyyyyyyy.]
07C07F040 2A0A2F2A 6F726620 6564206D 77207470 [*/.* from dept w]
07C07F050 65726568 70656420 3D6F6E74 00003031 [here deptno=10..]
07C07F060 00000000 00000000 [........]
--//這個chunk 應該是對應sql語句的結束部分.
--//0x00000651 = 1617.
--//0x7C07F068-0x7C07EA18+1 = 1617,正好對上.
--//在sql語句前偏移8位元組處. 0x61F = 1567,我猜測對上該chunk的sql語句長度.
--//0x7C07F05d - 0x7C07EA40 +1 = 1566, 加上結尾\0,正好1567.
--//在sql語句前偏移16位元組處. 0x00000000.
SYS@book> @ fcha 000000007C07EA18
Find in which heap (UGA, PGA or Shared Pool) the memory address 000000007C07EA18 resides...
Press ENTER to continue, CTRL+C to cancel...
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 000000007C07EA18 1 1 KGLNA 1616 freeabl 0 00
SYS@book> @sharepool/shp3 000000007C07EA18
select a.* from x$ksmsp a where a.ksmchptr=hextoraw('000000007C07EA18')
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F08850700E0 18658 1 1 1 KGLNA 000000007C07EA18 1616 freeabl 0 00
select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007C07EA18')
no rows selected
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007C07EA18', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F08850700E0 18663 1 1 1 KGLNA 000000007C07EA18 1616 freeabl 0 00
Dump of memory from 0x000000007C083158 to 0x000000007C0839B0
07C083150 00000859 80B38F00 [Y.......]
--//0x0859 = 2137.
--//SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007C083188', 'xxxxxxxxxxxxxxxx')
--//between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1
--//的查詢能對上.
SYS@book> @ calc 0x000000007C0839B0 - 0x000000007C083158
DEC HEX
----------------------------------- --------------------
2136.000000 858
--//大小也能對上.
07C083160 7C083068 00000000 00000000 00000000 [h0.|............]
07C083170 00000000 00000000 00000000 00000000 [................]
07C083180 00000000 C0080050 80ADB5F0 00000000 [....P...........]
07C083190 80ADB5F0 00000000 7C08CD90 00000000 [...........|....]
07C0831A0 7C0832E0 00000000 00010000 10012841 [.2.|........A(..]
07C0831B0 00000001 00000001 00010001 00000002 [................]
07C0831C0 00000000 00000007 00010000 00000000 [................]
07C0831D0 00000000 00000000 7C083980 00000000 [.........9.|....]
07C0831E0 00000001 00000000 7C0831E8 00000000 [.........1.|....]
07C0831F0 7C0831E8 00000000 7C0831F8 00000000 [.1.|.....1.|....]
07C083200 7C0831F8 00000000 7C48DE68 00000000 [.1.|....h.H|....]
07C083210 7C48DE68 00000000 7C083218 00000000 [h.H|.....2.|....]
07C083220 7C083218 00000000 7C08B0A0 00000000 [.2.|.......|....]
07C083230 7C08B0A0 00000000 00000000 00000000 [...|............]
07C083240 00000001 00000000 690D7447 00000000 [........Gt.i....]
07C083250 00002A91 00000000 7C0832C8 00000000 [.*.......2.|....]
07C083260 7C083260 00000000 7C083260 00000000 [`2.|....`2.|....]
07C083270 7C083270 00000000 7C083270 00000000 [p2.|....p2.|....]
07C083280 00000000 00000000 80640F68 00000000 [........h.d.....]
07C083290 00000000 00000000 00000000 00000000 [................]
07C0832A0 00000003 00000001 00020002 00000000 [................]
07C0832B0 00000001 00000000 00000000 00000000 [................]
07C0832C0 00000000 00000000 00000000 00000000 [................]
07C0832D0 00000025 00000000 515A2A91 00000000 [%........*ZQ....]
07C0832E0 ECA06AFD 0137D357 690D7447 515A2A91 [.j..W.7.Gt.i.*ZQ]
07C0832F0 00000000 12017A78 003B1212 00000000 [....xz....;.....]
07C083300 00000000 00000000 00000650 00000000 [........P.......]
07C083310 00000000 00000000 7C08ED98 00000000 [...........|....]
$$$$$$$$
07C083320 7C083330 00000000 515A2A91 00000053 [03.|.....*ZQS...]
--//0x515A2A91 = 1364863633 ++++++++=> hash_value,在sql語句前偏移8位元組處.
--//0x7C083330 sql語句開始的地址.在sql語句前偏移16位元組處.
--//注意 $$$$$$$$ 對應的地址.在sql語句前偏移24位元組處.我猜測下一個chunk的位置.
07C083330 656C6573 2F207463 3031202A 7979790A [select /* 10.yyy]
07C083340 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy]
Repeat 5 times
...
07C083850 7979790A 79797979 79797979 79797979 [.yyyyyyyyyyyyyyy]
07C083860 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy]
Repeat 4 times
07C0838B0 79797979 7979790A 79797979 79797979 [yyyy.yyyyyyyyyyy]
07C0838C0 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy]
Repeat 4 times
07C083910 79797979 79797979 7979790A 79797979 [yyyyyyyy.yyyyyyy]
07C083920 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy]
Repeat 4 times
07C083970 79797979 79797979 79797979 7979790A [yyyyyyyyyyyy.yyy]
07C083980 00000000 00000000 00000000 00000000 [................]
Repeat 2 times
--//SQL語句的開始部分
--//繼續查詢yyyyyy:
Dump of memory from 0x000000007C08ED80 to 0x000000007C096698
07C08ED80 00007919 00B38F00 7C08DCE0 00000000 [.y.........|....]
07C08ED90 0A996C98 00000000 7C07EA30 00000000 [.l......0..|....]
~~~~~~~~
07C08EDA0 000078E9 00000000 79797979 79797979 [.x......yyyyyyyy]
++++++++
07C08EDB0 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy]
Repeat 4 times
....
07C096600 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy]
Repeat 4 times
07C096650 79797979 79797979 7979790A 79797979 [yyyyyyyy.yyyyyyy]
07C096660 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy]
Repeat 2 times
07C096690 00000079 00000000 [y.......]
--//SQL語句的中間部分
--//0x7919 = 31001.
-//0x7C096698-0x7C08ED80+1 = 31001
--//0x78E9 = 30953,sql語句的長度,對應前面的+++++,在sql語句前偏移8位元組處.
--//0x7C07EA30 , 在sql語句前偏移16位元組處.
--//注意看開始的地址內容,正好表示chunk長度+1,我估計不是巧合,至於如何連線起來的,注意sql語句前偏移16位元組處就可以知道.
SYS@book> @fcha 000000007C08ED80
Find in which heap (UGA, PGA or Shared Pool) the memory address 000000007C08ED80 resides...
Press ENTER to continue, CTRL+C to cancel...
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 000000007C08ED80 1 1 KGLNA 31000 freeabl 0 00
--//我把這3個chunk放在一起觀察,按照語句順序,注意看開始sql語句的前24位元組處,以及後續的sql語句的前偏移的16位元組處.
--//chunk 1
Dump of memory from 0x000000007C083158 to 0x000000007C0839B0
07C083150 00000859 80B38F00 [Y.......]
07C083160 7C083068 00000000 00000000 00000000 [h0.|............]
07C083170 00000000 00000000 00000000 00000000 [................]
07C083180 00000000 C0080050 80ADB5F0 00000000 [....P...........]
07C083190 80ADB5F0 00000000 7C08CD90 00000000 [...........|....]
07C0831A0 7C0832E0 00000000 00010000 10012841 [.2.|........A(..]
07C0831B0 00000001 00000001 00010001 00000002 [................]
07C0831C0 00000000 00000007 00010000 00000000 [................]
07C0831D0 00000000 00000000 7C083980 00000000 [.........9.|....]
07C0831E0 00000001 00000000 7C0831E8 00000000 [.........1.|....]
07C0831F0 7C0831E8 00000000 7C0831F8 00000000 [.1.|.....1.|....]
07C083200 7C0831F8 00000000 7C48DE68 00000000 [.1.|....h.H|....]
07C083210 7C48DE68 00000000 7C083218 00000000 [h.H|.....2.|....]
07C083220 7C083218 00000000 7C08B0A0 00000000 [.2.|.......|....]
07C083230 7C08B0A0 00000000 00000000 00000000 [...|............]
07C083240 00000001 00000000 690D7447 00000000 [........Gt.i....]
07C083250 00002A91 00000000 7C0832C8 00000000 [.*.......2.|....]
07C083260 7C083260 00000000 7C083260 00000000 [`2.|....`2.|....]
07C083270 7C083270 00000000 7C083270 00000000 [p2.|....p2.|....]
07C083280 00000000 00000000 80640F68 00000000 [........h.d.....]
07C083290 00000000 00000000 00000000 00000000 [................]
07C0832A0 00000003 00000001 00020002 00000000 [................]
07C0832B0 00000001 00000000 00000000 00000000 [................]
07C0832C0 00000000 00000000 00000000 00000000 [................]
07C0832D0 00000025 00000000 515A2A91 00000000 [%........*ZQ....]
07C0832E0 ECA06AFD 0137D357 690D7447 515A2A91 [.j..W.7.Gt.i.*ZQ]
07C0832F0 00000000 12017A78 003B1212 00000000 [....xz....;.....]
07C083300 00000000 00000000 00000650 00000000 [........P.......]
07C083310 00000000 00000000 7C08ED98 00000000 [...........|....]
--//0x7C08ED98 => 下一個chunk的位置(看下面的+++++++)
07C083320 7C083330 00000000 515A2A91 00000053 [03.|.....*ZQS...]
--// ~~~~~~~~=> 0x7C083330 指向sql語句開始位置,0x515A2A91 = 1364863633 ,hash_value.
07C083330 656C6573 2F207463 3031202A 7979790A [select /* 10.yyy]
07C083340 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy]
...
07C083970 79797979 79797979 79797979 7979790A [yyyyyyyyyyyy.yyy]
07C083980 00000000 00000000 00000000 00000000 [................]
--//chunk 2
Dump of memory from 0x000000007C08ED80 to 0x000000007C096698
07C08ED80 00007919 00B38F00 7C08DCE0 00000000 [.y.........|....]
07C08ED90 0A996C98 00000000 7C07EA30 00000000 [.l......0..|....]
--// ++++++++
--//0x7C07EA30,下一個chunk的位置(看下面的!!!!!!!)
07C08EDA0 000078E9 00000000 79797979 79797979 [.x......yyyyyyyy]
--//000078E9 = 30953 ,對應該chunk,sql語句佔用的長度.
07C08EDB0 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy]
Repeat 4 times
....
07C096600 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy]
Repeat 4 times
07C096650 79797979 79797979 7979790A 79797979 [yyyyyyyy.yyyyyyy]
07C096660 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy]
Repeat 2 times
07C096690 00000079 00000000 [y.......]
--//chunk 3
Dump of memory from 0x000000007C07EA18 to 0x000000007C07F068
07C07EA10 00000651 00B38F00 [Q.......]
07C07EA20 7C07E8A8 00000000 0A996C98 00000000 [...|.....l......]
07C07EA30 00000000 00000000 0000061F 00000000 [................]
--// !!!!!!!!,結束0x00000000.
07C07EA40 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy]
Repeat 1 times
07C07EA60 79797979 79797979 0A797979 79797979 [yyyyyyyyyyy.yyyy]
07C07EA70 79797979 79797979 79797979 79797979 [yyyyyyyyyyyyyyyy]
...
Repeat 3 times
07C07F030 79797979 79797979 79797979 0A797979 [yyyyyyyyyyyyyyy.]
07C07F040 2A0A2F2A 6F726620 6564206D 77207470 [*/.* from dept w]
07C07F050 65726568 70656420 3D6F6E74 00003031 [here deptno=10..]
07C07F060 00000000 00000000 [........]
--//當然是不是這樣連線在一起的我不知道,只能自己亂猜測.
--//也就是第1個chunk sql語句前24位元組處記錄了下一個chunk的位置,前16位元組記錄sql語句的開始,前8位元組記錄hash_value.
--//第2個chunk語句前16位元組處記錄了下一個chunk的位置,前8位元組記錄該chunk sql語句佔用長度.
--//最後一個chunk語句前16位元組處記錄0x00000000,表示結束.補充也許應該是0x00000000 00000000.前8位元組記錄該chunk sql語句剩餘長度.
--//猜測這些純粹想了解超長sql語句如何連線在一起的.
6.附上指令碼:
$ cat shp4.sql
column N0_6_16 format 99999999
SELECT DECODE (kglhdadr,
kglhdpar, 'parent handle address',
'child handle address')
-- text,
kglhdadr,
kglhdpar,
substr(kglnaobj,1,40) c40,
KGLHDLMD,
KGLHDPMD,
kglhdivc,
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16 N0_6_16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
kglnahsh,
kglobt03 ,
kglobt09
FROM x$kglob
WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;
$ cat shp3.sql
prompt
prompt select a.* from x$ksmsp a where a.ksmchptr=hextoraw('&1');
prompt
--select a.*,dump(a.KSMCHCOM,16) c60 from x$ksmsp a where a.ksmchptr=hextoraw('&1');
select a.* from x$ksmsp a where a.ksmchptr=hextoraw('&1');
prompt
prompt select a.* from x$ksmsp a where a.ksmchpar=hextoraw('&&1');
prompt
--select a.*,dump(a.KSMCHCOM,16) c60 from x$ksmsp a where a.ksmchpar=hextoraw('&&1');
select a.* from x$ksmsp a where a.ksmchpar=hextoraw('&&1');
--select * from x$ksmsp where ksmchptr in (
--SELECT x
-- FROM (SELECT a.ksmchptr, lag (a.ksmchptr, 1) OVER (ORDER BY a.ksmchptr) x
-- FROM x$ksmsp a )
-- WHERE '&1' between x and ksmchptr);
-- oracle do not support raw compare
--SELECT * FROM x$ksmsp WHERE '&1' between KSMCHPTR and TO_CHAR(TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ,'0xxxxxxxxxxxxxxx');
prompt
prompt SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1;
prompt
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2852946/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20220119]超長sql語句補充3.txtSQL
- [20220120]超長sql語句補充4.txtSQL
- [20201105]再分析sql語句.txtSQL
- [20201210]sql語句優化.txtSQL優化
- [20220331]如何調整sql語句.txtSQL
- [20240607]PL/SQL中sql語句的註解.txtSQL
- [20240320]空格與sqlpus的sql語句.txtSQL
- [20200320]SQL語句優化的困惑.txtSQL優化
- [20181119]sql語句執行緩慢分析.txtSQL
- [20181114]一條sql語句的優化.txtSQL優化
- [20211221]分析sql語句遇到的問題.txtSQL
- [20220329]是否開發寫錯sql語句.txtSQL
- [20210923]sql語句佔用Sharable Memory分析.txtSQL
- [20211009]使用bash計算sql語句的sql_id.txtSQL
- [20200324]SQL語句優化的困惑2.txtSQL優化
- [20211224]vim外掛格式化sql語句.txtSQL
- [20211231]vim自動格式化sql語句.txtSQL
- [20220329]19c sql語句打補丁.txtSQL
- [20210112]ashtop查詢特定表的SQL語句.txtSQL
- [20191101]通過zsh計算sql語句的sql_id.txtSQL
- [20191011]通過bash計算sql語句的sql_id.txtSQL
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- SQL語句SQL
- mysql sql語句執行超時設定MySql
- [20190328]簡單探究sql語句相關mutexes.txtSQLMutex
- [20200126]使用DBMS_SHARED_POOL.MARKHOT與sql語句.txtSQL
- [20200326]dbms_monitor跟蹤與SQL語句分析.txtSQL
- [20201214]查詢隱式轉換的sql語句.txtSQL
- 20201214]查詢隱式轉換的sql語句.txtSQL
- [20190329]探究sql語句相關mutexes補充2.txtSQLMutex
- [20220111]該語句的sql_id如何計算的.txtSQL
- [20220109]開發不應該這樣寫SQL語句.txtSQL
- [20211229]toad下優化sql語句注意的問題.txt優化SQL
- [20230329]記錄除錯sql語句遇到的問題.txt除錯SQL
- [20221012]修改統計資訊最佳化sql語句.txtSQL
- [20210113]ashtop查詢特定表的SQL語句2.txtSQL
- 【SQL】9 SQL INSERT INTO 語句SQL
- 【SQL】10 SQL UPDATE 語句SQL