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.