[20240911]关于依赖链4.txt

--//继续补充前面的探究，问题还是集中在oracle如何通过x$kgldp组织起来这些相关信息的.
--//我看了链接Oracle X$ tables – Part 1 – Where do they get their data from?
--//http://blog.tanelpoder.com/2014/01/10/oracle-x-tables-part-1-where-do-they-get-their-data-from
--//摘抄一段内容,金山词霸的翻译:

Wow, why does the X$KCCCP data reside in my session's UGA? This is where the extra complication (and sophistication) of
X$ fixed tables comes into play!
哇，为什么X$KCCCP数据驻留在我的会话的UGA中？这就是X$固定表的额外复杂(和复杂)发挥作用的地方！

Some X$ tables do not simply read whatever is in some memory location, but they have helper functions associated with
them (something like fixed packages that the ASM instance uses internally). So, whenever you query this X$, then first a
helper function is called, which will retrieve the source data from whereever it needs to, then copies it to your UGA in
the format corresponding to this X$ and then the normal X$ memory location parsing & presentation code kicks in.
一些X$表不是简单地读取某些内存位置中的任何内容，而是具有与之相关联的辅助函数(类似于ASM实例在内部使用的固定包)。因此，每
当您查询这个X$时，首先调用一个助手函数，它将从需要的地方检索源数据，然后将其以与这个X$对应的格式复制到您的UGA，然后启动
正常的X$内存位置解析表示代码。

If you trace what the X$KCCCP access does – you'd see a bunch of control file parallel read wait events every time you
query the X$ table (to retrieve the checkpoint progress records). So this X$ is not doing just a passive read only
presentation of some memory structure (array). The helper function will first do some real work, allocates some runtime
memory for the session (the kxsFrame4kPage chunk in UGA) and copies the results of its work to this UGA area – so that
the X$ array & offset parsing code can read and present it back to the query engine.
如果您跟踪X$KCCCP访问的功能——每次查询X$表(以检索检查点进程记录)时，您都会看到一堆控制文件并行读取等待事件。所以这个X$
不仅仅是对一些内存结构(数组)的被动只读表示。辅助函数将首先做一些实际的工作，为会话分配一些运行时内存(UGA中的
kxsFrame4kPage块)，并将其工作结果复制到这个UGA区域——以便X$数组偏移解析代码可以读取并将其呈现回查询引擎。

In other words, the ADDR column in X$ tables does not necessarily show where the source data it shows ultimately lives,
but just where the final array that got parsed for presentation happened to be. Sometimes the parsed data structure is
the ultimate source where it comes from, sometimes a helper function needs to do a bunch of work (like taking latches
and walking linked lists for X$KSMSP or even doing physical disk reads from controlfiles for X$KCCCP access).
换句话说，X$表中的ADDR列不一定显示它显示的源数据最终存在的位置，而只是显示被解析的最终数组的位置。有时解析的数据结构是它
的最终来源，有时助手函数需要做一系列工作(比如为X$KSMSP获取锁存器和行走链接列表，甚至从控制文件中进行物理磁盘读取以进行
X$KCCCP访问)。

1.环境:
SYS@book> @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.

select
 addr,
 to_number(addr,'XXXXXXXXXXXXXXXX') addr_dec,
 to_number(lag(addr,1) over (order by indx),'xxxxxxxxxxxxxxxx') lag_addr_dec,
 to_char(
 to_number(addr,'XXXXXXXXXXXXXXXX') -
 to_number(lag(addr,1) over (order by indx),'xxxxxxxxxxxxxxxx'),
 'xxxxxxxxxxxxxxxx'
 ) row_size_hex,
 to_number(addr,'XXXXXXXXXXXXXXXX') -
 to_number(lag(addr,1) over (order by indx),'xxxxxxxxxxxxxxxx') row_size,
 indx
from x$kgldp
where indx <= 10;

