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.

NB! If you want to move to the "New World" - and benefit from the awesomeness of Hadoop, without having to re-engineer your existing applications - check out Gluent, my new startup that will make history! ;-)

This entry was posted in Cool stuff, Oracle and tagged . Bookmark the permalink.

64 Responses to SQL_ID is just a fancy representation of hash value

  1. satish says:

    @Greg
    SELECT sql_handle, plan_name
    FROM dba_sql_plan_baselines
    WHERE signature IN (
    SELECT exact_matching_signature FROM v$sql WHERE sql_id=’&SQL_ID’)
    )
    Thanks to Doug who has provided link in his blog which i am giving below.

    http://intermediatesql.com/oracle/how-to-find-spm-baseline-by-sql_id/#more-639

  2. Vishal Gupta says:

    Tanel,

    How did you work out the algorithm to convert the SQL_ID to HASH_VALUE? Can you please give some pointers?

  3. Tanel Poder says:

    @Vishal Gupta
    It’s just taking last 4 bytes of the 8 byte binary value, with acknowledging the different formats (base-32 vs base 10).

    The simple SQL code above in the article should explain the sequence of operations…

  4. Narayana Rao says:

    Tanel
    on a 3 node rac (11.1.0), on node3, sql_id ‘X’ chooses good plan ‘A’, but on the node 2 & 3, the same sql_id ‘X’ chooses bad plan ‘Y’,

    Question : what could be the reason,
    can we drop the bad plan_hash_value , ? /

    Thanks
    NR

    • Farah says:

      This post was very helpful. I do not see a follow-up on Plan being different on 1 node versus the other two, question posted by NR.

      Hi Tanel: Did you already answer this one? Sorry I do not see it posted here.

      Thanks
      Farah

  5. ershad says:

    HI Tanel,

    the question may not be relevant to the post. Try to see if you can answer.
    It is possible that a SQL_ID can have multiple plan_hash_value and thus multiple execution plan. We will get the timetamp when the new plan_hash_value is generated from dba_hist_sql_plan. But is there a history to know why the new plan is generated.

    Thanks,
    Ershad

  6. IndraBhushan says:

    Good Note!

  7. Alex Honig says:

    Tanel Poder , it is possible to get my current sql_id?
    example:
    I make a query insert into TABLE values (VALUE); commit;
    And the table has a trigger after insert, in my trigger I want to record the insert statment into my new audit table.
    I know that it is possible enabling extended audit in the db, but I want to do it by trigger. it is possible?

    • Tanel Poder says:

      Alex, one more option would be to query V$SESSION.PREV_SQL_ID as the first thing in the trigger code. It would show your session’s previously executed SQL_ID (as the current one is the select querying V$SESSION right now itself). This might not be 100% reliable though, for reasons like maybe a recursive query execution was needed for some reason while running your SQL and this may mess up the SQL_ID value recorded in V$SESSION.

      If 99.9% reliability is fine, then PREV_SQL_ID should do …

  8. Srini Krovvidi says:

    Hi Tanel,

    How to get SQL_HANDLE from SQL_ID ?

    is there a function or procedure to convert the same ?

    Thanks,
    Srini

    • Tanel Poder says:

      The SQL_HANDLE is just HEX representation of the v$sql.exact_matching_signature value. So if your cursor is still in cache (or you do have corresponding ASH entries), you can go from SQL_ID -> EXACT_MATCHING_SIGNATURE -> SQL_HANDLE.

  9. Stanley Raj says:

    Has the hashing algorithm changed between 10g and 11g ?

  10. Aastha says:

    Can we have same hash value for different sql id

    • Tanel Poder says:

      Yep you can have the same PLAN hash value for different SQL statements (thus different SQL_IDs) as many different SQL statements can have similar execution plans.

      Also, it is possible to have the same SQL text hash value for different SQL_IDs, although it’s a very small chance. It’s because the SQL_IDs are “more unique” – a 8 byte portion of the long hash value, the SQL hash_value is just 4 bytes of the same long hash value.

  11. Vishal says:

    Hi Tanel,

    When I compare hash_value in v$sql to v$latch it doesn’t give result.As you posted ,Oracle takes last 32 bits of the MD5 hash and this will be the hash value (as seen in v$sql.hash_value).
    How do I get the hash value from v$sql corresponding to hash in v$latch?

    regards,Vishal

    • Tanel Poder says:

      The last 32 bits of the SQL text (or library cache object name) MD5 hash value is the V$SQL.HASH_VALUE … the HASH_VALUE in V$SQL is a different thing (it’s just the hash value of the latch name string, not related to SQL text hash value).

      If you want to map which SQL_ID is causing/experiencing some latch waits you should query ASH (with ashtop.sql for example) or V$LATCHHOLDER (see my latchprof/latchprofx.sql script articles)

Leave a Reply

Your email address will not be published. Required fields are marked *