[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:      ""

0x7c11fd1e:      "DNAME"
0x7c11fd23:      ""

0x7c11fd1e:      "DNAME"
0x7c11fd23:      ""

0x7c11fd1e:      "DNAME"
0x7c11fd23:      ""