[20191122]oracel SQL parsing function qcplgte.txt
--//昨天看了鏈接:https://nenadnoveljic.com/blog/memory-leak-parsing/ =>Memory Leak During Parsing
qcplgte
qcplgte is one of the auxiliary functions in Oracle database that underpin SQL parsing. In particular, this function
divides the SQL text into several parts. In Oracle 12.2, the function receives a pointer through the second argument.
The address to the next part of the SQL string to parse is stored at the 8 bytes offset. After parsing the portion of
the SQL text, the function will update the same memory location with the pointer to the substring for the next parsing
stage.
Qcplgte是Oracle資料庫中支持SQL決議的輔助功能之一，尤其是這一職能將SQL文本分為幾個部分，在Oracle12.2中，函式通過第二個參
數接收指標，要決議的SQL字串的下一部分的地址存盤在8位元組偏移量處，在決議了在SQL文本中，函式將用指向子字串的指標更新相
同的記憶體位置，以便進行下一次決議舞臺，
After having figured out the input, it is fairly easy to come up with gdb commands which will display all of the parsing
stages:
在計算出輸入之后，可以很容易地找到GDB命令，這些命令將顯示所有的決議階段：
break qcplgte
set pagination off
commands 1
silent
x/s *(uint64_t *)($rsi+0x8)
continue
end
Here's the short explanation of the commands above: According to x64 calling convention for System V the second
parameter is passed through the %rsi register. The pointer to the SQL text is stored in the memory location %rsi+8. The
OS is 64-bit, therefore casting to uint64_t when dereferencing %rsi+0x8. Finally, x/s will dereference the pointer to
the (sub)string which is stored on the memory location %rsi+0x8.
下面是對上面命令的簡短解釋：根據對SystemV的x64呼叫約定，第二個引數通過%RSI暫存器傳遞，指向SQL文本的指標存盤在記憶體位置
%rsi 8中，作業系統為64位，因此當取消參考%rsi 0x8時，轉換為uint 64_t，最后，x/s將取消指向存盤在記憶體位置%rsi 0x8上的(子)字
符串的指標，
--//注:里面的翻譯我直接拿金山詞霸翻譯的.可能不是很準確.
--//我并不感興趣作者遇到的Memory Leak問題.而是使用跟蹤qcplgte看到的輸出.自己也測驗看看:
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
$ cat parse.gdb
break qcplgte
set pagination off
commands 1
silent
x/s *(uint64_t *)($rsi+0x8)
continue
end
--//分析scott.dept表.略.重啟資料庫.
2.建立測驗環境:
--//視窗1:
SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
295 5 34816 DEDICATED 34817 21 3 alter system kill session '295,5' immediate;
--//記下spid=34817
--//視窗2:
$ gdb -p 34817 -x parse.gdb
GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-45.el5)
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
Attaching to process 34817
...
3.測驗1:
--//視窗1:
SCOTT@book> select/*+this is a test*/* from dept;
--//掛起.
--//視窗2:
Continuing.
0x7fff2e05d9c8: "select/*+this is a test*/* from dept"
0x7fff2e05d9ce: "/*+this is a test*/* from dept"
0x7fff2e05d9d1: "this is a test*/* from dept"
0x7fff2e05d9d5: " is a test*/* from dept"
0x7fff2e05d9e1: "* from dept"
0x7fff2e05d9e2: " from dept"
0x7fff2e05d9e7: " dept"
0x7fff2e05d9ec: ""
0x7fcbb40a8100: "select/*+this is a test*/* from dept"
0x7fcbb40a8106: "/*+this is a test*/* from dept"
0x7fcbb40a8119: "* from dept"
0x7fcbb40a811a: " from dept"
0x7fcbb40a811f: " dept"
0x7fcbb40a8124: ""
0x7fcbb40a8090: "SELECT /*+THISISATEST*/ * FROM DEPT"
0x7fcbb40a8096: " /*+THISISATEST*/ * FROM DEPT"
0x7fcbb40a80a9: " FROM DEPT"
0x7fcbb40a80ae: " DEPT"
--//很奇怪看到3組,第1組占8行也就是呼叫函式qcplgte8次.好像在逐步分解.而第2次呼叫函式qcplgte6次.這個用語言不好表達,慢慢理解吧.
--//實際上陳述句越復雜,第1組看到的呼叫函式qcplgte次數越多.原始鏈接有1個例子,大家可以自行測驗.
--//而第3組是全部換成大寫,注解部分發生了合并,洗掉了空格.
--//視窗1,如果再次執行相同陳述句.視窗2不會有輸出,因為第2次執行是軟決議.
SCOTT@book> select/*+this is a test*/* from dept;
4.測驗2:
--//視窗1:
SCOTT@book> select/*+this is a test*/* from scott.dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--//加入shaema看看.
--//視窗2:
0x7fff2e05d9c0: "select/*+this is a test*/* from scott.dept "
0x7fff2e05d9c6: "/*+this is a test*/* from scott.dept "
0x7fff2e05d9c9: "this is a test*/* from scott.dept "
0x7fff2e05d9cd: " is a test*/* from scott.dept "
0x7fff2e05d9d9: "* from scott.dept "
0x7fff2e05d9da: " from scott.dept "
0x7fff2e05d9df: " scott.dept "
0x7fff2e05d9e5: ".dept "
0x7fff2e05d9e6: "dept "
0x7fff2e05d9ea: " "
0x7fcbb40a8108: "select/*+this is a test*/* from scott.dept "
0x7fcbb40a810e: "/*+this is a test*/* from scott.dept "
0x7fcbb40a8121: "* from scott.dept "
0x7fcbb40a8122: " from scott.dept "
0x7fcbb40a8127: " scott.dept "
0x7fcbb40a812d: ".dept "
0x7fcbb40a812e: "dept "
0x7fcbb40a8132: " "
0x7fcbb40a8090: "SELECT /*+THISISATEST*/ * FROM SCOTT . DEPT"
0x7fcbb40a8096: " /*+THISISATEST*/ * FROM SCOTT . DEPT"
0x7fcbb40a80a9: " FROM SCOTT . DEPT"
0x7fcbb40a80ae: " SCOTT . DEPT"
0x7fcbb40a80b4: " . DEPT"
0x7fcbb40a80b6: " DEPT"
--//發現一個很有趣的情況注意看第3組的輸出,schema.tablename中間有空格.
--//我打開另外的視窗3執行如下:
--//視窗3:
SYS@book> select * from scott . dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--//也就是如上寫法也是能執行成功的.
4.測驗3:
--//突然想起我們許多情況應用使用大量文字變數,設定cursor_sharing=force的情況.測驗看看這種情況如何決議.
--//視窗1:
SCOTT@book> alter session set cursor_sharing=force ;
Session altered.
SCOTT@book> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--//視窗2:
0x7fff9d820208: "select * from dept where deptno=10"
0x7fff9d82020e: " * from dept where deptno=10"
0x7fff9d820210: " from dept where deptno=10"
0x7fff9d820215: " dept where deptno=10"
0x7fff9d82021a: " where deptno=10"
0x7fff9d820220: " deptno=10"
0x7fff9d820227: "=10"
0x7fff9d820228: "10"
0x7fff9d82022a: ""
0x7dc4a338: "select * from dept where deptno=:\"SYS_B_0\""
0x7dc4a33e: " * from dept where deptno=:\"SYS_B_0\""
0x7dc4a340: " from dept where deptno=:\"SYS_B_0\""
0x7dc4a345: " dept where deptno=:\"SYS_B_0\""
0x7dc4a34a: " where deptno=:\"SYS_B_0\""
0x7dc4a350: " deptno=:\"SYS_B_0\""
0x7dc4a357: "=:\"SYS_B_0\""
0x7dc4a351: "deptno=:\"SYS_B_0\""
0x7dc4a357: "=:\"SYS_B_0\""
0x7dc4a358: ":\"SYS_B_0\""
0x7dc4a359: "\"SYS_B_0\""
0x7dc4a362: ""
0x7f0cb4e28108: "select * from dept where deptno=:\"SYS_B_0\""
0x7f0cb4e2810e: " * from dept where deptno=:\"SYS_B_0\""
0x7f0cb4e28110: " from dept where deptno=:\"SYS_B_0\""
0x7f0cb4e28115: " dept where deptno=:\"SYS_B_0\""
0x7f0cb4e2811a: " where deptno=:\"SYS_B_0\""
0x7f0cb4e28120: " deptno=:\"SYS_B_0\""
0x7f0cb4e28127: "=:\"SYS_B_0\""
0x7f0cb4e28128: ":\"SYS_B_0\""
0x7f0cb4e28129: "\"SYS_B_0\""
0x7f0cb4e28132: ""
0x7f0cb4e28090: "SELECT * FROM DEPT WHERE DEPTNO = :\"SYS_B_0\""
0x7f0cb4e28096: " * FROM DEPT WHERE DEPTNO = :\"SYS_B_0\""
0x7f0cb4e28098: " FROM DEPT WHERE DEPTNO = :\"SYS_B_0\""
0x7f0cb4e2809d: " DEPT WHERE DEPTNO = :\"SYS_B_0\""
0x7f0cb4e280a2: " WHERE DEPTNO = :\"SYS_B_0\""
0x7f0cb4e280a8: " DEPTNO = :\"SYS_B_0\""
0x7f0cb4e280af: " = :\"SYS_B_0\""
0x7f0cb4e280b1: " :\"SYS_B_0\""
0x7f0cb4e280b3: "\"SYS_B_0\""
0x7de1bdd6: "DEPTNO"
0x7de1bddc: ""
0x7db9aad6: "DNAME"
0x7db9aadb: ""
0x7db9a9fe: "LOC"
0x7db9aa01: ""
--//看到4組,仔細觀察可以發現前面2組與第一次測驗一樣.我覺得真正分析的是第3次.
--//還很奇怪的是這樣的情況還看到表dept中3個欄位的分析呼叫.
--//視窗3:
SCOTT@book> show parameter cursor_sharing
NAME TYPE VALUE
-------------- ------ ------
cursor_sharing string EXACT
SCOTT@book> select * from dept where deptno=30;
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
--//切換會視窗1,執行相同陳述句:
SCOTT@book> select * from dept where deptno=30;
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
--//視窗2看到的輸出:
0x7fff9d820208: "select * from dept where deptno=30"
0x7fff9d82020e: " * from dept where deptno=30"
0x7fff9d820210: " from dept where deptno=30"
0x7fff9d820215: " dept where deptno=30"
0x7fff9d82021a: " where deptno=30"
0x7fff9d820220: " deptno=30"
0x7fff9d820227: "=30"
0x7fff9d820228: "30"
0x7fff9d82022a: ""
0x7de1bdd6: "DEPTNO"
0x7de1bddc: ""
0x7db9aad6: "DNAME"
0x7db9aadb: ""
0x7db9a9fe: "LOC"
0x7db9aa01: ""
--//你可以看出因為別的會話在cursor_sharing=EXACT執行1次select * from dept where deptno=30;.
--//這樣在cursor_sharing=force的會話執行select * from dept where deptno=30;僅僅分析1組.
--//視窗1:
SCOTT@book> select * from dept where deptno=40;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
--//視窗2看到的輸出:
0x7fff9d820208: "select * from dept where deptno=40"
0x7fff9d82020e: " * from dept where deptno=40"
0x7fff9d820210: " from dept where deptno=40"
0x7fff9d820215: " dept where deptno=40"
0x7fff9d82021a: " where deptno=40"
0x7fff9d820220: " deptno=40"
0x7fff9d820227: "=40"
0x7fff9d820228: "40"
0x7fff9d82022a: ""
0x7de1bdd6: "DEPTNO"
0x7de1bddc: ""
0x7db9aad6: "DNAME"
0x7db9aadb: ""
0x7db9a9fe: "LOC"
0x7db9aa01: ""
--//可以看到跟前面一樣的情況.
--//可以看出cursor_sharing=force的情況下,只要存在文字變數,要呼叫qcplgte 1組,以消耗一定cpu資源為前提的.
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/31547.html
標籤：Oracle
上一篇：oracle體系結構簡介