…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.
NB! Dates updated: After a 1.5 year break, this year’s only Advanced Oracle Troubleshooting training class (updated with Oracle 12c content) takes place on 14-18 December 2015 and 11-15 January 2016 (I had to reschedule the start from November to December). So sign up now if you want to learn new cool stuff!