oracle 主键 hash,【学习笔记】详细讲解sql_id和hash value的部分转换案例

天萃荷净

sql_id和hash value的部分转换，从oracle 10g开始引进了sql_id，在老版本的oralce中,要表明一条sql,一般使用hash value，而在10g及其以后版本中一般建议使用sql_id。

从9i的sp和10g的awr中也可以看出.对于Library Cache对象,Oracle使用MD5算法进行哈希,生成一个128位的Hash Value,其中低32位作为HASH VALUE显示,SQL_ID则取了后64位.既然hash value和sql_id之前存在着这样的关系,那么我们就可以通过函数实现两者的部分转换(因为最终取值长度不同,所以不能完全转换)

1.查询sql_id和hash value

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')

2 "www.oracleplus.com" from dual;

www.oracleplus.com

-------------------

2012-05-26 01:05:39

SQL> select sql_id,hash_value from v$sql where sql_text like

2 'select * from dual';

SQL_ID HASH_VALUE

------------- ----------

a5ks9fhw2v9s1 942515969

2.oracle自带函数转换sql_id to hash value

SQL> select dbms_utility.SQLID_TO_SQLHASH('a5ks9fhw2v9s1') hash_value FROM DUAL;

HASH_VALUE

----------

942515969

3.自己编写函数sql_id to hash value

SQL> CREATE OR REPLACE FUNCTION sql_id_2_hash_value (sql_id VARCHAR2)

2 RETURN NUMBER

3 IS

4 l_output NUMBER := 0;

5 BEGIN

6 SELECT TRUNC (

7 MOD (

8 SUM (

9 (INSTR ('0123456789abcdfghjkmnpqrstuvwxyz',

10 SUBSTR (LOWER (TRIM (sql_id)), LEVEL, 1))

11 - 1)

12 * POWER (32, LENGTH (TRIM (sql_id)) - LEVEL)),

13 POWER (2, 32)))

14 INTO l_output

15 FROM DUAL

16 CONNECT BY LEVEL <= LENGTH (TRIM (sql_id));

17 RETURN l_output;

18 END;

19 /

函数已创建。

SQL> select sql_id_2_hash_value('a5ks9fhw2v9s1') hash_value FROM DUAL;

HASH_VALUE

----------

942515969

4.hash value 转换为部分 sql_id

SQL> CREATE OR REPLACE FUNCTION hash_value_2_sql_id (p_hash_value NUMBER)

2 RETURN VARCHAR2

3 IS

4 l_output VARCHAR2 (8) := '';

5 BEGIN

6 FOR i

7 IN ( SELECT SUBSTR (

8 '0123456789abcdfghjkmnpqrstuvwxyz',

9 1

10 + FLOOR (

11 MOD (p_hash_value / (POWER (32, LEVEL - 1)), 32)),

12 1)

13 sqlidchar

14 FROM DUAL

15 CONNECT BY LEVEL <= LN (p_hash_value) / LN (32)

16 ORDER BY LEVEL DESC)

17 LOOP

18 l_output := l_output || i.sqlidchar;

19 END LOOP;

20

21 RETURN l_output;

22 END;

23 /

函数已创建。

SQL> select hash_value_2_sql_id(942515969) from dual;

HASH_VALUE_2_SQL_ID(942515969)

--------------------------------------------------------

2v9s1

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】详细讲解sql_id和hash value的部分转换案例