详解sql_id,hash_value,address,old_hash_value(一)

详解sql_id,hash_value,address,old_hash_value

在ORACLE的v$sql，v$sqlarea等大量视图中存在sql_id、hash_value、address、old_hash_value等字段。这些字段到底具有什么作用，又有什么区别？从网络搜集的资料可以看出，sql_id,和hash_value均可以用来标示sql语句。而old_hash_value仅仅是oracle为保持兼容而留下的字段（10g之前，oracle的hash_value的计算方法不同）。address是用来表示父游标内存地址的，在极端情况下，不同的sql语句可能会具有相同的sql_id,此时我们可以借助address来区分。

在oracle中， 数据库首先对sql语句通过 MD5算法计算其hash值，并将其会存入x$kglob的kglnahsv字段中。如：

[sql] 
SQL> select 8888 from dual;  
  
      8888  
----------  
      8888  
  
SQL> select kglnahsv from x$kglob where kglnaobj = 'select 8888 from dual';  
  
KGLNAHSV  
--------------------------------  
d6331ec5db1329feb863e5f098654cc3  
d6331ec5db1329feb863e5f098654cc3  
    现在，我们看一下怎样在oracle中实现md5算法。我们可以创建如下函数，来计算某字符串的md5值。
[sql] 
create or replace function md5(v_str in varchar2) return RAW as  
begin  
return DBMS_CRYPTO.hash(UTL_I18N.string_to_raw(v_str, 'AL32UTF8'), DBMS_CRYPTO.hash_md5 );  
end;  
    运算结果如下：
[sql] 
SQL> select md5('8888') from dual;  
  
MD5('8888')  
----------------------------------------  
CF79AE6ADDBA60AD018347359BD144D2  

通过某网站的在线md5算法，计算的结果和我们的计算结果完全一致：

oracle是否对sql语句直接采用了md5算法那？当然不是，oracle在计算之前，会在sql语句的末尾添加一个空字符（\x00)，然后再对字符串执行md5运算，并对计算出的结果进行反序操作。下面的例子很好的说明了这一切：

好了，现在我们已经知道KGLNAHSV字段的值是如何计算出来的，那么sql_id,和hash_value又是怎样计算的那？其实很简单，sql_id是KGLNAHSV字段的末64位即8个字节的32位进制表示，如果sql_id的长度不足13，那么会在左端补0；而hash_value是kglnahsv的末32位即4个字节的10进制表示。验证如下

[sql] 
CREATE OR REPLACE FUNCTION sqltext_to_hash(p_sql IN VARCHAR2)RETURN NUMBER  
    IS  
BEGIN  
    RETURN TO_NUMBER( RAWTOHEX( UTL_RAW.reverse( UTL_RAW.SUBSTR( md5(p_sql||chr(0)), 13,4))),  'xxxxxxxx');  
END;  
  
  
CREATE OR REPLACE FUNCTION sqltext_to_sqlid(p_sql IN VARCHAR2)  
RETURN VARCHAR2  
IS  
v_md5 RAW(16) := md5(p_sql ||chr(0));  
v_temp NUMBER := TO_NUMBER(RAWTOHEX(UTL_RAW.reverse(UTL_RAW.SUBSTR(v_md5, 9, 4)))|| RAWTOHEX(UTL_RAW.reverse(UTL_RAW.SUBSTR(v_md5, 13, 4))),RPAD('x', 16, 'x'));  
v_sqlid VARCHAR2(13);  
BEGIN  
FOR i IN 0 .. FLOOR(LN(v_temp) / LN(32))  
LOOP  
v_sqlid :=SUBSTR('0123456789abcdfghjkmnpqrstuvwxyz',FLOOR(MOD(v_temp / POWER(32, i), 32)) + 1,1)|| v_sqlid;  
END LOOP;  
RETURN LPAD(v_sqlid, 13, '0');  
END;  
  
  
CREATE OR REPLACE FUNCTION sqlid_to_hash(p_sqlid IN VARCHAR2)  
RETURN NUMBER  
IS  
v_sqlid VARCHAR2(20) := TRIM(p_sqlid);  
v_hash NUMBER;  
BEGIN  
SELECT SUM((INSTR('0123456789abcdfghjkmnpqrstuvwxyz', SUBSTR(LOWER(v_sqlid), LEVEL, 1))-1)* POWER(32, LENGTH(v_sqlid)-LEVEL))  
INTO v_hash  
FROM DUAL  
CONNECT BY LEVEL <= LENGTH(v_sqlid);  
RETURN BITAND(v_hash, POWER(2, 32)-1);  
END;  
[sql] 
SQL> select sql_text,sql_id,hash_value from v$sql where sql_text like 'select 8888 from dual';  
  
SQL_TEXT               SQL_ID        HASH_VALUE  
------------------------------ ------------- ----------  
select 8888 from dual          bhsz5y2c6am63 2556775619  
  
SQL> select sqltext_to_hash('select 8888 from dual') from dual;  
  
SQLTEXT_TO_HASH('SELECT8888FROMDUAL')  
-------------------------------------  
               2556775619  
  
SQL> select sqltext_to_sqlid('select 8888 from dual') from dual;  
  
SQLTEXT_TO_SQLID('SELECT8888FROMDUAL')  
----------------------------------------------------------------------------------------------------  
bhsz5y2c6am63  
  
SQL> select sqlid_to_hash('bhsz5y2c6am63') from dual;  
  
SQLID_TO_HASH('BHSZ5Y2C6AM63')  
-----------------