[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,完成对上.