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.
Trackbacks
Comments
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 ;)
Genius! Thanks for the info!
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.
Dion, thanks ;)
Tanel,thanks a lot for this very clear explanation !
My pleasure! :)
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.
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 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).
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.
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.
Tanel,
In 11g, you can do this:
SQL> select dbms_utility.SQLID_TO_SQLHASH(‘btxdhy7gkbwjk’) hash_value FROM DUAL;
HASH_VALUE
———-
3743806002
Thanks Prashant, I didn’t know that!
But of course my script is much cooler ;-)
…looks like it’s there in 10gR2 as well. I don’t have 10gR1 handy to check if it’s there as well
Yes, Indeed!
Really? I didn’t find that function in 10.2 doc:
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#i996767
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.
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.
cool!
Yong:
9.2.0.8 doesn’t have sql_id :)
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?
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 :)
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.
Hallo Tanel,
it is possible to get 9i hash_value (=old_hash_value) from sql_id ?
Best Regards,
Petr
No probably not as the 9i style hash value is calculated by different algorithm (kgghash) than the 10g+ hashes (MD5 hash)
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.
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.
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
@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.
@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;
/
Hi David,
Cool Stuff! I’ve also written something similar – for searching stuff from AWR by partial SQL_ID…
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
@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
hello,
I m very happy with these notes. I have also a question!
i want to capture sql statement into table through trigger…
@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
Excellent!