[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