ADDR                         ADDR_DEC         LAG_ADDR_DEC ROW_SIZE_HEX                  ROW_SIZE                 INDX
---------------- -------------------- -------------------- ----------------- -------------------- --------------------
00007FEEDBBD6FF8      140663865569272                                                                                0
00007FEEDBBD7060      140663865569376      140663865569272                68                  104                    1
00007FEEDBBD6970      140663865567600      140663865569376 #################                -1776                    2
00007FEEDBBD69E0      140663865567712      140663865567600                70                  112                    3
00007FEEDBBD6FF8      140663865569272      140663865567712               618                 1560                    4
00007FEEDBBD7060      140663865569376      140663865569272                68                  104                    5
00007FEEDBBD7060      140663865569376      140663865569376                 0                    0                    6
00007FEEDBBD7060      140663865569376      140663865569376                 0                    0                    7
00007FEEDBBDA2D0      140663865582288      140663865569376              3270                12912                    8
00007FEEDBBDA338      140663865582392      140663865582288                68                  104                    9
00007FEEDBBDA3A0      140663865582496      140663865582392                68                  104                   10
11 rows selected.
--//说明行长度104(0x68).

SELECT ta.kqftanam, co.kqfconam, co.kqfcodty, co.kqfcotyp, co.kqfcomax,
       co.kqfcolsz, co.kqfcolof, co.kqfcosiz, co.kqfcooff, co.kqfcoidx,
       co.kqfcoipo
  FROM x$kqfta ta, x$kqfco co
 WHERE co.kqfcotab = ta.indx
   AND kqftanam LIKE UPPER('%x$kgldp%')
ORDER BY co.indx
--ORDER BY ta.kqftanam, co.kqfcooff, co.indx
;

KQFTANAM KQFCONAM KQFCODTY KQFCOTYP KQFCOMAX KQFCOLSZ KQFCOLOF KQFCOSIZ KQFCOOFF KQFCOIDX KQFCOIPO
-------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------
X$KGLDP  ADDR           23        9        0        0        0        8        0        0        0
X$KGLDP  INDX            2       11        0        0        0        4        0        0        0
X$KGLDP  INST_ID         2       11        0        0        0        4        0        0        0
X$KGLDP  CON_ID          2       11        0        0        0        2        0        0        0
X$KGLDP  KGLHDADR       23        0        0        0        0        8        8        0        0
X$KGLDP  KGLHDPAR       23        0        0        0        0        8       16        0        0
X$KGLDP  KGLNAHSH        2       11        0        0        0        4       40        1        0 --//KQFCOIDX=1,表示该字段有索引.
X$KGLDP  KGLDEPNO        2        0        0        0        0        2       44        0        0
X$KGLDP  KGLRFHDL       23        0        0        0        0        8       24        0        0
X$KGLDP  KGLRFHSH        2       11        0        0        0        4       52        0        0
X$KGLDP  KGLRFFLG        2        0        0        0        0        2       46        0        0
X$KGLDP  KGLDPOBJ       23        0        0        0        0        8       32        0        0
X$KGLDP  KGLDPPOS        2        0        0        0        0        4       48        0        0
X$KGLDP  KGLDPFGR       23        3        0        4       56     2000       60        0        0
14 rows selected.

SYS@book> @ xind  x$kgldp
TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION CON_ID
---------- ------------ ----------- --------------- ------
X$KGLDP               1 KGLNAHSH                  0      0

$ 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 or hash_value(16) = 6669B6BE or 6669b6be
sql_id(32) = b7zpx6jm6mdpy
sql_id(32) = b7zpx6jm6mdpy
sql_id(32) = b7zpx6jm6mdpy
--//确定sql_id=b7zpx6jm6mdpy,hash_value(10) = 1718204094

$ sql_idz.sh 'DEPT.SCOTT.BOOK01P\x1\0\0\0' 3
sql_text = DEPT.SCOTT.BOOK01P\x1\0\0\0
full_hash_value(16) = 05DB243908B3C797B99628590EDB820C or 05db243908b3c797b99628590edb820c
xxxxx_matching_signature(10) = -5073823567219817972 or  13372920506489733644
hash_value(10) = 249266700 or hash_value(16) = 0EDB820C or 0edb820c
sql_id(32) = bm5j8b47dr0hc
sql_id(32) = bm5j8b47dr0hc
sql_id(32) = bm5j8b47dr0hc

