Tuesday, December 10, 2013

Getting the SQL_ID for a statement



A while back while I was teaching a class in Dallas, I was chatting with Carlos Sierra about the fact that I was disappointed there wasn’t a function in Oracle to return a SQL_ID for a statement.  Yes, I can get it AFTER I run a statement, but that is kind of closing the door after the horse has escaped. 

I wanted to know the statements SQL_ ID before I ran it so I could easily monitor it.  Carlos wrote up a nifty little routine to do it and you can find it here.
Apparently I was just a little ahead of the curve.  Now in 12c there is a new package called DBMS_SQL_TRANSLATOR.   In it are two routines of interest.

FUNCTION SQL_HASH RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN
FUNCTION SQL_ID RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TEXT
                       CLOB                    IN

These do pretty much what they say, the return the SQL_ID and the HASH_VALUE for a given statement.  Just a little test to show this:

SQL> DECLARE
  2    sqltext CLOB;
  3    sqlid   VARCHAR2(13);
  4    sqlhash number;
  5  BEGIN
  6    sqltext :=  'SELECT * FROM DUAL';
  7    sqlid   :=  DBMS_SQL_TRANSLATOR.SQL_ID (sqltext);
  8    sqlhash :=  DBMS_SQL_TRANSLATOR.SQL_HASH (sqltext);
  9    dbms_output.put_line ('SQL ID is '||sqlid);
 10    dbms_output.put_line ('SQL HASH is '||to_char(sqlhash));
 11  END;
 12  /
SQL ID is 9g6pyx7qz035v
SQL HASH is 3991932091
SQL>
SQL> SELECT * FROM DUAL;

D
-
X

SQL>
SQL> select SQL_ID, HASH_VALUE from v$sql where sql_text like 'SELECT * FROM DUAL';
SQL_ID             HASH_VALUE
------------- ---------------
9g6pyx7qz035v      3991932091

And I want to say a big thanks to Gary Propeck for pointing these out to me! 

2 comments:

  1. It seems that whitespace affects the SQL_ID that is returned. I didn't think that is how Oracle generates the actual SQL_ID.

    SQL> select dbms_sql_translator.sql_id('select * from dual') as sql_id_1
    , dbms_sql_translator.sql_id('select * from dual') as sql_id_2
    from dual;

    SQL_ID_1 SQL_ID_2
    a5ks9fhw2v9s1 6uqa615jqc33y

    ReplyDelete
  2. Yes you are correct. Basically Oracle takes each character and multiplies the ASCII values together then runs this number thru a hash algorithm to generate the value we see. Spaces and Tabs are characters like any other, so use them within a line makes a difference.

    ReplyDelete