从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.orasos.com" from dual; www.orasos.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
参考:http://blog.tanelpoder.com/2009/02/22/sql_id-is-just-a-fancy-representation-of-hash-value/
