[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中。