2.测试:
--//session 1:
SCOTT@book01p> @ mn.txt 1e8
--//如果你很快执行完成,增加循环次数.

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   000000006B3FC170 000000006B37F400 SELECT count(*)  FROM dept ,emp WHERE de          1          2          0 000000006B1F8768 000000006B1F9150       8128      16176       3327     27631      27631 1718204094 b7zpx6jm6mdpy          0
parent handle address  000000006B37F400 000000006B37F400 SELECT count(*)  FROM dept ,emp WHERE de          1          0          0 000000006B1993C8 00                     4064          0          0      4064       4064 1718204094 b7zpx6jm6mdpy      65535

SYS@book> select * from x$kgldp where kglnahsh=1718204094 ;
ADDR             INDX INST_ID CON_ID KGLHDADR         KGLHDPAR           KGLNAHSH KGLDEPNO KGLRFHDL           KGLRFHSH KGLRFFLG KGLDPOBJ         KGLDPPOS KGLDPFGR
---------------- ---- ------- ------ ---------------- ---------------- ---------- -------- ---------------- ---------- -------- ---------------- -------- --------
00007FEEDB83EEF0    0       1      3 000000006B3FC170 000000006B37F400 1718204094        2 000000006ABE3F10  249266700        1 000000006CDCA748       22
00007FEEDB83EF58    1       1      3 000000006B3FC170 000000006B37F400 1718204094        1 000000006B81C570 1273316885        1 000000006CDCA748       28
00007FEEDB83EFC0    2       1      3 000000006B3FC170 000000006B37F400 1718204094        0 0000000066BA2B88  925293031        1 000000006CDCA748        0

--//我前面已经解析KGLDPPOS表示对象在sql语句偏移的位置.
--//0123456789012345678902234567890323456789042345678905234567890612
--//SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno
--///KGLDPPOS = 0 28 22 可以猜测对象在sql语句的偏移量，这个很容易验证。当然KGLDPPOS=0，估计是一个例外。
SYS@book> @ fchaz 00007FEEDB83EEF0
LOC KSMCHPTR                     KSMCHIDX             KSMCHDUR KSMCHCOM                     KSMCHSIZ KSMCHCLS             KSMCHTYP KSMCHPAR         KSMCHPTR_END
--- ---------------- -------------------- -------------------- ---------------- -------------------- -------- -------------------- ---------------- -----------------
UGA 00007FEEDB83EBE0                                           kxs-heap-d                       1056 recr                     4095 00007FEEDBBEF228 00007FEEDB83EFFF
--//位于UGA区域.说明通过某种方式读取放入UGA区域.你可以看看如下链接:
--//http://blog.tanelpoder.com/2014/01/10/oracle-x-tables-part-1-where-do-they-get-their-data-from  

$ ./lookup.awk   kxs
kxs : kernel execution shared cursor

SYS@book> oradebug setmypid
Statement processed.

--//仅仅看第一行.dept对象.
SYS@book> oradebug peek 0x00007FEEDB83EEF0 104 1
[7FEEDB83EEF0, 7FEEDB83EF58) = 00000003 00000000 6B3FC170 00000000 6B37F400 00000000 6ABE3F10 00000000 6CDCA748 00000000 6669B6BE 00010002 00000016 ...

