[20191213]共享池繫結變數的值在哪裡.txt

[20191213]共享池繫結變數的值在哪裡.txt

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

create table t as select rownum id ,lpad('x',100,'x') name ,lpad('1',1) flag from dual connect by level<=1e5;

2.測試:
--//session 1:
SCOTT@book> variable v_name varchar2(100)
SCOTT@book> exec :v_name :='987654321'
PL/SQL procedure successfully completed.

select * from t where name = :v_name;
select * from t where name = :v_name;
select * from t where name = :v_name;
select * from t where name = :v_name;
select * from t where name = :v_name;
select * from t where name = :v_name;
--//執行多次,獲取sql_id=2tftcf9su9k3b.

3.觀察:
--//session 2:
SYS@book> @ sharepool/shp4 2tftcf9su9k3b 0
old  20:  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new  20:  WHERE kglobt03 = '2tftcf9su9k3b'  or kglhdpar='2tftcf9su9k3b' or kglhdadr='2tftcf9su9k3b' or KGLNAHSH= 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 000000007E2E15A0 000000007BC3C6C0 select * from t where name = :v_name              1          0          0 000000007DEC0790 000000007D2A9800       8600       8088       3093     19781      19781 1906624619 2tftcf9su9k3b          0
父遊標控制程式碼地址 000000007BC3C6C0 000000007BC3C6C0 select * from t where name = :v_name              1          0          0 000000007C8B3CD8 00                     4720          0          0      4720       4720 1906624619 2tftcf9su9k3b      65535

--//猜測繫結變數值在子游標控制程式碼的堆0中.

select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007DEC0790')
old   1: select a.* from x$ksmsp a where a.ksmchpar=hextoraw('&&1')
new   1: select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007DEC0790')
ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FB13FD49300       3486          1          1          1 KGLH0^71a4c86b   000000007DFC2800       4096 recr           4095 000000007DEC0790
00007FB13FE54FE0      19180          1          1          1 KGLH0^71a4c86b   000000007C13D3D8       4096 freeabl           0 000000007DEC0790

--//在peek 000000007DFC2800開始處看看.

SYS@book> oradebug setmypid
Statement processed.

SYS@book> oradebug peek 0x000000007DFC2800 4096 1
[07DFC2800, 07DFC3800) = 00001001 80B38F00 7DFC1800 00000000 7BCEF990 00000000 7C05EF68 00000000 00000000 00000000 00000001 C00E0FFF 7DEC0790 00000000 ...

SYS@book> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_27540.trc
--//檢查/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_27540.trc跟蹤檔案,發現:

