[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资源为前提的.