*** 2024-09-11T11:20:30.268915+08:00 (CDB$ROOT(1))
Processing Oradebug command 'peek 0x00007FEEDB83EEF0 104 1'
[7FEEDB83EEF0, 7FEEDB83EF58) = 00000003 00000000 6B3FC170 00000000 6B37F400 ...
Dump of memory from 0x7FEEDB83EF04 to 0x7FEEDB83EF58
7FEEDB83EF00          00000000 6ABE3F10 00000000      [.....?.j....]
7FEEDB83EF10 6CDCA748 00000000 6669B6BE 00010002  [H..l......if....]
7FEEDB83EF20 00000016 0EDB820C 00000000 00000000  [................]
7FEEDB83EF30 00000069 C0B38F00 DB83E0A0 00007FEE  [i...............]
7FEEDB83EF40 17AE08A8 00000000 00000000 00000000  [................]
7FEEDB83EF50 01010101 00007FEE                    [........]

--//整理如下:
00000003 00000000 6B3FC170 00000000
6B37F400 00000000 6ABE3F10 00000000
6CDCA748 00000000 6669B6BE 00010002
00000016 0EDB820C 00000000 00000000
00000069 C0B38F00 DB83E0A0 00007FEE
17AE08A8 00000000 00000000 00000000
01010101 00007FEE                  

--//字段保存顺序与输出字段顺序有点不一致.
SELECT ta.kqftanam, co.kqfconam, co.kqfcodty, co.kqfcotyp, co.kqfcomax,
       co.kqfcolsz, co.kqfcolof, co.kqfcosiz, co.kqfcooff, co.kqfcoidx,
       co.kqfcoipo,co.indx
  FROM x$kqfta ta, x$kqfco co
 WHERE co.kqfcotab = ta.indx
   AND kqftanam LIKE UPPER('%x$kgldp%')
ORDER BY ta.kqftanam, co.kqfcooff, co.indx
;

KQFTANAM KQFCONAM KQFCODTY KQFCOTYP KQFCOMAX KQFCOLSZ KQFCOLOF KQFCOSIZ KQFCOOFF KQFCOIDX KQFCOIPO  INDX
-------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -----
X$KGLDP  ADDR           23        9        0        0        0        8        0        0        0 11428
X$KGLDP  INDX            2       11        0        0        0        4        0        0        0 11429
X$KGLDP  INST_ID         2       11        0        0        0        4        0        0        0 11430
X$KGLDP  CON_ID          2       11        0        0        0        2        0        0        0 11431
X$KGLDP  KGLHDADR       23        0        0        0        0        8        8        0        0 11432
X$KGLDP  KGLHDPAR       23        0        0        0        0        8       16        0        0 11433
X$KGLDP  KGLRFHDL       23        0        0        0        0        8       24        0        0 11436
X$KGLDP  KGLDPOBJ       23        0        0        0        0        8       32        0        0 11439
X$KGLDP  KGLNAHSH        2       11        0        0        0        4       40        1        0 11434
X$KGLDP  KGLDEPNO        2        0        0        0        0        2       44        0        0 11435
X$KGLDP  KGLRFFLG        2        0        0        0        0        2       46        0        0 11438
X$KGLDP  KGLDPPOS        2        0        0        0        0        4       48        0        0 11440
X$KGLDP  KGLRFHSH        2       11        0        0        0        4       52        0        0 11437
X$KGLDP  KGLDPFGR       23        3        0        4       56     2000       60        0        0 11441
14 rows selected.
--//注意看KQFCOOFF的数值,表示保存的偏移.
--//CON_ID  ,KQFCOOFF= 0, 长度=2 00000003 00000000 颠倒 00000000 00000003,取后2位0003
--//KGLHDADR,KQFCOOFF= 8, 长度=8 6B3FC170 00000000 颠倒 00000000 6B3FC170 --//sql语句的子游标句柄.
--//KGLHDPAR,KQFCOOFF=16, 长度=8 6B37F400 00000000 颠倒 00000000 6B37F400 --//sql语句的父游标句柄.
--//KGLRFHDL,KQFCOOFF=24, 长度=8 6ABE3F10 00000000 颠倒 00000000 6ABE3F10 --//dept对象的句柄.
--//KGLDPOBJ,KQFCOOFF=32, 长度=8 6CDCA748 00000000 颠倒 00000000 6CDCA748 --//KGLDPOBJ
--//KGLNAHSH,KQFCOOFF=40, 长度=4 6669B6BE = 1718204094,正好是KGLNAHSH.
--//KGLDEPNO,KQFCOOFF=44, 长度=2 00010002  取后2位0002
--//KGLRFFLG,KQFCOOFF=46, 长度=2 00010002  取前2位0001
--//KGLDPPOS,KQFCOOFF=48, 长度=4 00000016 = 22
--//KGLRFHSH,KQFCOOFF=52, 长度=4 0EDB820C = 249266700 ,正好是KGLRFHSH.
SYS@book> @ kglob 0 249266700
==============================
INST_ID                       : 1
OWNER                         : SCOTT
NAME                          : DEPT
DB_LINK                       :
NAMESPACE                     : TABLE/PROCEDURE
TYPE                          : TABLE
NAMESPACE_NUM                 : 1
NAMESPACE_HEX                 : 1
SHARABLE_MEM                  : 4064
LOADS                         : 1
EXECUTIONS                    : 0
LOCKS                         : 0
PINS                          : 0
KEPT                          : NO
CHILD_LATCH                   : 98828
INVALIDATIONS                 : 0
HASH_VALUE                    : 249266700
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
LOCK_MODE                     : NONE
PIN_MODE                      : NONE
STATUS                        : VALID
TIMESTAMP                     : 2024-08-29/17:27:12
PREVIOUS_TIMESTAMP            :
LOCKED_TOTAL                  : 2
PINNED_TOTAL                  : 2
PROPERTY                      :
FULL_HASH_VALUE               : 05db243908b3c797b99628590edb820c
CON_ID                        : 3
CON_NAME                      : BOOK01P
ADDR                          : 000000006ABE3F10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
EDITION                       :
SQL_ID                        :
OBJECT_STR                    : DEPT.SCOTT.BOOK01P\x1\0\0\0
PL/SQL procedure successfully completed.

