[20191125]oracel SQL parsing function qcplgte 2.txt「建议收藏」

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说[20191125]oracel SQL parsing function qcplgte 2.txt「建议收藏」,希望您对编程的造诣更进一步.

[20191125]oracel SQL parsing function qcplgte 2.txt

–//参考前面的测试:http://blog.itpub.net/267265/viewspace-2665273/=>[20191122]oracel SQL parsing function qcplgte.txt
–//补充一些测试:

1.环境:
–//session 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

SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
———- ———- ———————— ——— —— ——- ———- ————————————————–
86 15 964 DEDICATED 965 30 8 alter system kill session “86,15” immediate;

SCOTT@book> alter session set cursor_sharing=force ;
Session altered.

2.测试:
–//session 1:
SCOTT@book> select * from dept where deptno=41;
no rows selected

–//session 2:
0x7fff1e373528: “select * from dept where deptno=41”
0x7fff1e37352e: ” * from dept where deptno=41″
0x7fff1e373530: ” from dept where deptno=41″
0x7fff1e373535: ” dept where deptno=41″
0x7fff1e37353a: ” where deptno=41″
0x7fff1e373540: ” deptno=41″
0x7fff1e373547: “=41”
0x7fff1e373548: “41”
0x7fff1e37354a: “”
0x7db65d78:      “select * from dept where deptno=:”SYS_B_0″”
0x7db65d7e:      ” * from dept where deptno=:”SYS_B_0″”
0x7db65d80:      ” from dept where deptno=:”SYS_B_0″”
0x7db65d85:      ” dept where deptno=:”SYS_B_0″”
0x7db65d8a:      ” where deptno=:”SYS_B_0″”
0x7db65d90:      ” deptno=:”SYS_B_0″”
0x7db65d97:      “=:”SYS_B_0″”
0x7db65d91:      “deptno=:”SYS_B_0″”
0x7db65d97:      “=:”SYS_B_0″”
0x7db65d98:      “:”SYS_B_0″”
0x7db65d99:      “”SYS_B_0″”
0x7db65da2:      “”
0x7f6fca73d9f0: “select * from dept where deptno=:”SYS_B_0″”
0x7f6fca73d9f6: ” * from dept where deptno=:”SYS_B_0″”
0x7f6fca73d9f8: ” from dept where deptno=:”SYS_B_0″”
0x7f6fca73d9fd: ” dept where deptno=:”SYS_B_0″”
0x7f6fca73da02: ” where deptno=:”SYS_B_0″”
0x7f6fca73da08: ” deptno=:”SYS_B_0″”
0x7f6fca73da0f: “=:”SYS_B_0″”
0x7f6fca73da10: “:”SYS_B_0″”
0x7f6fca73da11: “”SYS_B_0″”
0x7f6fca73da1a: “”
0x7f6fca73d978: “SELECT * FROM DEPT WHERE DEPTNO = :”SYS_B_0″”
0x7f6fca73d97e: ” * FROM DEPT WHERE DEPTNO = :”SYS_B_0″”
0x7f6fca73d980: ” FROM DEPT WHERE DEPTNO = :”SYS_B_0″”
0x7f6fca73d985: ” DEPT WHERE DEPTNO = :”SYS_B_0″”
0x7f6fca73d98a: ” WHERE DEPTNO = :”SYS_B_0″”
0x7f6fca73d990: ” DEPTNO = :”SYS_B_0″”
0x7f6fca73d997: ” = :”SYS_B_0″”
0x7f6fca73d999: ” :”SYS_B_0″”
0x7f6fca73d99b: “”SYS_B_0″”
0x7cc6fb76:      “DEPTNO”
0x7cc6fb7c:      “”
0x7c7b8bfe:      “DNAME”
0x7c7b8c03:      “”
0x7c7b8b26:      “LOC”
0x7c7b8b29:      “”

–//session 1:
SCOTT@book> select * from dept where deptno=42;
no rows selected

–//session 2:
0x7fff1e373528: “select * from dept where deptno=42”
0x7fff1e37352e: ” * from dept where deptno=42″
0x7fff1e373530: ” from dept where deptno=42″
0x7fff1e373535: ” dept where deptno=42″
0x7fff1e37353a: ” where deptno=42″
0x7fff1e373540: ” deptno=42″
0x7fff1e373547: “=42”
0x7fff1e373548: “42”
0x7fff1e37354a: “”
0x7cc6fb76:      “DEPTNO”
0x7cc6fb7c:      “”
0x7c7b8bfe:      “DNAME”
0x7c7b8c03:      “”
0x7c7b8b26:      “LOC”
0x7c7b8b29:      “”

