[20160202]dblink与父子游标.txt

--昨天遇到1一个bug，在10g下，如果设置参数cursor_sharing=force的情况下，如果访问远程表带文字变量，转成带参后，出现大量子
--光标的问题。链接如下：
http://blog.itpub.net/267265/viewspace-1985215/

--今天看看这样的sql语句在本地的共享池情况。

1.环境：
SCOTT@book> @ &r/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 PUBLIC DATABASE LINK loopback USING 'localhost:1521/book';

2.测试：

SCOTT@book> select * from dept@loopback where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ &r/dpcz ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  d34tsffmd6tgv, child number 0

select * from dept@loopback where deptno=10

NOTE: cannot fetch plan for SQL_ID: d34tsffmd6tgv, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)
8 rows selected.

--如果查询执行计划会出现如上的错误，实际远端执行如下。

SELECT "A1"."DEPTNO", "A1"."DNAME", "A1"."LOC"
  FROM "DEPT" "A1"
WHERE "A1"."DEPTNO" = 10;

3.观察父游标情况：

SYS@book> @ &r/sharepool/shp4 d34tsffmd6tgv

TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16  N0_6_16        N20   KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游标句柄地址 0000000066F47FC0 0000000066F48350 select * from dept@loopback where deptno 0000000062652750 0000000069D196B0       4488       8088       3076    15652      15652 2798872059 d34tsffmd6tgv
父游标句柄地址 0000000066F48350 0000000066F48350 select * from dept@loopback where deptno 0000000066E87EA0 00                     4736          0          0     4736       4736 2798872059 d34tsffmd6tgv

--父游标:
new   1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000066F48350', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FF13D33D7B8      58041          1          1          2 KGLHD            0000000066F48320        576 recr             80 00

--父游标堆0：
new   1: select a.*,dump(a.KSMCHCOM,16) c60 from x$ksmsp a where a.ksmchpar='0000000066E87EA0'
ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR        
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FF13D020F38      31891          1          1          3 KGLH0^a6d365fb   0000000069D18F40       4096 recr           4095 0000000066E87EA0

--父游标堆0的DS描述符:
new   1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000066E87EA0', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FF13D303D50      59585          1          1          2 KGLDA            0000000066E87E38        240 freeabl           0 00

4.观察子游标情况：
--子游标:
new   1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000066F47FC0', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FF13D33EE00      58022          1          1          2 KGLHD            0000000066F47F90        368 recr             80 00

--子游标堆0:
new   1: select a.*,dump(a.KSMCHCOM,16) c60 from x$ksmsp a where a.ksmchpar='0000000062652750'
ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR        
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FF13D020EE0      31892          1          1          3 KGLH0^a6d365fb   0000000069D17F40       4096 recr           4095 0000000062652750

--子游标堆0的DS描述符:
new   1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000062652750', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FF13D1FE6C8      44243          1          1          2 KGLDA            00000000626526E8        240 freeabl           0 00

--子游标堆6:
new   1: select a.*,dump(a.KSMCHCOM,16) c60 from x$ksmsp a where a.ksmchpar='0000000069D196B0'
ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR        
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FF13D1AA248      14401          1          1          4 SQLA^a6d365fb    00000000688D7CF0       4096 recr           4095 0000000069D196B0
00007FF13D1AA1F0      14402          1          1          4 SQLA^a6d365fb    00000000688D6CF0       4096 freeabl           0 0000000069D196B0

--可以发现比访问本地表少1个chunk。

--子游标堆6的DS描述符，可以发现在父游标堆0中。
new   1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000069D196B0', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FF13D020F38      31891          1          1          3 KGLH0^a6d365fb   0000000069D18F40       4096 recr           4095 0000000066E87EA0

--除了堆6少1个chunk，其他基本一样，估计就是少了执行计划。

select * from dept@loopback,dual where deptno=10;

sql_id=9bwxnfwkzrar2

SYS@book> @ &r/sharepool/shp4 9bwxnfwkzrar2
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16  N0_6_16        N20   KGLNAHSH KGLOBT03
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------- ---------- ---------- -------------
子游标句柄地址 0000000064655F68 0000000066ED19D8 select * from dept@loopback,dual where d 00000000852BEBA8 0000000063721770       4504      12144       3081    19729      19729  637250274 9bwxnfwkzrar2
父游标句柄地址 0000000066ED19D8 0000000066ED19D8 select * from dept@loopback,dual where d 000000007C0810A0 00                     4736          0          0     4736       4736  637250274 9bwxnfwkzrar2

SYS@book> @ &r/sharepool/shp3 0000000063721770
select a.* from x$ksmsp a where a.ksmchptr='0000000063721770'
no rows selected

select a.* from x$ksmsp a where a.ksmchpar='0000000063721770'

ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         C60
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- ------------------------------------------------------------
00007FF13CED99C0        446          1          1          4 SQLA^25fbaae2    000000007CA52738       4096 recr           4095 0000000063721770 Typ=1 Len=13: 53,51,4c,41,5e,32,35,66,62,61,61,65,32
00007FF13CE614A8       6351          1          1          4 SQLA^25fbaae2    000000006535E000       4096 freeabl           0 0000000063721770 Typ=1 Len=13: 53,51,4c,41,5e,32,35,66,62,61,61,65,32
00007FF13CE2E300       8287          1          1          4 SQLA^25fbaae2    0000000065BED000       4096 freeabl           0 0000000063721770 Typ=1 Len=13: 53,51,4c,41,5e,32,35,66,62,61,61,65,32

ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FF13D0C0D28      24220          1          1          3 KGLH0^25fbaae2   0000000063721000       4096 recr           4095 000000007C0810A0

--可以看出能看到执行计划的在堆6中多1个chunk，估计执行计划就在那个chunk中。