[20240829]关于依赖链3.txt

[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
posted @   lfree  阅读(5)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
历史上的今天:
2021-09-01 [20210831]bbed读取数据块6.txt
点击右上角即可分享
微信分享提示