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