联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
从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.xifenfei.com" from dual; www.xifenfei.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/