07DFC2FE0 7DFC3010 00000000 7DEC0790 00000000  [.0.}.......}....]
07DFC2FF0 00000001 00000001 00000080 00300030  [............0.0.]
07DFC3000 0A3D083C 00000000 00000000 00000000  [<.=.............]
07DFC3010 7DFC3018 00000000 00000301 00000080  [.0.}............]
07DFC3020 00000000 01000000 00000000 00000000  [................]
07DFC3030 00000000 00010354 00000000 00000000  [....T...........]
07DFC3040 00000080 00000000 7DFC3078 00000000  [........x0.}....]
07DFC3050 7DEC0790 00000000 00000001 00000001  [...}............]
07DFC3060 00000080 00310010 0A27037C 00000000  [......1.|.'.....]
07DFC3070 00000000 00000000 7DFC3080 00000000  [.........0.}....]
07DFC3080 0101FFFF 00090000 00000000 00000000  [................]
07DFC3090 7DFC30C0 00000000 7DEC0790 00000000  [.0.}.......}....]
07DFC30A0 00000002 00000002 000000AA 00300018  [..............0.]
07DFC30B0 0A271C14 00000000 00000000 00000000  [..'.............]
07DFC30C0 7DFC30C8 00000000 00000000 00000000  [.0.}............]
07DFC30D0 7DFC30F8 00000000 00000009 00000000  [.0.}............]
07DFC30E0 00000000 00000000 00000000 00000000  [................]
07DFC30F0 00000000 00000000 36373839 32333435  [........98765432]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
07DFC3100 00000031 00000000 7C13DAA0 00000000  [1..........|....]
07DFC3110 00010000 00000000 7C13D430 00000000  [........0..|....]
07DFC3120 7DFC3150 00000000 7C13DA80 00000000  [P1.}.......|....]
07DFC3130 00000000 00000000 00000006 00000000  [................]
07DFC3140 00000000 00000000 00000001 00000000  [................]
07DFC3150 00000006 00000000 00000000 00000000  [................]
07DFC3160 00000000 00000000 7C13D6A0 00000000  [...........|....]
07DFC3170 7C13D830 00000000 7C13D420 00000000  [0..|.... ..|....]
07DFC3180 7C13D420 00000000 7DEC0790 00000000  [ ..|.......}....]
07DFC3190 7DEC0790 00000000 09F47724 00000000  [...}....$w......]
07DFC31A0 00000400 00000000 00000361 C0B38F00  [........a.......]
07DFC31B0 7DFC2890 00000000 7DEC0808 00000000  [.(.}.......}....]
07DFC31C0 7DFC2870 00000000 00000000 00000000  [p(.}............]
07DFC31D0 00000000 00000000 00000000 00000000  [................]

--//換1個變數執行看看.
--//session 1:
SCOTT@book> exec :v_name :='zzzzz987654321'
PL/SQL procedure successfully completed.

SCOTT@book> select * from t where name = :v_name;
no rows selected

SCOTT@book> select * from t where name = :v_name;
no rows selected

SCOTT@book> select * from t where name = :v_name;
no rows selected

--//session 2:
SYS@book> oradebug peek 0x000000007DFC2800 4096 1
[07DFC2800, 07DFC3800) = 00001001 80B38F00 7DFC1800 00000000 7BCEF990 00000000 7C05EF68 00000000 00000000 00000000 00000001 C00E0FFF 7DEC0790 00000000 ...

07DFC30E0 00000000 00000000 00000000 00000000  [................]
07DFC30F0 00000000 00000000 36373839 32333435  [........98765432]
07DFC3100 00000031 00000000 7C13DAA0 00000000  [1..........|....]
07DFC3110 00010000 00000000 7C13D430 00000000  [........0..|....]

--//可以僅僅第1次執行放入子游標的堆0中.

SYS@book> oradebug peek 0x000000007C13D3D8 4096 1
[07C13D3D8, 07C13E3D8) = 00001001 00B38F00 7C13C3D8 00000000 6005A8F0 00000000 7DEC0790 00000000 7DFC2830 00000000 00000FD9 50B38F00 00000000 00000000 ...

$ grep 987 /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_27540.trc
07DFC30F0 00000000 00000000 36373839 32333435  [........98765432]
07DFC30F0 00000000 00000000 36373839 32333435  [........98765432]
07DFC30F0 00000000 00000000 36373839 32333435  [........98765432]
07C13DB90 00000000 00000000 36373839 32333435  [........98765432]

--//基本可以確定繫結變數的值在子游標的堆0裡面.

4.附上指令碼:
$ cat shp4.sql
column N0_6_16 format 99999999
SELECT DECODE (kglhdadr,
               kglhdpar, '父遊標控制程式碼地址',
               '子游標控制程式碼地址')
          text,
       kglhdadr,
       kglhdpar,
       substr(kglnaobj,1,40) c40,
           KGLHDLMD,
           KGLHDPMD,
           kglhdivc,
       kglobhd0,
       kglobhd6,
       kglobhs0,kglobhs6,kglobt16,
       kglobhs0+kglobhs6+kglobt16 N0_6_16,
           kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
           kglnahsh,
           kglobt03 ,
           kglobt09
  FROM x$kglob
 WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;