[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语句里面呢,另外写一篇验证这种情况.