[20240911]关于依赖链4.txt

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

[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;

编辑推荐：

返回列表

[20240911]关于依赖链4.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推