[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;