Function to compute SQL_ID out of SQL_TEXT

Ric Van Dyke from Hotsos asked me yesterday if I knew of a PL/SQL function that inputs a SQL_TEXT and outputs a SQL_ID. I kind of remembered Tanel Poder had written something on this regard. And I also recalled seen an API under DBMS_UTILITY that would provide the SQL_ID given a SQL_TEXT. When I researched on both, I was able to get from Tanel a function to generate the SQL HASH_VALUE out of given SQL_ID, and from DBMS_UTILITY another API to get the SQL HASH_VALUE out of a SQL_TEXT provided as a VARCHAR2. But I could not find one to compute SQL_ID given SQL_TEXT written on PL/SQL. But Slavik Markovich gave me the insight that I needed to build a PL/SQL function that computes SQL_ID out of SQL Text. Slavik’s function is written on Python.

So, thanks to Ric for his challenging question/request, and to Tanel and Slavik for their valuable insight, I was encouraged to develop this function. Feel free to use it! I have seen the same question been asked a few times in the past, so at least I know we have such a function now!

GRANT EXECUTE ON SYS.DBMS_CRYPTO TO csierra;
CREATE OR REPLACE FUNCTION compute_sql_id (sql_text IN CLOB)
RETURN VARCHAR2 IS
 BASE_32 CONSTANT VARCHAR2(32) := '0123456789abcdfghjkmnpqrstuvwxyz';
 l_raw_128 RAW(128);
 l_hex_32 VARCHAR2(32);
 l_low_16 VARCHAR(16);
 l_q3 VARCHAR2(8);
 l_q4 VARCHAR2(8);
 l_low_16_m VARCHAR(16);
 l_number NUMBER;
 l_idx INTEGER;
 l_sql_id VARCHAR2(13);
BEGIN
 l_raw_128 := /* use md5 algorithm on sql_text and produce 128 bit hash */
 SYS.DBMS_CRYPTO.hash(TRIM(CHR(0) FROM sql_text)||CHR(0), SYS.DBMS_CRYPTO.hash_md5);
 l_hex_32 := RAWTOHEX(l_raw_128); /* 32 hex characters */
 l_low_16 := SUBSTR(l_hex_32, 17, 16); /* we only need lower 16 */
 l_q3 := SUBSTR(l_low_16, 1, 8); /* 3rd quarter (8 hex characters) */
 l_q4 := SUBSTR(l_low_16, 9, 8); /* 4th quarter (8 hex characters) */
 /* need to reverse order of each of the 4 pairs of hex characters */
 l_q3 := SUBSTR(l_q3, 7, 2)||SUBSTR(l_q3, 5, 2)||SUBSTR(l_q3, 3, 2)||SUBSTR(l_q3, 1, 2);
 l_q4 := SUBSTR(l_q4, 7, 2)||SUBSTR(l_q4, 5, 2)||SUBSTR(l_q4, 3, 2)||SUBSTR(l_q4, 1, 2);
 /* assembly back lower 16 after reversing order on each quarter */
 l_low_16_m := l_q3||l_q4;
 /* convert to number */
 SELECT TO_NUMBER(l_low_16_m, 'xxxxxxxxxxxxxxxx') INTO l_number FROM DUAL;
 /* 13 pieces base-32 (5 bits each) make 65 bits. we do have 64 bits */
 FOR i IN 1 .. 13
 LOOP
 l_idx := TRUNC(l_number / POWER(32, (13 - i))); /* index on BASE_32 */
 l_sql_id := l_sql_id||SUBSTR(BASE_32, (l_idx + 1), 1); /* stitch 13 characters */
 l_number := l_number - (l_idx * POWER(32, (13 - i))); /* for next piece */
 END LOOP;
 RETURN l_sql_id;
END compute_sql_id;
/
SHOW ERRORS;
-- test
SET SERVEROUT ON;
SELECT compute_sql_id('select ''Slavik'' from dual') FROM DUAL;
-- you should get 29schpgjyfxux

Written by Carlos Sierra

September 12, 2013 at 5:51 pm

Posted in Scripts

33 Responses

Subscribe to comments with RSS.

This is pretty cool Carlos, can’t wait to try this.
Thanks!

Jared

September 12, 2013 at 6:16 pm

Reply
- Cool!
  
  Carlos Sierra
  
  September 12, 2013 at 9:48 pm
  
  Reply
Good post. Christian Antognini also had a means to get the sqlid from a query just executed in your own session:

