Get the SQL_ID for a SQL statement BEFORE you run it

Posted by

An AskTom customer asked the question recently: “We often use the SQL_ID to dig into the performance views once we have run a statement, but can we know what the SQL_ID for a statement will be in advance?”

Normally this is a question I would not have probably considered “blog worthy” because it is a topic that has already been covered by others. Tanel Poder has discussed the relationship between “modern” SQL_ID and the “legacy” HASH_VALUE (Spoiler alert: They are essentially the same thing) and Nenad Noveljic has provided an example of the base32 method that is utilised to do the conversion between the two.

But there’s two reasons I was inspired to put pen to paper (or more accurately, two fingers to keyboard Smile)

A great strategy

You might be thinking “Why would we need the SQL_ID at all?” unless there was some specific performance tuning requirement. It’s the same question I asked of this customer, and I was very pleased at the response. This customer was aiming to build performance metrics directly into their application. Thus when the application was going to run a business critical query, the plan was to derive the SQL_ID, then run the query in the normal way and then use the SQL_ID to pick up various statistics from V$SQL, V$SQL_PLAN and the like. They would capture this data so they could then do comparisons over time and between environments, as well as preemptively pop up warnings for queries known to take a long time to execute so users could have an expectation of response time. I’m a big fan of this approach – the application taking some responsibility for its own performance, rather than the typical approach of “That’s the DBA’s problem”.

A new, easier way

If you’ve clicked on the posts by Tanel or Nenad and decided that base32, hex codes and appending null terminators to strings all just sounds like too much hard work, you’ll be pleased to know there is a much easier way to get the SQL_ID from SQL statement. You now just need to call DBMS_SQL_TRANSLATOR. Here’s an example of that in action


SQL> set feedback only sql_id
SQL> SELECT * FROM SCOTT.EMP;

14 rows selected.

SQL_ID: 4ay6mhcbhvbf2

SQL> set feedback on
SQL> select dbms_sql_translator.sql_id('SELECT * FROM SCOTT.EMP')
  2  from   dual;

DBMS_SQL_TRANSLATOR.SQL_ID('SELECT*FROMSCOTT.EMP')
--------------------------------------------------------------------
4ay6mhcbhvbf2

1 row selected.

Before I learnt about DBMS_SQL_TRANSLATOR I also covered the topic of SQL_ID and HASH_VALUE in the video below.

8 comments

  1. Hi Connor,

    As soon as I read this post, I was hoping that I could use your technique to get the SQL_ID before running a SQL statement and then use it to determine the Execution Plan, as shown: –

    SELECT *
    FROM TABLE(dbms_xplan.display_cursor(DBMS_SQL_TRANSLATOR.SQL_ID(‘SELECT * FROM SCOTT.EMP’), NULL, ‘ALLSTATS LAST’));
    /

    Unfortunately, it shows a message “SQL_ID: ……… cannot be found”.

    It would be nice to have the ability to determine the Execution Plan before executing the SQL Statement. I know there is Explain Plan but it is highly inaccurate in some cases.

  2. Hi Connor, when using the DBMS_SQL_TRANSLATOR technique to get the SQL_ID before running a SQL statement in Execution Plan — DBMS_XPLAN, it doesn’t work. Any thoughts?

  3. Hi Connor, the above technique doesn’t work when used in DBMS_XPLAN to get the Execution Plan of a given SQL statement without having to execute the SQL statement first. Any thoughts?

      1. Connor, I was trying to do something like this:-

        SELECT *
        FROM TABLE(dbms_xplan.display_cursor(dbms_sql_translator.sql_id(‘SELECT * SCOTT.EMP’), 0))
        ;

  4. I wrote a small script:

    $ ./sql_id.sh “SELECT * FROM SCOTT.EMP”
    sql_text = SELECT * FROM SCOTT.EMP\0
    full_hash_value(16) = 75D51728C16133524578D383170DADC2
    hash_value(10) = 386772418
    sql_id(32) = 4ay6mhcbhvbf2
    sql_id(32) = 4ay6mhcbhvbf2
    sql_id(32) = 4ay6mhcbhvbf2

    $ cat sql_id.sh
    #! /bin/bash
    # calcucate sql_text of full_hash_value(16),hash_value(10),sql_id(32).
    odebug=${ODEBUG:-0}

    sql_text=${1}’\0′
    v1=$(echo -e -n “$sql_text” | md5sum | sed ‘s/ -//’ | xxd -r -p | od -t x4 | sed -n -e ‘s/^0\+ //’ -e ‘s/ //gp’ | tr ‘a-z’ ‘A-Z’)
    v2=${v1:(-16):16}
    v3=${v2:(-8):8}
    # v2=$(echo “obase=16;ibase=16; $v1 % 10000000000000000” | bc| tr -d ‘\\\r\n’)
    # v3=$(echo “obase=10;ibase=16; $v1 % 100000000” | bc| tr -d ‘\\\r\n’)

    if [ $odebug -eq 1 ] ; then
    echo v1=$v1 v2=$v2 v3=$v3
    fi

    echo “sql_text = $sql_text”
    echo “full_hash_value(16) = $v1 ”
    echo “hash_value(10) = $(( 16#$v3 )) ”

    BASE32=($(echo {0..9} {a..z} | tr -d ‘eilo’))
    res=”
    for i in $(echo “obase=32;ibase=16; $v2” | bc| tr -d ‘\\\r\n’)
    do
    res=${res}${BASE32[$(( 10#$i ))]}
    done
    echo “sql_id(32) = $(printf “%13s” $res | tr ‘ ‘ ‘0’)”
    echo “sql_id(32) = $(printf “%013s” $res)”

    res1=$(eval $(echo “obase=32;ibase=16; $v2″ | bc| tr -d ‘\\\r\n’ | awk ‘BEGIN{RS=” +”; printf “echo ” }/./{printf “${BASE32[$(( 10#%02d))]} “, $1}’ ))
    res1=$(tr -d ” ” <<< $res1)
    echo "sql_id(32) = $(printf "%013s" $res1)"

Got some thoughts? Leave a comment

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.