–//session 1:
SCOTT@book> select * from dept where deptno=41;
no rows selected

–//session 2:
0x7cc6fb76:      “DEPTNO”
0x7cc6fb7c:      “”
0x7c7b8bfe:      “DNAME”
0x7c7b8c03:      “”
0x7c7b8b26:      “LOC”
0x7c7b8b29:      “”

–//session 1:
–//执行多次.
SCOTT@book> select * from dept where deptno=41;
no rows selected

–//session 2:
0x7cc6fb76:      “DEPTNO”
0x7cc6fb7c:      “”
0x7c7b8bfe:      “DNAME”
0x7c7b8c03:      “”
0x7c7b8b26:      “LOC”
0x7c7b8b29:      “”

–//在cursor_sharing=force的情况,如果绑定变量参数不同才会调用1组qcplgte.不过里面的字段不知道为什么每次都要调用.
–//即使是明确字段的情况下也是一样.
–//session 1:
SCOTT@book> select dname from dept where deptno=41;
no rows selected

–//session 1 调用多次select dname from dept where deptno=41;

–//session 2:

0x7fff1e373528: “select dname from dept where deptno=41”
0x7fff1e37352e: ” dname from dept where deptno=41″
0x7fff1e373534: ” from dept where deptno=41″
0x7fff1e373539: ” dept where deptno=41″
0x7fff1e37353e: ” where deptno=41″
0x7fff1e373544: ” deptno=41″
0x7fff1e37354b: “=41”
0x7fff1e37354c: “41”
0x7fff1e37354e: “”
0x7c8622f8:      “select dname from dept where deptno=:”SYS_B_0″”
0x7c8622fe:      ” dname from dept where deptno=:”SYS_B_0″”
0x7c862304:      ” from dept where deptno=:”SYS_B_0″”
0x7c8622ff:      “dname from dept where deptno=:”SYS_B_0″”
0x7c862304:      ” from dept where deptno=:”SYS_B_0″”
0x7c8622ff:      “dname from dept where deptno=:”SYS_B_0″”
0x7c862304:      ” from dept where deptno=:”SYS_B_0″”
0x7c862309:      ” dept where deptno=:”SYS_B_0″”
0x7c86230e:      ” where deptno=:”SYS_B_0″”
0x7c862314:      ” deptno=:”SYS_B_0″”
0x7c86231b:      “=:”SYS_B_0″”
0x7c862315:      “deptno=:”SYS_B_0″”
0x7c86231b:      “=:”SYS_B_0″”
0x7c86231c:      “:”SYS_B_0″”
0x7c86231d:      “”SYS_B_0″”
0x7c862326:      “”
0x7f6fca73d9f8: “select dname from dept where deptno=:”SYS_B_0″”
0x7f6fca73d9fe: ” dname from dept where deptno=:”SYS_B_0″”
0x7f6fca73da04: ” from dept where deptno=:”SYS_B_0″”
0x7f6fca73da09: ” dept where deptno=:”SYS_B_0″”
0x7f6fca73da0e: ” where deptno=:”SYS_B_0″”
0x7f6fca73da14: ” deptno=:”SYS_B_0″”
0x7f6fca73da1b: “=:”SYS_B_0″”
0x7f6fca73da1c: “:”SYS_B_0″”
0x7f6fca73da1d: “”SYS_B_0″”
0x7f6fca73da26: “”
0x7f6fca73d978: “SELECT DNAME FROM DEPT WHERE DEPTNO = :”SYS_B_0″”
0x7f6fca73d97e: ” DNAME FROM DEPT WHERE DEPTNO = :”SYS_B_0″”
0x7f6fca73d984: ” FROM DEPT WHERE DEPTNO = :”SYS_B_0″”
0x7f6fca73d989: ” DEPT WHERE DEPTNO = :”SYS_B_0″”
0x7f6fca73d98e: ” WHERE DEPTNO = :”SYS_B_0″”
0x7f6fca73d994: ” DEPTNO = :”SYS_B_0″”
0x7f6fca73d99b: ” = :”SYS_B_0″”
0x7f6fca73d99d: ” :”SYS_B_0″”
0x7f6fca73d99f: “”SYS_B_0″”
0x7c11fd1e:      “DNAME”
0x7c11fd23:      “”