关于sql_id与hash_value

根据Slavik 的帖子Oracle sql_id and hash value可知,Oracle输入的SQL语句的hash_value与SQL_id 是根据SQL 文本+”\0×00″计算md5的hash得到的,并将此md5hash值保存在X$KGLOB.KGLNAHSV中. 而同时根据Tanel Poder的blog SQL_ID is just a fancy representation of hash value的内容以及后续的Comment,我们可以得知,SQL_ID为此hash值的后32位,而hash_value 为此Hash值的后16位组成..

不过SQL_ID是此Hash值的后32位通过”0123456789abcdfghjkmnpqrstuvwxyz”的串计算成32进制数,而hash_value为后16位数计算成的10进制数.

下面是例子说明:

sys@DBMAIN>select 123456 from dual;

    123456
----------
    123456

sys@DBMAIN>select kglnahsv, kglnahsh from x$kglob where kglnaobj = 'select 123456 from dual';

KGLNAHSV                            KGLNAHSH
--------------------------------- ----------
aabb8933b5e804034eba3ddbb0a55b1a  2963626778
aabb8933b5e804034eba3ddbb0a55b1a  2963626778

sys@DBMAIN>l
  1* select sql_text,sql_id,hash_value from v$sql where hash_value = 2963626778
sys@DBMAIN>/

SQL_TEXT                                           SQL_ID        HASH_VALUE
-------------------------------------------------- ------------- ----------
select 123456 from dual                            4xfjxvfsaaqsu 2963626778

--利用前述x$kglob 中的md5 hash值的最后8位来计算对应的Hash值.
sys@DBMAIN>select to_number('b0a55b1a','xxxxxxxx') from dual;

TO_NUMBER('B0A55B1A','XXXXXXXX')
--------------------------------
                      2963626778

sys@DBMAIN>select to_char(2963626778,'xxxxxxxx') from dual;

TO_CHAR(2
---------
 b0a55b1a

sys@DBMAIN>
>>> import hashlib
>>> import math
>>> import struct
--这一段通过直接将struct的第四部分,也就是md5值的最后16位提取出来,以与上述SQL语句中得到的hash_value值进行比较.
>>> stmt = "select 123456 from dual"
>>> d = hashlib.md5(stmt + '\x00').digest()
>>> struct.unpack('IIII', d)[3]
2963626778L

--这一段是将上述的Struct结构的数据按照16位分段转换成16进制,由于16进制数有0x开头,以及结尾部分数据包含L,所以使用[2:10]提取起2-10的字符.
>>> h = ""
>>> for i in struct.unpack('IIII', d):
...     h += hex(i)[2:10]
...
>>> h
'aabb8933b5e804034eba3ddbb0a55b1a'
>>>

>>> import hashlib
>>> import math
>>> import struct
>>> stmt = "select 123456 from dual"
>>> def sqlid_2_hash(sqlid):
  sqln = msb * (2 ** 32) + lsb
  stop = math.log(sqln, math.e) / math.log(32, math.e) + 1
  sqlid = ''
  alphabet = '0123456789abcdfghjkmnpqrstuvwxyz'
  for i in range(0, stop):
    sqlid = alphabet[(sqln / (32 ** i)) % 32] + sqlid
...   sum = 0
  return sqlid
...   i = 1
def stmt_2_hash(stmt):
...   alphabet = '0123456789abcdfghjkmnpqrstuvwxyz'
...   for ch in sqlid:
...     sum += alphabet.index(ch) * (32**(len(sqlid) - i))
...     i += 1
...   return sum % (2 ** 32)
...
>>> def stmt_2_sqlid(stmt):
...   h = hashlib.md5(stmt + '\x00').digest()
...   (d1,d2,msb,lsb) = struct.unpack('IIII', h)
...   sqln = msb * (2 ** 32) + lsb
...   stop = math.log(sqln, math.e) / math.log(32, math.e) + 1
...   sqlid = ''
...   alphabet = '0123456789abcdfghjkmnpqrstuvwxyz'
...   for i in range(0, stop):
...     sqlid = alphabet[(sqln / (32 ** i)) % 32] + sqlid
...   return sqlid
...
>>> def stmt_2_hash(stmt):
...   return struct.unpack('IIII', hashlib.md5(stmt + '\x00').digest())[3]
...
>>> stmt_2_hash(stmt)
2963626778L
>>> stmt_2_sqlid(stmt)
__main__:8: DeprecationWarning: integer argument expected, got float
'4xfjxvfsaaqsu'
>>> sqlid_2_hash('4xfjxvfsaaqsu')
2963626778L
>>>

另可以使用Tanel Poder的脚本i2h.sql或者dbms_utility.sqlid_to_sqlhash来作sql_id到sql_hash的转化..

select
    lower(trim('&1')) sql_id
  , trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',substr(lower(trim('&1')),level,1))-1)
                       *power(32,length(trim('&1'))-level)),power(2,32))) hash_value
from
    dual
connect by
    level <= length(trim('&1'))
/
sys@DBMAIN>define 1=4xfjxvfsaaqsu
sys@DBMAIN>@i2h
old   2:     lower(trim('&&1')) sql_id
new   2:     lower(trim('4xfjxvfsaaqsu')) sql_id
old   3:   , trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',substr(lower(trim('&1')),level,1))-1)
new   3:   , trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',substr(lower(trim('4xfjxvfsaaqsu')),level,1))-1)
old   4:                        *power(32,length(trim('&1'))-level)),power(2,32))) hash_value
new   4:                        *power(32,length(trim('4xfjxvfsaaqsu'))-level)),power(2,32))) hash_value
old   8:     level <= length(trim('&1'))
new   8:     level <= length(trim('4xfjxvfsaaqsu'))

SQL_ID        HASH_VALUE
------------- ----------
4xfjxvfsaaqsu 2963626778

sys@DBMAIN>select dbms_utility.sqlid_to_sqlhash('4xfjxvfsaaqsu') from dual;

DBMS_UTILITY.SQLID_TO_SQLHASH('4XFJXVFSAAQSU')
----------------------------------------------
                                    2963626778

No related posts.

1 comment to 关于sql_id与hash_value

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>