[20220216]为什么出现这样的情况.txt

来源：这里教程网时间：2026-03-03 17:28:11 作者：

[20220216]为什么出现这样的情况.txt --//春节放假期间,看来以前一个帖子,很奇怪的发现以前写的链接: --//[20191213]共享池绑定变量的值在哪里.txt=>http://blog.itpub.net/267265/viewspace-2668705/ --//里面查询子光标堆0时,发现多了一个chunk,按照以前测试这里仅仅显示一个chunk,为什么呢? --//我重复测试看看. 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; SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ------------------- ----------- 1906624619 2tftcf9su9k3b 0 51307 1601196873 2022-02-16 09:08:06 16777220 --//执行多次,获取sql_id=2tftcf9su9k3b. SYS@book> @sharepool/shp4 2tftcf9su9k3b 0 KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 --------------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address 0000000084607898 select * from t where name = :v_name 1 0 0 00000000828F51A8 0000000081EADE40 4528 12144 3093 19765 19765 1906624619 2tftcf9su9k3b 0 parent handle address 0000000084607898 select * from t where name = :v_name 1 0 0 00000000828B2658 00 4736 0 0 4736 4736 1906624619 2tftcf9su9k3b 65535 --//注意看KGLOBHS0=4528. --//子光标堆0: SYS@book> @sharepool/shp3 00000000828F51A8 select a.* from x$ksmsp a where a.ksmchptr=hextoraw('00000000828F51A8') no rows selected select a.* from x$ksmsp a where a.ksmchpar=hextoraw('00000000828F51A8') ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007FFD25347D40 6085 1 1 1 KGLH0^71a4c86b 0000000082732E00 4096 recr 4095 00000000828F51A8 --//仅仅1个chunk. SELECT * FROM x$ksmsp WHERE TO_NUMBER ('00000000828F51A8', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1 ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007FFD25340E50 5772 1 1 1 KGLDA 00000000828F5140 240 freeabl 0 00 --//子光标堆6: SYS@book> @sharepool/shp3 0000000081EADE40 select a.* from x$ksmsp a where a.ksmchptr=hextoraw('0000000081EADE40') no rows selected select a.* from x$ksmsp a where a.ksmchpar=hextoraw('0000000081EADE40') ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007FFD25340560 5085 1 1 1 SQLA^71a4c86b 0000000082D82968 4096 freeabl 0 0000000081EADE40 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 00007FFD25340508 5086 1 1 1 SQLA^71a4c86b 0000000082D81968 4096 freeabl 0 0000000081EADE40 00007FFD253370E0 6112 1 1 1 SQLA^71a4c86b 0000000082730E00 4096 recr 4095 0000000081EADE40 --//3个chunk. SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000081EADE40', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1 ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007FFD25315CB8 7379 1 1 1 KGLH0^71a4c86b 0000000081EAD6D0 4096 recr 4095 00000000828B2658 --//子光标堆0下仅仅1个chunk,为什么我前面的测试出现两个chunk.我看了执行计划,发现note部分出现如下: Note ----- - dynamic sampling used for this statement (level=2) - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level --//难道是我没有分析表的原因吗? --//分析表后. SCOTT@book> @ gts t Gather Table Statistics for table t... PL/SQL procedure successfully completed. --//注意:no_invalidate = false,理论讲当收集完统计信息后，收集对象的cursor会立即失效(新的执行计划，新的子游标) SCOTT@book> select * from t where name = :v_name; no rows selected SYS@book> @sharepool/shp4 2tftcf9su9k3b 0 KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 --------------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address 0000000084607898 select * from t where name = :v_name 1 0 1 00000000828F51A8 0000000081EADE40 8600 8088 3093 19781 19781 1906624619 2tftcf9su9k3b 0 parent handle address 0000000084607898 select * from t where name = :v_name 1 0 1 00000000828B2658 00 4736 0 0 4736 4736 1906624619 2tftcf9su9k3b 65535 --//你可以发现分析表后并没有出现新的子光标,但是KGLOBHS0=8600.注意看子光标堆0,堆6的描述符地址还是00000000828F51A8,0000000081EADE40 --//子光标堆0: SYS@book> @sharepool/shp3 00000000828F51A8 select a.* from x$ksmsp a where a.ksmchptr=hextoraw('00000000828F51A8') no rows selected select a.* from x$ksmsp a where a.ksmchpar=hextoraw('00000000828F51A8') ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007FFD253402F8 6578 1 1 1 KGLH0^71a4c86b 0000000082D82968 4096 freeabl 0 00000000828F51A8 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 00007FFD253274A8 8277 1 1 1 KGLH0^71a4c86b 0000000082732E00 4096 recr 4095 00000000828F51A8 --//变成了2个chunk. SELECT * FROM x$ksmsp WHERE TO_NUMBER ('00000000828F51A8', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1 ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007FFD2531C978 7754 1 1 1 KGLDA 00000000828F5140 240 freeabl 0 00 --//子光标堆6: SYS@book> @sharepool/shp3 0000000081EADE40 select a.* from x$ksmsp a where a.ksmchptr=hextoraw('0000000081EADE40') no rows selected select a.* from x$ksmsp a where a.ksmchpar=hextoraw('0000000081EADE40') ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007FFD2533CA80 6681 1 1 1 SQLA^71a4c86b 0000000082D81968 4096 freeabl 0 0000000081EADE40 00007FFD25324880 8437 1 1 1 SQLA^71a4c86b 0000000082730E00 4096 recr 4095 0000000081EADE40 --//2个chunk.比原来少了1个chunk. SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000081EADE40', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1 ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007FFD253C9050 10392 1 1 1 KGLH0^71a4c86b 0000000081EAD6D0 4096 recr 4095 00000000828B2658 --//仔细看下划线的地址KSMCHPTR=0000000082D82968,对比前面可以发现其中1个chunk是从堆6下移动到堆0.(注使用+++++标识) --//至于为什么出现这样的情况我不知道. --//附上相关测试脚本: $ cat sharepool/shp4.sql column N0_6_16 format 99999999 SELECT DECODE (kglhdadr, kglhdpar, 'parent handle address', 'child handle address') -- 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; $ cat sharepool/shp3.sql prompt prompt select a.* from x$ksmsp a where a.ksmchptr=hextoraw('&1'); prompt --select a.*,dump(a.KSMCHCOM,16) c60 from x$ksmsp a where a.ksmchptr=hextoraw('&1'); select a.* from x$ksmsp a where a.ksmchptr=hextoraw('&1'); prompt prompt select a.* from x$ksmsp a where a.ksmchpar=hextoraw('&&1'); prompt --select a.*,dump(a.KSMCHCOM,16) c60 from x$ksmsp a where a.ksmchpar=hextoraw('&&1'); select a.* from x$ksmsp a where a.ksmchpar=hextoraw('&&1'); --select * from x$ksmsp where ksmchptr in ( --SELECT x -- FROM (SELECT a.ksmchptr, lag (a.ksmchptr, 1) OVER (ORDER BY a.ksmchptr) x -- FROM x$ksmsp a ) -- WHERE '&1' between x and ksmchptr); -- oracle do not support raw compare --SELECT * FROM x$ksmsp WHERE '&1' between KSMCHPTR and TO_CHAR(TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ,'0xxxxxxxxxxxxxxx'); prompt prompt SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1; prompt SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1; $ cat tpt/gts.sql -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. prompt Gather Table Statistics for table &1.... exec dbms_stats.gather_table_stats(null, upper('&1'), null, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false); --//注意:no_invalidate = false.