--//最后一个KGLDPFGR ,KQFCOOFF=60,按照前面累加应该是56,我猜测与KQFCOLSZ KQFCOLOF=4 56有关.
--//具体细节不探究了.
--//对应内容如下:
00000000
00000069 C0B38F00 DB83E0A0 00007FEE
17AE08A8 00000000 00000000 00000000
01010101 00007FEE

3.总结:
--//当然具体如何形成x$kgldp的输出,我还是不清楚....
--//我仅仅解析了放入uga区域,如何读取它的信息.
--//另外索引如何建立以及检索也未知....

4.附上xind.sql shp4x.sql脚本:
$ cat xind.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- File name:   xind.sql
-- Purpose:     query X$ index
-- Author:      lfree
--
-- Usage:
--     @ xind <x$table_name,...>
--     @ xind <x$kglob,x$kgldp>
--------------------------------------------------------------------------------
set termout off
column column_string new_value column_string format a200
select decode('&1','','1=1','1','1=1','1=1','1=1','table_name in ('||''''||replace(upper('&1'),',',''',''')||''')' ) column_string from dual ;
set termout on

select * from V$INDEXED_FIXED_COLUMN where 1=1 and (&column_string);

$ cat sharepool/shp4x.sql
column N0_6_16 format 99999999
column handle_type format a22
SELECT /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ DECODE (kglhdadr,
               kglhdpar, 'parent handle address',
               'child handle address')
           handle_type,
       kglhdadr,
       kglhdpar,
       --//substr(kglnaobj,1,40) c40,
       substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj), '(name not found)'),chr(13),'') ,1,40)  c40,
           KGLHDLMD,
           KGLHDPMD,
           kglhdivc,
       kglobhd0,
       kglobhd6,
       kglobhs0,kglobhs6,kglobt16,
       kglobhs0+kglobhs6+kglobt16 N0_6_16,
           kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
           kglnahsh,
           kglobt03,
           kglobt09
  FROM x$kglob
 WHERE kglobt03 = lower('&1') or KGLNAHSH= &2;