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.

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

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

74 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. Pingback: SQL Plan Management | IT World

  6. Pingback: V$SQL.IS_OBSOLETE « Timur Akhmadeev's blog

  7. Pingback: 人生就是如此 » sql_id VS hash_value

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

  9. IndraBhushan says:

    Good Note!

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

  11. Pingback: Oracle如何根据SQL_TEXT生成SQL_ID - 一个故事@MySQL DBA

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

  13. Pingback: Function to compute SQL_ID out of SQL_TEXT | Oracle SQL Tuning Tools and Tips

  14. Pingback: Oracle如何根据SQL_TEXT生成SQL_ID | Multiprocess

  15. Stanley Raj says:

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

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>