[20240829]关于依赖链.txt

[20240829]关于依赖链.txt --//如果修改表结构或者改名,相关的sql语句在共享池会失效,必须重新分析,我开始学习orcle时候,一直认为这些相关信息保存在表对象 --//句柄的堆0里面,如果涉及到的sql语句很多,这样堆0应该很大,而实际上的情况上堆0一直没有变化.我曾经问过别人这个问题,最终无 --//法知道答案,或者解答不能让人满意. --//如果不在表对象句柄的堆0里面,这样应该分散在相关sql语句的对象句柄的堆0里面(我估计是父游标堆0),如果修改表结构或者改名, --//相关语句会失效,oracle内部如何操作实现,我一直感觉这个很复杂,也没有人给我讲解.简单探究看看. --//更正:实际上在子游标堆0 1.环境: SCOTT@book01p> @ ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. $ cat mm.txt DECLARE     l_count PLS_INTEGER; BEGIN     FOR i IN 1..&&1     LOOP        EXECUTE IMMEDIATE 'SELECT count(*)  FROM dept WHERE deptno = '||i into l_count;     END LOOP; END; / --//确定表对象的hash_value.  $ sql_idz.sh 'DEPT.SCOTT.BOOK01P\001\0\0' 0 sql_text = DEPT.SCOTT.BOOK01P\001\0\0\0 full_hash_value(16) = 05DB243908B3C797B99628590EDB820C or 05db243908b3c797b99628590edb820c hash_value(10) = 249266700 sql_id(32) = bm5j8b47dr0hc sql_id(32) = bm5j8b47dr0hc sql_id(32) = bm5j8b47dr0hc --//hash_value(10) = 249266700 2.测试: --//session 1: SCOTT@book01p> @ mm.txt 1e6 --//session 2: SYS@book> @ sharepool/shp4x 0 249266700 SYS@book> @pr ============================== HANDLE_TYPE                   : parent handle address KGLHDADR                      : 000000006CA2CE78 KGLHDPAR                      : 000000006CA2CE78 C40                           : SCOTT.DEPT KGLHDLMD                      : 2 KGLHDPMD                      : 2 KGLHDIVC                      : 0 KGLOBHD0                      : 000000006BEE94B0 KGLOBHD6                      : 00 KGLOBHS0                      : 4064 KGLOBHS6                      : 0 KGLOBT16                      : 0 N0_6_16                       : 4064 N20                           : 4064 KGLNAHSH                      : 249266700 KGLOBT03                      : KGLOBT09                      : 0 PL/SQL procedure successfully completed. --//不管执行,KGLHDLMD=2,KGLHDPMD=2,前者lock,后者pin. --//注:2表示共享模式 3表示排他模式 1表示NULL模式. select * from x$ksmsp where ksmchpar=hextoraw(lpad(upper('000000006BEE94B0'),         16, '0')); ADDR                   INDX    INST_ID     CON_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007F1E36924D80     135230          1          1          1          1 KGLH0^edb820c    000000006BEF6850       4096 recr           4095 000000006BEE94B0 -//可以相关表对象的句柄的堆0 chunk size还是4096,并没有变化. SYS@book> column TO_NAME format a30 SYS@book> select * from gv$object_dependency where to_name='DEPT' and rownum<=10;    INST_ID FROM_ADDRESS      FROM_HASH TO_OWNER TO_NAME TO_ADDRESS          TO_HASH    TO_TYPE     CON_ID ---------- ---------------- ---------- -------- ------- ---------------- ---------- ---------- ----------          1 00000000622E6E80 1646526486 SCOTT    DEPT    000000006CA2CE78  249266700          2          3          1 000000006E1EB9A0  336330891 SCOTT    DEPT    000000006CA2CE78  249266700          2          3          1 000000006C6C6B60 3092512987 SCOTT    DEPT    000000006CA2CE78  249266700          2          3          1 00000000612C8B10 2576744745 SCOTT    DEPT    000000006CA2CE78  249266700          2          3          1 000000006CB06A38 2169242469 SCOTT    DEPT    000000006CA2CE78  249266700          2          3          1 0000000062D7BA38  346686460 SCOTT    DEPT    000000006CA2CE78  249266700          2          3          1 000000006A53A570 3590849582 SCOTT    DEPT    000000006CA2CE78  249266700          2          3          1 000000006BD74918  977012134 SCOTT    DEPT    000000006CA2CE78  249266700          2          3 ~~~~~~~~~~~~~~~~~~~~~~~                  1 000000006CBB0AC8  583140822 SCOTT    DEPT    000000006CA2CE78  249266700          2          3          1 000000006AD648A8 1894909508 SCOTT    DEPT    000000006CA2CE78  249266700          2          3 10 rows selected. --//输出太多,仅仅取了10行记录. SYS@book> select count(*) from gv$object_dependency where to_name='DEPT' ;   COUNT(*) ----------        785 --//FROM_ADDRESS 地址对应就是相应sql语句的父游标句柄. SYS@book> @ sharepool/shp4 000000006BD74918 0 HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 ---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address   000000006E7AC980 000000006BD74918 SELECT count(*)  FROM dept WHERE deptno           0          0          0 000000006A654550 0000000066355AC0       8080      12128       3313     23521      23521 2645750751 d5du9zffv5wyz          0 parent handle address  000000006BD74918 000000006BD74918 SELECT count(*)  FROM dept WHERE deptno           0          0          0 0000000069F66910 00                     4064          0          0      4064       4064 2645750751 d5du9zffv5wyz      65535 --//KGLNAHSH与前面的不一致，估计查询时前面的已经清除了。 --//注意看TO_ADDRESS不变. SYS@book> @ sharepool/shp4 000000006CA2CE78 0 SYS@book> @pr ============================== HANDLE_TYPE                   : parent handle address KGLHDADR                      : 000000006CA2CE78 KGLHDPAR                      : 000000006CA2CE78 C40                           : SCOTT.DEPT KGLHDLMD                      : 0 KGLHDPMD                      : 0 KGLHDIVC                      : 0 KGLOBHD0                      : 000000006BEE94B0 KGLOBHD6                      : 00 KGLOBHS0                      : 4064 KGLOBHS6                      : 0 KGLOBT16                      : 0 N0_6_16                       : 4064 N20                           : 4064 KGLNAHSH                      : 249266700 KGLOBT03                      : KGLOBT09                      : 0 PL/SQL procedure successfully completed. --//执行表对象的父游标。 总结: --//很明显这些依赖信息应该不在表对象的堆0里面,不然堆0应该占用chunk变大. --//那么是否分散在各个相关sql语句里面呢,另外写一篇验证这种情况.