[20191122]oracel SQL parsing function qcplgte.txt

[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 <
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:
<
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資源為前提的.