SELECT sql_id, child_number, sql_text, optimizer_mode, plan_hash_value
FROM v$sql
WHERE sql_id = (SELECT prev_sql_id
FROM v$session
WHERE sid = sys_context(‘userenv’,’sid’));

Jon Adams

September 12, 2013 at 6:35 pm

Reply
- Thanks Jon. Ric’s requirement was getting the SQL_ID before the SQL gets executed. Something to do with monitoring from the beginning and not after the fact.
  
  Carlos Sierra
  
  September 12, 2013 at 9:49 pm
  
  Reply
sokrates@11.2 > select compute_sql_id(q’|select compute_sql_id(‘select user from dual’) sql_id from dual|’) sql_id from dual;

SQL_ID
——————————
3r3qbcmwwcas6

sokrates@11.2 > select compute_sql_id(‘select user from dual’) sql_id from dual;

SQL_ID
——————————
ahc9t761kxw30

sokrates@11.2 > select sql_id from v$sqlarea where sql_text=q’|select compute_sql_id(‘select user from dual’) sql_id from dual|’;

SQL_ID
——————————
3r3qbcmwwcas6

seems to work !

Matthias Rogel

September 13, 2013 at 3:20 am

Reply
- Thanks Matthias for validating!
  
  Carlos Sierra
  
  September 13, 2013 at 7:31 am
  
  Reply
Hi Carlos,
Graham Simpson from PeopleSoft CoE here. I have written a duplicate of this feature and built it into TraceMagic – the *.tracesql analysis tool PeopleSoft Customers and Support use to pick apart PeopleSoft trace files. It was written in MS Visual Studio. I wish I’d known about Slavik Markovich’s work before hand, as I did struggle for days when reverse-engineering the line: sql_text = (sql_text + vbNullChar).
I’m posting the functions source here in case any of your readers might want to write there own little VS (or?) application – to possibly do this stuff in batch? or without a database handy? *shrug* Enjoy!

Keep the good tools coming Carlos!

Private Function getSQL_ID(sql_text As String) As String
Dim hashlib As New Security.Cryptography.MD5CryptoServiceProvider
Dim arrData() As Byte
Dim byteHash() As Byte
Dim sqlid As String = “”
Const alphabet As String = “0123456789abcdfghjkmnpqrstuvwxyz”
Dim MSB As UInt64
Dim LSB As UInt64
Dim sqln As UInt64
Dim arr3() As UInt32 = {0, 0, 0, 0}
Dim arr4() As UInt32 = {0, 0, 0, 0}
sql_text = Trim(sql_text)
sql_text = (sql_text + vbNullChar)
arrData = System.Text.Encoding.ASCII.GetBytes(sql_text)
byteHash = hashlib.ComputeHash(arrData)
Buffer.BlockCopy(byteHash, 8, arr3, 0, 4)
Buffer.BlockCopy(byteHash, 12, arr4, 0, 4)
MSB = (((arr3(0) Or (arr3(1) << 8)) Or (arr3(2) << &H10)) Or (arr3(3) << &H18))
LSB = (((arr4(0) Or (arr4(1) << 8)) Or (arr4(2) << &H10)) Or (arr4(3) << &H18))
sqln = (MSB <> (iCount * 5)) Mod 32)) + sqlid
Next iCount
Return sqlid
End Function

Graham Simpson

September 13, 2013 at 10:02 am

Reply
- Graham,
  Thanks a lot for sharing! I was not aware of your function on VS. Yes this function was long overdue…
  
  Carlos Sierra
  
  September 13, 2013 at 10:06 am
  
  Reply
- Code did not post properly
  sqln = (MSB <> (iCount * 5)) Mod 32)) + sqlid
  Next iCount
  Return sqlid
  
  Graham Simpson
  
  September 13, 2013 at 10:06 am
  
  Reply
  - still wrong, can I send it to you? Sorry for the trouble.
    
    Graham Simpson
    
    September 13, 2013 at 10:07 am
  - Yes, please send as text to csierra@enkitec.com. Thx!
    
    Carlos Sierra
    
    September 13, 2013 at 12:02 pm
  - Thanks!
    
    Carlos Sierra
    
    September 13, 2013 at 12:01 pm
  - Could you send the VB/C# code for getsql_id to me too?
    
    Thanks!
    
    Pieter
    
    August 12, 2015 at 8:02 am
  - Update: as I didn’t quite understand the piece of code that was posted already I implemented it myself… so no need to send it anymore. Thanks anyway…
    
    Pieter
    
    August 12, 2015 at 2:00 pm
