[20260825]ora_hash与kgghash函数.txt

--//昨天测试使用oracle的ora_hash函数计算数据字典对象的hash值,使用bpftrace跟踪发现调用oracle的内部函数kgghash,与我手工执
--//行ora_hash计算结果一致.既然这样是否可以反向验证如果执行带有ora_hash的sql语句,使用内部调用kgghash函数.

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.测试:
--//session 1:
SYS@book> @spid
==============================
SID                           : 115
SERIAL#                       : 61604
PROCESS                       : 3330
SERVER                        : DEDICATED
SPID                          : 3331
PID                           : 49
P_SERIAL#                     : 5
KILL_COMMAND                  : alter system kill session '115,61604' immediate;
PL/SQL procedure successfully completed.

--//window 1:
# bpftrace dc_hash.bt 3331
Attached 6 probes
Tracing Oracle kgghash... Target PID: 3331
Hit Ctrl-C to stop.

--//session 1:
SYS@book> select ora_hash('abcdef') from dual;
ORA_HASH('ABCDEF')
------------------
        2608113931

--//window 1：
--//检索bpftrace dc_hash.bt 3331，检索2608113931
[16:23:11] kgghash #13 pid=3331 hash_text=DUAL length=4 seed=0
[16:23:11] kgghash #13 pid=3331 return hash_value=000000007303d122 1929629986 0000000000005122 20770
[16:23:11] kgghash #14 pid=3331 hash_text=abcdef length=6 seed=0
[16:23:11] kgghash #14 pid=3331 return hash_value=000000009b74a90b 2608113931 000000000000290b 10507
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//反向验证ora_hash函数就是调用oracle内部的kgghash函数。

--//如果换成采用绑定变量观察更加容易：
SYS@book> variable a varchar2(32);
SYS@book> exec :a := 'abcdef1';
PL/SQL procedure successfully completed.

SYS@book> select ora_hash(:a,32767,100) from dual;
ORA_HASH(:A,32767,100)
----------------------
                  6366

[16:36:40] kgghash #361 pid=3331 hash_text=CDB$ROOT length=8 seed=0
[16:36:40] kgghash #361 pid=3331 return hash_value=000000001eee6512 518939922 0000000000006512 25874
[16:36:40] kgghash #362 pid=3331 hash_text=\x0b\x00\x01\x00\x00\x00\x00\x00 length=8 seed=0
[16:36:40] kgghash #362 pid=3331 return hash_value=00000000ceb5d99e 3468024222 000000000000599e 22942
[16:36:40] kgghash #363 pid=3331 hash_text=abcdef1 length=7 seed=64
[16:36:40] kgghash #363 pid=3331 return hash_value=00000000d85b18de 3629848798 00000000000018de 6366
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

3.附上测试:
$ cat dc_hash.bt
BEGIN
{
    printf("Tracing Oracle kgghash... Target PID: %d\n", $1);
    printf("Hit Ctrl-C to stop.\n\n");
}

uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kqrhsh
/ $1 == 0 || pid == $1 /
{
        printf("\nkrqhsh Begin\n");
}

uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kqrhsh
/ $1 == 0 || pid == $1 /
{
        printf("krqhsh End\n");
}


uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kgghash
/ $1 == 0 || pid == $1 /
{
    @kgg++;
    @is_kgg[pid] = 1;
//  printf("[%s] kgghash #%02d pid=%d hash_text=%s length=%d seed=%x hash_text_ascii = ", strftime("%H:%M:%S", nsecs), @kgg, pid,str(arg0,arg1+1),arg1,arg2);
//      print(buf(str(arg0,arg1+1),arg1));
//  printf("[%s] kgghash #%02d pid=%d hash_text=%s length=%d seed=%x \n", strftime("%H:%M:%S", nsecs), @kgg, pid,buf(str(arg0,arg1+1),arg1), arg1,arg2);
//  printf("xxxx = %s %s %s\n",str(arg0,arg1),str(arg0,arg1+1),buf(arg0,arg1));

//  printf("[%s] kgghash #%02d pid=%d hash_text=%s length=%d seed=%x \n", strftime("%H:%M:%S", nsecs), @kgg, pid,buf(str(arg0,arg1+1),arg1), arg1,arg2);
    printf("[%s] kgghash #%02d pid=%d ptr=%016lx hash_text=%s length=%d seed=%x \n", strftime("%H:%M:%S", nsecs), @kgg, pid,arg0,buf(arg0,arg1), arg1,arg2);
}

uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kgghash
/ $1 == 0 || pid == $1 /
{
    if (@is_kgg[pid] == 1) {
        printf("[%s] kgghash #%02d pid=%d return hash_value=%016lx %ld %016lx %d\n", strftime("%H:%M:%S", nsecs), @kgg,pid, retval, retval, retval%32768,retval%32768);
        delete(@is_kgg[pid]);
    }
}

END
{
    printf("\nTotal kgghash: %d\n", @kgg);
    delete(@kgg);

}