[20240829]关于依赖链3.txt

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

[20240829]关于依赖链3.txt --//如果修改表结构或者改名,相关的sql语句在共享池会失效,必须重新分析,我开始学习orcle时候,一直认为这些相关信息保存在表对象 --//句柄的堆0里面,如果涉及到的sql语句很多,这样堆0应该很大,而实际上的情况上堆0一直没有变化.我曾经问过别人这个问题,最终无 --//法知道答案,或者解答不能让人满意. --//如果不在表对象句柄的堆0里面,这样应该分散在相关sql语句的对象句柄的堆0里面(我估计是父游标堆0),如果修改表结构或者改名, --//相关sql语句会失效,oracle内部如何操作实现,我一直感觉这个很复杂,也没有人给我讲解.简单探究看看. --//更正在sql语句的子游标堆0里面类型recr的chunk中. 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 mn.txt DECLARE l_count PLS_INTEGER; BEGIN FOR i IN 1..&&1 LOOP EXECUTE IMMEDIATE 'SELECT count(*) FROM dept ,emp WHERE dept.deptno = emp.deptno' into l_count; END LOOP; END; / --//为了测试加入2个表.dept,emp在scott用户下. --//确定sql语句sql_id. $ sql_idz.sh 'SELECT count(*) FROM dept ,emp WHERE dept.deptno = emp.deptno' 0 sql_text = SELECT count(*) FROM dept ,emp WHERE dept.deptno = emp.deptno\0 full_hash_value(16) = 1A481C72A05FBA55B3FEBD346669B6BE or 1a481c72a05fba55b3febd346669b6be hash_value(10) = 1718204094 sql_id(32) = b7zpx6jm6mdpy sql_id(32) = b7zpx6jm6mdpy sql_id(32) = b7zpx6jm6mdpy --//确定sql_id=b7zpx6jm6mdpy,hash_value(10) = 1718204094 2.测试: --//session 1: SCOTT@book01p> @ mn.txt 1e8 --//如果你很快执行完成,增加循环次数. --//session 2: SYS@book> select * from gv$object_dependency where FROM_HASH=1718204094; INST_ID FROM_ADDRESS FROM_HASH TO_OWNER TO_NAME TO_ADDRESS TO_HASH TO_TYPE CON_ID ---------- ---------------- ---------- ---------- -------------------- ---------------- ---------- ---------- ---------- 1 0000000064130C90 1718204094 SCOTT DEPT 0000000064126F80 249266700 2 3 1 0000000064130C90 1718204094 SCOTT EMP 000000006412CBD0 1273316885 2 3 1 0000000064130C90 1718204094 SCOTT 0000000066579048 925293031 255 3 SYS@book> @ v2 gv$object_dependency Show SQL text of views matching "gv$object_dependency"... no rows selected VIEW_NAME TEXT ------------------------------ ---------------------------------------------------------------------------------------------------- GV$OBJECT_DEPENDENCY select d.inst_id,d.kglhdpar, d.kglnahsh, o.kglnaown, o.kglnaobj, o.kglhdadr, o.kglnahsh, o.kglobtyp, d.con_id from x$kglob o, x$kgldp d where o.kglnahsh = d.kglrfhsh and o.kglhdadr = d.kglrfhdl --//关联x$kglob视图,直接查询x$kglob. SYS@book> column KGLDPFGR format a20 SYS@book> select * from x$kgldp where kglnahsh=1718204094 order by KGLDEPNO; ADDR INDX INST_ID CON_ID KGLHDADR KGLHDPAR KGLNAHSH KGLDEPNO KGLRFHDL KGLRFHSH KGLRFFLG KGLDPOBJ KGLDPPOS KGLDPFGR ---------------- ---- ------- ------ ---------------- ---------------- ---------- -------- ---------------- ---------- -------- ---------------- -------- -------- 00007FC158642710 2 1 3 000000006412F530 0000000064130C90 1718204094 0 0000000066579048 925293031 1 000000006412E3F0 0 --//SCOTT 00007FC1586426A8 1 1 3 000000006412F530 0000000064130C90 1718204094 1 000000006412CBD0 1273316885 1 000000006412E3F0 28 --//EMP 00007FC158642640 0 1 3 000000006412F530 0000000064130C90 1718204094 2 0000000064126F80 249266700 1 000000006412E3F0 22 --//DEPT --//根据上面的视图定义,很容易验证相关地址. --//0000000064130C90 sql语句的父游标地址, hash_value=1718204094 --//000000006412F530 sql语句的子游标地址, hash_value=1718204094 --//0000000064126F80 DEPT的handle address, hash_value=249266700 --//000000006412CBD0 EMP的handle address, hash_value=1273316885 --//0000000066579048 SCOTT的handle address,hash_value=925293031 --//0123456789012345678902234567890323456789042345678905234567890612 --//SELECT count(*) FROM dept ,emp WHERE dept.deptno = emp.deptno --///KGLDPPOS = 0 28 22 可以猜测对象在sql语句的偏移量，这个很容易验证。当然KGLDPPOS=0，估计是一个例外。 --//可以简单验证: SYS@book> @ sharepool/shp4x b7zpx6jm6mdpy 0 HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 ---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address 000000006412F530 0000000064130C90 SELECT count(*) FROM dept ,emp WHERE de 1 2 0 000000006412F370 00000000641302B8 8080 16176 3327 27583 27583 1718204094 b7zpx6jm6mdpy 0 parent handle address 0000000064130C90 0000000064130C90 SELECT count(*) FROM dept ,emp WHERE de 1 0 0 0000000064130AD0 00 4064 0 0 4064 4064 1718204094 b7zpx6jm6mdpy 65535 --//parent handle address=0000000064130C90,child handle address=000000006412F530. SYS@book> @ kglob 0 925293031 ============================== INST_ID : 1 OWNER : NAME : SCOTT DB_LINK : NAMESPACE : SCHEMA TYPE : NONE NAMESPACE_NUM : 73 NAMESPACE_HEX : 49 SHARABLE_MEM : 0 LOADS : 0 EXECUTIONS : 0 LOCKS : 2 PINS : 0 KEPT : NO CHILD_LATCH : 55783 INVALIDATIONS : 0 HASH_VALUE : 925293031 LOCK_MODE : SHARED PIN_MODE : NONE STATUS : UNKOWN TIMESTAMP : PREVIOUS_TIMESTAMP : LOCKED_TOTAL : 12 PINNED_TOTAL : 0 PROPERTY : FULL_HASH_VALUE : 46bd45166adbb098d6fef7ce3726d9e7 CON_ID : 3 CON_NAME : BOOK01P ADDR : 0000000066579048 EDITION : SQL_ID : OBJECT_STR : SCOTT PL/SQL procedure successfully completed. --//schema=SCOTT ,HASH_VALUE : 925293031,ADDR: 0000000066579048 等于句柄地址完全对上. --//再来看看KGLDPOBJ=000000006412E3F0. SYS@book> @ fchaz 000000006412E3F0 LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_END --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ----------------- SGA 000000006412E300 1 1 KGLH0^6669b6be 4096 recr 4095 000000006412F370 000000006412F2FF --//看看它位于什么位置,注意KSMCHCOM=KGLH0^6669b6be, 6669b6be = 1718204094 正好是sql语句的hash_value. --//0x000000006412E3F0-0x000000006412E300 = 0xf0 = 240. select x$ksmsp.*,TO_CHAR(TO_NUMBER(KSMCHPTR,'XXXXXXXXXXXXXXXX') + KSMCHSIZ - 1,'FM0XXXXXXXXXXXXXXX') KSMCHPTR_END from x$ksmsp where ksmchpar=hextoraw(lpad(upper('000000006412F370'),16, '0')); ADDR INDX INST_ID CON_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_END ---------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- ----------------- 00007FC158486DD8 159196 1 1 1 1 KGLH0^6669b6be 000000006412E300 4096 recr 4095 000000006412F370 000000006412F2FF ~~~~~~~~~~~~~~~~~ 00007FC1584863B0 159221 1 1 1 1 KGLH0^6669b6be 0000000064123E90 4096 freeabl 0 000000006412F370 0000000064124E8F --//上下对比可以依赖链的相关信息保存在child handle address的堆0的类型KSMCHCLS=recr. SYS@book> @ t TRACEFILE ----------------------------------------------------------------- /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3642.trc SYS@book> oradebug setmypid Statement processed. SYS@book> oradebug peek 0x000000006412E300 4096 1 [06412E300, 06412F300) = 00001001 80B38F00 6412E0D0 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000003 000A0FFF 6412F370 00000000 ... $ egrep "66579048|6412CBD0|64126F80" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3642.trc 06412ECC0 6412CDA8 00000000 66579048 00000000 [...d....H.Wf....] !!!!!!!! 06412ED90 6412CC70 00000000 6412CBD0 00000000 [p..d.......d....] @@@@@@@@ 06412EE00 17ADC348 00000000 6412CBD0 00000000 [H..........d....] 06412EE10 6412CBD0 00000000 00000051 00B38F00 [...d....Q.......] 06412EE30 64127020 00000000 64126F80 00000000 [ p.d.....o.d....] ######## 06412EE70 17ADC348 00000000 64126F80 00000000 [H........o.d....] 06412EE80 64126F80 00000000 00000031 00B38F00 [.o.d....1.......] --//xkgldp.KGLRFHDL的地址信息保存在child handle address的堆0的类型KSMCHCLS=recr. --//0000000066579048 出现1次 scott --//000000006412CBD0 出现3次 emp --//0000000064126F80 出现3次 dept SYS@book> oradebug peek 0x000000006412E3F0 64 1 [06412E3F0, 06412E430) = 6412F530 00000000 6412EAA8 00000000 00000000 00000000 6412F320 00000000 00000000 00000000 00000000 00000000 00000000 00000000 ... --//6412F530 00000000 颠倒 00000000 6412F530 指向子游标句柄地址. --//除了知道sql语句的子游标堆0保存依赖链信息外,还是搞不懂如果某个对象失效,oracle内部如何操作的. --//问题还是集中在oracle如何通过x$kgldp组织起来这些相关信息的. SYS@book> @xind x$kgldp TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION CON_ID ------------------------------ ------------ ------------------------------ --------------- ---------- X$KGLDP 1 KGLNAHSH 0 0

编辑推荐：

返回列表

[20240829]关于依赖链3.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推