Cool. You might want to add Q-quote to allow sql text with embedded quotes. 🙂

Kerry Osborne

September 13, 2013 at 10:28 am

Reply
- Good idea!
  
  Carlos Sierra
  
  September 13, 2013 at 12:02 pm
  
  Reply
I wrote a similar function a couple years ago along with the corresponding text to hash and id to hash functions. I posted all three as comments to the same Slavik page linked above. It didn’t occur to me to go big with clob support instead of just varchar2. I guess it’s time to update! Thanks

Sean D. Stuber

October 29, 2013 at 1:33 pm

Reply
- No problem!
  
  Carlos Sierra
  
  October 29, 2013 at 7:37 pm
  
  Reply
Hey Carlos! Looks like some one in Redwood shores was listening to our conversation. Now in 12c there are two functions in DBMS_SQL_TRANSLATOR, SQL_HASH and SQL_ID.

Ric

December 10, 2013 at 10:41 am

Reply
- Ric,
  That is good to know!
  Thanks — Carlos
  
  Carlos Sierra
  
  December 10, 2013 at 12:21 pm
  
  Reply
Hi Carlos
I have a situation where my sql text is very long. When using your function I get this error.
ORA-01704: string literal too long
01704. 00000 – “string literal too long”
*Cause: The string literal is longer than 4000 characters.

Could you suggest a solution. Thanks in advance!
Atin

atin

June 23, 2014 at 8:33 am

Reply
- send me an email with the details of how you are calling my function. you need to use CLOBs. carlos.sierra.usa@gmail.com
  
  Carlos Sierra
  
  June 23, 2014 at 9:48 am
  
  Reply
[…] are calculated by database engine, briefly and in detail; also implementations in Python and PL/SQL to compute […]

Computing Oracle SQL_ID and HASH_VALUE – Beautiful Data

September 23, 2017 at 9:02 am

Reply
Hi Carlos,
This is really useful and have been using it for some time now.
I would like to check with you if this holds true with newer releases of Oracle DB.

Thank You!

Prashant

Prashant

December 14, 2017 at 12:43 am

Reply
- I haven’t tested it but I do not see why not.
  
  Carlos Sierra
  
  December 14, 2017 at 8:17 am
  
  Reply
[…] Baseline fleet cleanup, I wanted to get the SQL_ID give a SQL Plan Baseline. Long ago I wrote a PL/SQL function that inputs some SQL Text and outputs its SQL_ID . Then, I could use such function, passing the SQL Text from the SQL Plan Baseline (this is on […]

Query to get SQL_ID from DBA_SQL_PLAN_BASELINES | Carlos Sierra's Tools and Tips

January 16, 2019 at 4:52 pm

Reply
Can you please share the python version of this as the Slavik’s function is written on Python is not accessible now.

Tony

January 11, 2021 at 5:41 am

Reply
- I do not have a python version
  
  Carlos Sierra
  
  January 11, 2021 at 2:01 pm
  
  Reply
  - I didn’t find Slaviks version as well. I wrote my own very simple one
    
    import hashlib
    import struct
    
    alphabet = ‘0 1 2 3 4 5 6 7 8 9 a b c d f g h j k m n p q r s t u v w x y z’.split()
    
    def text_to_sqlid(txt): # str or array of str
    if not isinstance(txt, str):
    txt = ”.join(txt)
    md5 = hashlib.md5((txt + chr(0)).encode()).digest()
    lw = struct.unpack(‘<8x2L', md5) # md5 is little endian and skip 2×4=8 bytes
    qw = lw[0] <> i & 0b11_111] for i in range(60, -1, -5)])
    
    Enjoy
    
    Sergey
    
    January 13, 2021 at 7:45 am
  - Thx
    
    Carlos Sierra
    
    January 14, 2021 at 10:29 am
  - Sorry, code was changed during pasting it. How can I attach code without disrupting it?
    
    Sergey
    
    January 13, 2021 at 7:49 am
  - Hi Sergey, Thanks for the reply. The script isn’t working as it’s changed when you pasted it. Can you please mail me the script to Tony_stark_4000@yahoo.com. Thanks in advance !
    
    Tony
    
    January 18, 2021 at 1:24 am
Hi Sergey, Thanks for the reply. The script isn’t working as it’s changed when you pasted it. Can you please mail me the script to Tony_stark_4000@yahoo.com. Thanks in advance !

Tony

January 18, 2021 at 1:19 am

Reply