34 lines
1.2 KiB
MySQL
34 lines
1.2 KiB
MySQL
/*
|
||
|
||
Description - Converts SQLID into HASH_VALUE
|
||
Input Parameter - SQL_ID
|
||
Source - http://blog.tanelpoder.com/2009/02/22/sql_id-is-just-a-fancy-representation-of-hash-value/
|
||
|
||
Basically all it does is takes the SQL ID, interpret it as a 13 character base-32 encoded number and then take only the lowest 4 bytes worth of information (4 bytes in base-256) out of that number and that’s the hash value.
|
||
|
||
Actually, since 10g the full story goes like this:
|
||
|
||
1) Oracle hashes the library cache object name with MD5, producing a 128 bit hash value
|
||
2) Oracle takes last 64 bits of the MD5 hash and this will be the SQL_ID (but it’s shown in base-32 for brevity rather than in hex or as a regular number)
|
||
3) Oracle takes last 32 bits of the MD5 hash and this will be the hash value (as seen in v$sql.hash_value).
|
||
|
||
In 10gR1+, you can do this:
|
||
SQL> select dbms_utility.SQLID_TO_SQLHASH(‘btxdhy7gkbwjk’) hash_value FROM DUAL;
|
||
HASH_VALUE
|
||
———-
|
||
3743806002
|
||
|
||
*/
|
||
|
||
select
|
||
lower(trim('&&1')) sql_id
|
||
, trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',substr(lower(trim('&&1')),level,1))-1)
|
||
*power(32,length(trim('&&1'))-level)),power(2,32))) hash_value
|
||
from
|
||
dual
|
||
connect by
|
||
level <= length(trim('&&1'))
|
||
/
|
||
|
||
|