SQL_ID is just a fancy representation of hash value

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