Compute hash_value based on the given sql_id

…Or in other words, how to translate SQL_ID to a hash value :)

I once wrote a script. to demo this in my Advanced Oracle Troubleshooting class.

Check this, I’ll run a query and then check what is its SQL_ID and HASH_VALUE from V$SQL:

SQL> select * from dual;

D
-
X

SQL> select sql_id, hash_value from v$sql
  2  where sql_text = 'select * from dual';

SQL_ID        HASH_VALUE
------------- ----------
a5ks9fhw2v9s1  942515969


So, V$SQL reports the real SQL_ID and HASH_VALUE above.

Now lets use my i2h.sql script. ( i2h stands for: sql Id to Hash value ):

SQL> @i2h a5ks9fhw2v9s1

SQL_ID        HASH_VALUE
------------- ----------
a5ks9fhw2v9s1  942515969

It works! :)

The code itself is simple (and you can download the script. from here)

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'))
/

Basically all I do is take 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.

So, SQL_ID is just another hash value of the library cache object name.

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).

The hashing approach changed between 9i and 10g, so in 10g+ you have a v$sql.old_hash_value column to represent the before-10g hash algorithm. This can be useful when comparing plans & statistics when testing migration from 9i to 10g.

Library cache is physically still organized by the hash value, not SQL_ID. When you query views like X$KGLOB or V$SQL by SQL_ID, then Oracle just extracts the low 4 bytes from the SQL_ID and still does the lookup by hash value.

So, despite only SQL_ID showing up everywhere in Enterprise Manager and newest Oracle views and scripts, the hash_value isn’t going anywhere, it’s a fundamental building block of the library cache hash table.

Tanel,

In 11g, you can do this:

SQL> select dbms_utility.SQLID_TO_SQLHASH(’btxdhy7gkbwjk’) hash_value FROM DUAL;

HASH_VALUE
———-
3743806002

CREATE OR REPLACE FUNCTION sqltext_to_sqlid(sql_text IN clob)
RETURN VARCHAR2
AS
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_sqlset_name VARCHAR2(30);
    v_sqlset_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
    v_sqlid VARCHAR2(13);
    v_sql_text CLOB;
    NUL CONSTANT varchar2(1) := CHR(0);
BEGIN
    if ( NUL = SUBSTR(sql_text, LENGTH(sql_text), 1) )
    then
        v_sql_text := sql_text;
    else
        v_sql_text := sql_text || NUL;
    end if;
 
    -- Create a semi-random name for the SQL SET
    v_sqlset_name := 'SQLTEXT_TO_SQLID_' || DBMS_RANDOM.STRING('U', 13);
 
    -- Create the SQL Set
    DBMS_SQLTUNE.CREATE_SQLSET (sqlset_name => v_sqlset_name);
 
    -- Populate the SQL Set
    OPEN v_sqlset_cursor FOR
        SELECT SQLSET_ROW(null, 0, v_sql_text, null, null, null, null, null, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, null, 0, 0, null, 0, 0, null, 0, null, null)
        FROM DUAL;
    DBMS_SQLTUNE.LOAD_SQLSET(v_sqlset_name, v_sqlset_cursor);
 
    SELECT sql_id INTO v_sqlid FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(v_sqlset_name));
 
    -- Drop the SQL Set
    DBMS_SQLTUNE.DROP_SQLSET (sqlset_name => v_sqlset_name);
 
    RETURN v_sqlid;
END;
/
SHOW ERRORS

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24387280/viewspace-696032/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24387280/viewspace-696032/