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.

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

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

  1. Olivier says:

    Hello Tanel, i usually query v$session and v$sql based on sql_hash_value, sql_address // hash_value, address to get the sql statement executed by one session. Any idea why is address used for ? thanks again for all this incredible stuff you provide to all of us ;)

  2. Dion Cho says:

    Genius! Thanks for the info!

  3. Tanel Poder says:

    Hi Olivier,

    The address is useful for rare cases where two different SQL statements hash to the same hash value (so the cursors have same hash but different address in shared pool).

    However, as the sql_address points to a parent cursor (which may have multiple child cursors under it), then as of 10g+ you should also use sql_child_number from v$session and match it to child_number in v$sql to find the actual child cursor executed.

  4. Olivier says:

    Tanel,thanks a lot for this very clear explanation !

  5. Christo Kutrovsky says:

    I wonder what’s the purpose of sql_id if all it represents is hash_value.

    I was under the impression it’s a combination of hash_value and address or some other ‘magical’ formula.

    I find hash value to be as readable as sql_id.

  6. Tanel Poder says:

    Well the address can’t be part of a SQL lookup mechanism as the whole point of the library cache hash table is that you can look up a cursor using hash value fast *when you don’t know its address*

    But yep, SQL_ID is just the lowest 64 bits of MD5 hash of the object name. Hash value is 32 lowest bits. The MD5 hash value is stored in X$KGLOB.KGLNAHSV as well so its possible to compare them.

    I guess SQL_ID looks more human readable to people who don’t like numbers :)

    I still use hash_value wherever possible but in some views they’ve left only SQL_ID in (like v$sql_shared_cursor for example).

    • I realy liked your blog that sql_id represents hash_value, big eye opener. Thanks!
      As to your statement that it is possible to compare the MD5 hash value in X$KGLOB . I tried to match in vain any sql_id from v$sql or dba_hist_sqlstat with select KGLNAHSV from X$KGLOB where KGLNAHSV like ‘%&sql_id%’. No rows selected. This was on 11.2.0.1 . I and on 11.2.0.3 as well. So the compare does not look so straigh forward.

      • Tanel Poder says:

        You’re looking into the wrong column. KGLOBT03 is where the 8-byte portion of the hash value (SQL_ID) is kept. The KGLNAHSV is where the full 16-byte MD5 hash value is kept. They are stored in different encoding – SQL_ID is base32 and KGLNAHSV is base16 (hex) encoding. So you would need to convert between those encodings using the method I described.

  7. I believe the sql_id is needed because it is “more unique” than the (old) hash value mechanism because it has more bits. This becomes important when you want to keep the contents of v$sql et. al. in the AWR for weeks or months. You don’t want to risk different statements having the same identifying value.

    On the other hand there’s a lot of code that uses a 32-bit hash value (both internal code and customer code) so it’s necessary to make a 32 bit hash value visible still for backwards compatibility.

    Then you have the problem that you’ve got to be able to find the statement efficiently by sql_id and hash_value – hence the need to use the sql_id to generate the hash_value and relegate the previous version of the hash value to the “old_hash_value”, again for backwards compatibility (e.g. with the 10g version of sprepsql.sql).

  8. Tanel Poder says:

    I’m somewhat sceptical about the need to use 8 bytes instead of 4 bytes for storing the SQL statement hash. I have never seen a hash collision of sql statements with different texts (chance of 1 in 4 billion).

    I’m sure these collisions do happen, especially with statements which use literal values instead of bind variables. But storing execution stats by SQL hash value or SQL ID for such statements doesnt make sense anyway.

    Having 8 bytes in SQLID doesn’t hurt, but I don’t think it solves any real problems either.

  9. Yong Huang says:

    Tanel,

    Thanks for your finding. A few months ago I asked Oracle in an SR unrelated to this if they can provide a conversion function between SQL ID and hash value. The analyst said he’ll file an enhancement request, and that’s the end of it. I’m glad you figured it out.

    BTW, on one Oracle forum I remember asking others to show hash_value-collided SQLs. A long time passed. Nobody provided a case.

  10. Prashant says:

    Tanel,

    In 11g, you can do this:

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

    HASH_VALUE
    ———-
    3743806002

  11. Tanel Poder says:

    Thanks Prashant, I didn’t know that!

    But of course my script is much cooler ;-)

  12. Tanel Poder says:

    …looks like it’s there in 10gR2 as well. I don’t have 10gR1 handy to check if it’s there as well

  13. Prashant says:

    Yes, Indeed!

  14. Tanel Poder says:

    I guess it’s undocumented in 10gR2 then :) when I describe dbms_utility on 10.2.0.1 and 10.2.0.3, it’s there.

  15. Yong Huang says:

    Thanks for Prashant’s finding. I have a 10gR1 database (10.1.0.5.0). The function is there. It’s not in 9.2.0.8.

  16. Eagle Fan says:

    cool!

    Yong:

    9.2.0.8 doesn’t have sql_id :)

  17. Deepak Sharma says:

    We use an application that displays HASH_VALUE of the SQLs. I want to convert that to SQL_ID instead (reverse of what we discuss here), so that I can take that SQL_ID and dig into AWR tables. Is this possible?

  18. Tanel Poder says:

    As hash_value has 32 bits but SQL_ID has 64 bits in it, then its possible to calculate only the last 6 characters of SQL_ID. Then it’d be possible to use WHERE sql_id LIKE ‘% ‘ to look up stuff from AWR repository.

    If I get time I’ll write such a script but I’m not sure when I have time. The principle is the same as in my i2h script so you might want to give it a try yourself :)

  19. David says:

    Tanel,

    Recently I have seen perforamnce of a query changed after running dbms_stats for underneath table. I suspect the plan value has changed.Is there any way I can find out what was hash value about 3-4 days back? I see v$sql has a column called old_hash_value. But that doesnt say when old hash value was recorded.

    Any thought will be very helpful.

  20. Petr says:

    Hallo Tanel,

    it is possible to get 9i hash_value (=old_hash_value) from sql_id ?

    Best Regards,
    Petr

  21. Tanel Poder says:

    No probably not as the 9i style hash value is calculated by different algorithm (kgghash) than the 10g+ hashes (MD5 hash)

  22. Sergei says:

    Tanel,
    How to convert “select * from dual” in your example to sql_id=a5ks9fhw2v9s1? I can do md5 on this SQL text, and then covert lower 64-bit into a string, but I get a different value (gspzdvr4r153j). I can say for sure that SQL_ID is a function of SQL text. If I run same SQL on different databases, I always get same SQL_ID.

  23. The reason for asking conversion SQL text -> SQL_ID (see my previous message) is this: I am executing the SQL statement and want then to check the plan, plan statistics, and sql statistics. All these views need SQL_ID. So I have to query v$session and see PREV_SQL_ID for my session. That works, but it would be more straight forward if I could calculate the SQL_ID. I would like also to check v$mystat and calculate delta but that will cause loosing PREV_SQL_ID. So it will be a big help for me to know how to derive SQL_ID.
    Thanks for sharing your knowledge.

  24. Laxmi Narayan yadav says:

    As per my observation, there may be diffrent hash values for Same SQL_ID . Belwo is the example from 10g database. SQL_ID is same for one query, but hash values changed , because of its plan.If Optimizer changed the paln , it doen’t changed the Sql_id . only hash values are changed.

    SNAP_ID SNAP_TIME SQL_ID PLAN_HASH_VALUE EXECS BUF_PER_EXEC PHY_PER_EXEC cpu_per_exec(Sec) Ela_per_exec(Sec)
    ———- ———— ————- ————— ———- ———— ———— —————– —————–
    9890 04-JAN 01:00 aws7vsqbgsv2f 292054465 1 19152 17162 1464.9596 2243.18342
    10082 12-JAN 01:00 aws7vsqbgsv2f 499145113 1 17878 17312 1645.00716 2427.27173
    10751 09-FEB 01:00 aws7vsqbgsv2f 499145113 1 17615 17216 2013.09833 2590.3509
    10775 10-FEB 01:00 aws7vsqbgsv2f 499145113 1 17600 16697 1510.09356 2011.26174
    10441 27-JAN 03:00 aws7vsqbgsv2f 633526049 1 30716260 123830 195.469094 367.046022
    10463 28-JAN 01:00 aws7vsqbgsv2f 633526049 1 47823212 56314 311.996498 421.198012
    9387 14-DEC 02:00 aws7vsqbgsv2f 1314878629 1 13830993 37637 164.261147 290.000352
    9409 15-DEC 00:00 aws7vsqbgsv2f 1314878629 1 1697269 66482 38.687391 174.137499
    9434 16-DEC 01:00 aws7vsqbgsv2f 1314878629 1 3051897 96378 63.465582 374.238581
    10010 09-JAN 01:00 aws7vsqbgsv2f 1539013174 1 18092 17529 1826.53375 2587.146
    9558 21-DEC 05:00 aws7vsqbgsv2f 1649443435 1 13547198 160290 165.933706 474.570667
    9579 22-DEC 02:00 aws7vsqbgsv2f 1649443435 1 14497174 111498 179.617514 382.41711
    9603 23-DEC 02:00 aws7vsqbgsv2f 1649443435 1 1177302 60633 33.820339 207.410881
    10438 27-JAN 00:00 aws7vsqbgsv2f 2244458875 1 19527 5451 1340.62538 1838.67007
    9555 21-DEC 02:00 aws7vsqbgsv2f 2427486390 1 17448 17154 1404.03808 2001.91158
    9601 23-DEC 00:00 aws7vsqbgsv2f 2427486390 1 17554 17077 572.529958 842.412332
    9794 31-DEC 01:00 aws7vsqbgsv2f 2427486390 1 17800 16604 2294.33382 3372.16529
    9482 18-DEC 01:00 aws7vsqbgsv2f 2504885205 1 17916 17417 1599.84238 2552.85456
    9487 18-DEC 06:00 aws7vsqbgsv2f 2504885205 1 17637 17376 2079.16662 3084.59003
    9887 03-JAN 22:00 aws7vsqbgsv2f 2624263197 1 17367 15937 384.032406 614.503072
    9050 30-NOV 00:00 aws7vsqbgsv2f 2992036255 1 21002 12194 1180.91106 1751.50631
    9002 28-NOV 00:00 aws7vsqbgsv2f 3079460420 1 3203260 20429 52.329462 126.545861
    9057 30-NOV 07:00 aws7vsqbgsv2f 3079460420 1 13675184 105298 166.269288 377.483123
    9075 01-DEC 01:00 aws7vsqbgsv2f 3079460420 1 4345291 15824 63.794956 123.178498

  25. Tanel Poder says:

    @Laxmi Narayan yadav
    Hi Laxmi – yes the PLAN_hash_value, which is the hash value of the execution plan, will change when the plan changes. But there’s also the SQL_HASH_VALUE (which is different from the PLAN_hash_value) and this is the same as SQL_ID – its a hash of the SQL TEXT (not plan), but only shorter (and less fancier) than SQL_ID as I wrote in my article.

  26. David says:

    @Tanel Poder
    Thank you for this. Even though Oracle provide a function, this has enabled me to work out how to reverse it. I wanted to be able to try and find part of the SQL_ID from the hash value. I can get the last 5 or 6 characters, and that is good enough. I often use OraSRP to profile Trace files, that reveals hash values, and sometimes it is useful to look for the SQL_ID in either the ASH data or the AWR repository.

    CREATE OR REPLACE FUNCTION h2i (p_hash_value NUMBER) RETURN VARCHAR2 IS
    l_output VARCHAR2(8) := ”;
    BEGIN
    FOR i IN (
    select substr(‘0123456789abcdfghjkmnpqrstuvwxyz’, 1+floor(mod(p_hash_value/(POWER(32,LEVEL-1)),32)),1) sqlidchar
    FROM dual
    CONNECT BY LEVEL <= LN(p_hash_value)/LN(32)
    ORDER BY LEVEL DESC
    ) LOOP
    l_output := l_output || i.sqlidchar;
    END LOOP;
    RETURN l_output;
    END;
    /

  27. Tanel Poder says:

    Hi David,

    Cool Stuff! I’ve also written something similar – for searching stuff from AWR by partial SQL_ID…

  28. Rakesh says:

    Very interesting stuff. Thanks Tanel.

    Could you please add some more information on Parent/Child Cursor? Also how long cursor information in retained in v$sql.

    Thank You

  29. Patrick says:

    @Prashant
    actually from 10g onwards
    rgds
    patrick boulay

    ******************************************************************
    SQL*Plus: Release 10.2.0.4.0 – Production on Thu Apr 8 12:28:15 2010

    Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
    With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options

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

    HASH_VALUE
    ———-
    3823597405

  30. SKY says:

    hello,

    I m very happy with these notes. I have also a question!
    i want to capture sql statement into table through trigger…

  31. Tanel Poder says:

    @SKY
    You need to use DBMS_FGA / fine grained auditing if you want to capture all SQL IDs of executed SQL. It’ll impact your db performance though

  32. ershad says:

    HI,
    Is it possible to have same hash value for two different sql id? Check below:

    prod> select plan_hash_value from v$sqlarea where SQL_ID =’3umh8hsfvwuz0′;

    PLAN_HASH_VALUE
    —————
    3587584899

    prod> select plan_hash_value from v$sqlarea where SQL_ID =’95jqqy0t2t12a';

    PLAN_HASH_VALUE
    —————
    3587584899

    thanks,
    ershad

  33. Tanel Poder says:

    @ershad
    Ershad, you are querying the PLAN_HASH_VALUE (not HASH_VALUE). Plan_hash_value is the hash of the (partial) SQL PLAN representation, not hash of the SQL TEXT. If multiple different SQL statements have same looking plans, then they’ll end up having the same plan_hash_value.

    But yes, it also is possible to have 2 different SQL IDs which would have the same SQL hash value (as SQL hash value is a subset of SQL ID) but it’s unlikely to happen.

  34. DanyC says:

    Tanel,

    You ARE a STAR without any doubt!! although i can’t afford to go attend to every virtual class, i’ll start saving some money to join them.

    Many thanks for evertyhing you do!

    Dani

  35. prakash says:

    this post is ver useful i have learn a lot from this post.thanx

  36. Manish says:

    Thats great. thank you so much

  37. Nasir says:

    Hi,
    I am trying to get the same sql_ID which is coming from application,
    i am getting the sql_text from v$sqlarea , the sql contains some binds , so before running the sql, i am settings those binds to the value which i need and running the query, however when i check the sql_id it is a different SQL_ID, how do i ensure that when i run the query i get the same exact sql_id , i am taking care so that even a single space is not coming , but still not able to replicate the sql_ID,

    any suggestions/comments ?

    thanks,

  38. Greg Wang says:

    hello,
    ‘Plan baseline’ is introduced in Oracle 11g SQL plan mgmt feature. I wonder how do we relate the ‘signature’ , ‘sql_handle’ , or ‘plan_name’ column in the dba_sql_plan_baselines view to the SQL_ID, HASH_VALUE, or PLAN_HASH_VALUE in v$sql.

    thanks!

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

  40. 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?

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

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

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

  44. IndraBhushan says:

    Good Note!

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

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

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>