[20240822]e8ec445edab00042802d511305ab90fa full_hash_value.txt

[20240822]e8ec445edab00042802d511305ab90fa full_hash_value.txt --//今天在写一篇[20240822]SQL语句与调用kgllkal,kglpnal.txt文章时遇到的问题,另外写一篇blog分析. 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. 2.问题提出: SCOTT@book01p> select * from dept where deptno=20;     DEPTNO DNAME                          LOC ---------- ------------------------------ -------------         20 RESEARCH                       DALLAS SCOTT@book01p> @hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------   95129850 80baj2c2ur47u            0     102650      2852011669   5ab90fa  2024-08-22 09:12:25    16777216 SYS@book> @ kglob 80baj2c2ur47u 0 ============================== INST_ID                       : 1 OWNER                         : NAME                          : select * from dept where deptno=20 DB_LINK                       : NAMESPACE                     : SQL AREA TYPE                          : CURSOR SHARABLE_MEM                  : 28352 LOADS                         : 1 EXECUTIONS                    : 4 LOCKS                         : 1 PINS                          : 0 KEPT                          : NO CHILD_LATCH                   : 0 INVALIDATIONS                 : 0 HASH_VALUE                    : 95129850 LOCK_MODE                     : NULL PIN_MODE                      : NONE STATUS                        : VALID TIMESTAMP                     : 2024-08-22/09:44:45 PREVIOUS_TIMESTAMP            : LOCKED_TOTAL                  : 3 PINNED_TOTAL                  : 5 PROPERTY                      : FULL_HASH_VALUE               : e8ec445edab00042802d511305ab90fa ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ CON_ID                        : 3 CON_NAME                      : ADDR                          : 00000000618725E8 EDITION                       : SQL_ID                        : 80baj2c2ur47u --//执行sql语句后会出现一个与2个FULL_HASH_VALUE值一样的对象,有一个对象owner='$BUILD$'： SYS@book> @ kglob 0 3280362473 ============================== INST_ID                       : 1 OWNER                         : NAME                          : e8ec445edab00042802d511305ab90fa DB_LINK                       : NAMESPACE                     : SQL AREA STATS TYPE                          : CURSOR STATS SHARABLE_MEM                  : 4064 LOADS                         : 1 EXECUTIONS                    : 0 LOCKS                         : 0 PINS                          : 0 KEPT                          : YES CHILD_LATCH                   : 23529 INVALIDATIONS                 : 0 HASH_VALUE                    : 3280362473 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ LOCK_MODE                     : NONE PIN_MODE                      : NONE STATUS                        : VALID TIMESTAMP                     : 2024-08-22/09:44:45 PREVIOUS_TIMESTAMP            : LOCKED_TOTAL                  : 1 PINNED_TOTAL                  : 1 PROPERTY                      : FULL_HASH_VALUE               : f70b8d76d1df4847c17a0582c3865be9 CON_ID                        : 3 CON_NAME                      : BOOK01P ADDR                          : 00000000672D1368 EDITION                       : SQL_ID                        : PL/SQL procedure successfully completed. --//注:我仅仅列出1条记录,实际上输出是2条记录. --//SQL AREA STATS,namespace=75. 75 = 0x4b,你可以查询v$db_object_cache视图name='e8ec445edab00042802d511305ab90fa'确定. --//namespace可以通过 select distinct kglhdnsp,kglhdnsd,kglobtyd from x$kglob   order by 1;确定. SYS@book> @ sharepool/shp4x 0 3280362473 HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 ---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- parent handle address  00000000672D1368 00000000672D1368 e8ec445edab00042802d511305ab90fa                  0          0          0 0000000064B083A8 00                     4064          0          0      4064       4064 3280362473                        0 $ sql_idz.sh 'e8ec445edab00042802d511305ab90fa.BOOK01P\x4b\0\0' sql_text = e8ec445edab00042802d511305ab90fa.BOOK01P\x4b\0\0\0 full_hash_value(16) = 707AF0CC4CA41370E0F5595782693B2F or 707af0cc4ca41370e0f5595782693b2f hash_value(10) = 2187934511   --//HASH_VALUE: 3280362473 对不上! sql_id(32) = f1xatay16kftg sql_id(32) = f1xatay16kftg sql_id(32) = f1xatay16kftg --//按照以前的计算方法sql语句,在sql语句后面补上\0字符. --//其他对象: NAME.OWNER.CON_NAME\x<namespace_hex>\0\0\0. --//如果没有owner,使用NAME.CON_NAME\x<namespace_hex>\0\0\0. --//如果不是PDB数据库: --//NAME.OWNER\x<namespace_hex>\0\0\0. --//NAME\x<namespace_hex>\0\0\0. --//但是我按照原来的规则计算结果对不上,也就是不是这样计算的，看看如何运算的。 3.使用gdb跟踪看看: $ cat md5.gdb set pagination off set logging file /tmp/md5.log set logging overwrite on set logging on break kggmd5Update   commands     printf "Length: %d\n",$rdx     x/8xc $rsi     c   end break kglComputeHash   commands     c   end break kggmd5Process   commands     c   end break kggmd5Finish   commands     c   end --//刷新共享池. SYS@book> alter system flush shared_pool; System altered. --//session 1: Select * from dept where deptno=20; --//执行多次注意第1个字母大写,减少递归. --//session 2: $ rlgdb -f -p 6148 -x md5.gdb --//session 1:第1个字母小写.注意一定要第1次执行硬解析.第2次执行看不到. SCOTT@book01p> select * from dept where deptno=20;     DEPTNO DNAME                          LOC ---------- ------------------------------ -------------         20 RESEARCH                       DALLAS --//退出. --//查看/tmp/md5.log文件,查询"e".可以找到如下内容: Breakpoint 1, 0x0000000015321c00 in kggmd5Update () Length: 32 0x7ffc93057630: 101 'e' 56 '8'  101 'e' 99 'c'  52 '4'  52 '4'  53 '5'  101 'e' --//e8ec445edab00042802d511305ab90fa,注脚本仅仅取前8个字符. Breakpoint 1, 0x0000000015321c00 in kggmd5Update () Length: 1 0x154f52bc: 46 '.'  0 '\000'    0 '\000'    0 '\000'    113 'q' 109 'm' 120 'x' 113 'q' --//. Breakpoint 1, 0x0000000015321c00 in kggmd5Update () Length: 7 0x7ffc9305765c: 67 'C'  104 'h' 105 'i' 108 'l' 100 'd' 58 ':'  48 '0'  0 '\000' --//Child:0 Breakpoint 1, 0x0000000015321c00 in kggmd5Update () Length: 1 0x154f52bc: 46 '.'  0 '\000'    0 '\000'    0 '\000'    113 'q' 109 'm' 120 'x' 113 'q' --//. Breakpoint 1, 0x0000000015321c00 in kggmd5Update () Length: 7 0x7b8303f4: 66 'B'  79 'O'  79 'O'  75 'K'  48 '0'  49 '1'  80 'P'  84 'T' --//BOOK01P,注意前面的长度是7,不会取最后的T字符 Breakpoint 1, 0x0000000015321c00 in kggmd5Update () Length: 4 0x7ffc930564a0: 75 'K'  0 '\000'    0 '\000'    0 '\000'    0 '\000'    0 '\000'    0 '\000'    0 '\000' --//K\0\0\0 --//K的ascii码是75对应的是名字空间的数字表示,NAMESPACE='SQL AREA STATS'. TYPE='CURSOR STATS' --//75 = 0x4b Breakpoint 4, 0x0000000015321a90 in kggmd5Finish () --//这样拼接起来就是'e8ec445edab00042802d511305ab90fa.Child:0.BOOK01P\x4b\0\0'. --//Child第1个字母居然是大写,疏忽了.... $ sql_idz.sh 'e8ec445edab00042802d511305ab90fa.Child:0.BOOK01P\x4b\0\0' sql_text = e8ec445edab00042802d511305ab90fa.Child:0.BOOK01P\x4b\0\0\0 full_hash_value(16) = F70B8D76D1DF4847C17A0582C3865BE9 or f70b8d76d1df4847c17a0582c3865be9 hash_value(10) = 3280362473 sql_id(32) = c2yh5hb1scqz9 sql_id(32) = c2yh5hb1scqz9 sql_id(32) = c2yh5hb1scqz9 --//FULL_HASH_VALUE: f70b8d76d1df4847c17a0582c3865be9,这次完成对上了. --//你可以发现拼接字符串的特点使用的是Child:0,里面的0很容易联想到child_number,多个子光标下会不同.避免争用. --//可以简单验证看看. 4.产生多个子光标看看: SYS@book> select owner,name,namespace,type,hash_value,full_hash_value from v$db_object_cache where name='e8ec445edab00042802d511305ab90fa'; OWNER   NAME                                     NAMESPACE      TYPE         HASH_VALUE FULL_HASH_VALUE ------- ---------------------------------------- -------------- ------------ ---------- --------------------------------         e8ec445edab00042802d511305ab90fa         SQL AREA STATS CURSOR STATS 3280362473 f70b8d76d1df4847c17a0582c3865be9 $BUILD$ e8ec445edab00042802d511305ab90fa         SQL AREA BUILD CURSOR       1635416785 7e8adf6b1c21e493a6bdcf5a617a7ad1 SCOTT@book01p> alter session set optimizer_index_cost_adj=99; Session altered. SCOTT@book01p> select * from dept where deptno=20; DEPTNO DNAME                          LOC ---------- ------------------------------ -------------         20 RESEARCH                       DALLAS --//这样产生新的子光标. SYS@book> select owner,name,namespace,type,hash_value,full_hash_value from v$db_object_cache where name='e8ec445edab00042802d511305ab90fa'; OWNER    NAME                                     NAMESPACE       TYPE         HASH_VALUE FULL_HASH_VALUE -------- ---------------------------------------- --------------- ------------ ---------- --------------------------------          e8ec445edab00042802d511305ab90fa         SQL AREA STATS  CURSOR STATS 3280362473 f70b8d76d1df4847c17a0582c3865be9          e8ec445edab00042802d511305ab90fa         SQL AREA STATS  CURSOR STATS 2807461702 8ecf05faf88a4ed54e1b8f15a7567746 $BUILD$  e8ec445edab00042802d511305ab90fa         SQL AREA BUILD  CURSOR       1635416785 7e8adf6b1c21e493a6bdcf5a617a7ad1 --//增加FULL_HASH_VALUE=8ecf05faf88a4ed54e1b8f15a7567746 $ sql_idz.sh 'e8ec445edab00042802d511305ab90fa.Child:1.BOOK01P\x4b\0\0' sql_text = e8ec445edab00042802d511305ab90fa.Child:1.BOOK01P\x4b\0\0\0 full_hash_value(16) = 8ECF05FAF88A4ED54E1B8F15A7567746 or 8ecf05faf88a4ed54e1b8f15a7567746 hash_value(10) = 2807461702 sql_id(32) = 4w6wg2qmpcxu6 sql_id(32) = 4w6wg2qmpcxu6 sql_id(32) = 4w6wg2qmpcxu6 --//OK,完成对上.