Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Function to compute SQL_ID out of SQL_TEXT

with 20 comments

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

20 Responses

Subscribe to comments with RSS.

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

    Jared

    September 12, 2013 at 6:16 pm

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

    • 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

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

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

  5. Cool. You might want to add Q-quote to allow sql text with embedded quotes. :)

    Kerry Osborne

    September 13, 2013 at 10:28 am

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

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

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,607 other followers

%d bloggers like this: