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