[20191216]共享池绑定变量的值在哪里2.txt

[20191216]共享池绑定变量的值在哪里2.txt --//上个星期的测试,链接:http://blog.itpub.net/267265/viewspace-2668705/=>[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 TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 子游标句柄地址 000000007BF11478 000000007BDD5330 select * from t where name = :v_name              1          0          0 000000007C4CB808 000000007C6B1F60       8600       8088       3093     19781      19781 1906624619 2tftcf9su9k3b          0 父游标句柄地址 000000007BDD5330 000000007BDD5330 select * from t where name = :v_name              1          0          0 000000007E2EC768 00                     4720          0          0      4720       4720 1906624619 2tftcf9su9k3b      65535 --//猜测绑定变量值在子游标句柄的堆0中. select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007C4CB808') 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('000000007C4CB808') ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007FA770A14858       2762          1          1          1 KGLH0^71a4c86b   000000007E1856D0       4096 freeabl           0 000000007C4CB808 00007FA770B653F8      15931          1          1          1 KGLH0^71a4c86b   000000007C6B07F0       4096 recr           4095 000000007C4CB808 --//前面的测试已经发现绑定变量保存在子光标堆0,仅仅第一次传入参数才保存在此处.建立新的子光标看看. --//session 1: SCOTT@book> variable v_name varchar2(2000) SCOTT@book> exec :v_name :='zzzzbbbbbbbbbbbbbbbb987654321aaaa' 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; --//session 2: SYS@book> @ sharepool/shp4 2tftcf9su9k3b 0 TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 子游标句柄地址 000000007BF11478 000000007BDD5330 select * from t where name = :v_name              0          0          0 000000007C4CB808 000000007C6B1F60       8600       8088       3997     20685      20685 1906624619 2tftcf9su9k3b          0 子游标句柄地址 000000007CDC4870 000000007BDD5330 select * from t where name = :v_name              1          0          0 000000007D1673C0 000000007C6B23C8       8600       8088       3997     20685      20685 1906624619 2tftcf9su9k3b          1 父游标句柄地址 000000007BDD5330 000000007BDD5330 select * from t where name = :v_name              1          0          0 000000007E2EC768 00                     4720          0          0      4720       4720 1906624619 2tftcf9su9k3b      65535 select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007D1673C0') ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007F4BACD74648        968          1          1          1 KGLH0^71a4c86b   000000007EA317D8       4096 recr           4095 000000007D1673C0 00007F4BACEE46A0      12697          1          1          1 KGLH0^71a4c86b   000000007CE01268       4096 freeabl           0 000000007D1673C0 00007F4BACED17B8      13245          1          1          1 KGLH0^71a4c86b   000000007CCEAB80       4096 freeabl           0 000000007D1673C0 --//在peek 000000007EA317D8开始处看看. SYS@book> oradebug setmypid Statement processed. SYS@book> oradebug peek 0x000000007EA317D8 4096 1 [07D1673C0, 07D1683C0) = 60001190 00000000 00000FE8 00000000 7D167370 00000000 7CCEAB98 00000000 7EA32198 00000000 00000000 00000000 00000000 00000000 ... SYS@book> oradebug tracefile_name /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_54395.trc --//检查/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_54395.trc跟踪文件,发现: 07EA320A0 00000000 00000000 7EA320D0 00000000  [......... .~....] 07EA320B0 00000021 00000000 00000000 00000000  [!...............] 07EA320C0 00000000 00000000 00000000 00000000  [................] 07EA320D0 7A7A7A7A 62626262 62626262 62626262  [zzzzbbbbbbbbbbbb] 07EA320E0 62626262 36373839 32333435 61616131  [bbbb987654321aaa] 07EA320F0 00000061 00000000 7CE01930 00000000  [a.......0..|....] 07EA32100 00010000 00000001 7CE012C0 00000000  [...........|....] 07EA32110 7EA32140 00000000 7CE01910 00000000  [@!.~.......|....] 07EA32120 00000000 00000000 0000000A 00000000  [................] --//再次验证我的判断. SYS@book> oradebug peek 0x000000007CE01268 4096 1 [07CE01268, 07CE02268) = 00001001 00B38F00 7CE011A0 00000000 6005A8F0 00000000 7D1673C0 00000000 7EA31808 00000000 00000F69 40B38F00 00000000 00000000 ... SYS@book> oradebug peek 0x000000007CCEAB80 4096 1 [07CCEAB80, 07CCEBB80) = 00001001 00B38F00 7CCEAA78 00000000 6005A8F0 00000000 7D1673C0 00000000 7CE01280 00000000 00000F41 C0B38F00 00000000 00000000 ... $ grep zzzz /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_54395.trc 07EA320D0 7A7A7A7A 62626262 62626262 62626262  [zzzzbbbbbbbbbbbb] 07CE01A20 00000000 00000000 7A7A7A7A 62626262  [........zzzzbbbb] --//检查/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_54395.trc跟踪文件,发现: 07CE01A20 00000000 00000000 7A7A7A7A 62626262  [........zzzzbbbb] 07CE01A30 62626262 62626262 62626262 36373839  [bbbbbbbbbbbb9876] 07CE01A40 32333435 61616131 00000061 00000000  [54321aaaa.......] 07CE01A50 00000000 00000000 00000000 00000000  [................]         Repeat 2 times --//在0x000000007CE01268处长度4096哪里也有. 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;