天萃荷净
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的部分转换案例