Converting HASH_VALUE to SQL_ID

The V$SQL.SQL_ID is a 64-bit number in a base32 notation. And the V$SQL.HASH_VALUE stores its least significant 32-bits in the decimal notation, for example:

select hash_value from v$sql where sql_id='7wckkp93ya8a2' ;

HASH_VALUE
----------
1206198594

This means that the most significant 32 bits will inevitably be lost when converting the V$SQL.HASH_VALUE back to the SQL_ID. Nevertheless, at least we can get its bottom 32 bits with the SQL for converting decimal integers to any base:

define b = 32
column base&&b format a13

define d = 1206198594
column base10 format 9999999999999999999

select &&d base10,
    listagg(
        substr(
          case &&b when 32 then 
            '0123456789abcdfghjkmnpqrstuvwxyz'
          else
            '0123456789abcdefghjkmnpqrstuvwxyz'
          end,
          mod(
            trunc(&&d/power(&&b,level-1)),
            &&b
          ) + 1 ,
          1
        ) 
    ) within group (order by level desc) base&&b 
  from dual 
  connect by level <= ceil(log(&&b,&&d+1))
;

              BASE10 BASE32
-------------------- -------------
          1206198594 13ya8a2

Now take a look at the SQL_ID in the ADS trace excerpt below:

kkoadsComputeSqlid: sql_id=9093421488918569282: newText=SELECT /* DS_SVC */  NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "T1#1")  */ 1 AS C1 FROM "T1" "T1#1", "T2" "T2#0" WHERE ("T2#0"."N2"=2) AND ("T1#1"."N1"="T2#0"."N1") AND ("T1#1"."N2"=2) AND ("T1#1"."N3"=2)) innerQuery, startPos = 20, stopPos = 120, newTextLen = 244, sqlLen = 343

At first glance, 9093421488918569282 looks entirely different from the V$SQL.SQL_ID and the V$SQL.HASH_VALUE, 7wckkp93ya8a2 and 1206198594, respectively.

Jonathan Lewis, however, kindly pointed out that the SQL_ID in the ADS trace is nothing else but the V$SQL.SQL_ID in the decimal notation. Consequently, we can use the same method to obtain the V$SQL.SQL_ID:

define d = 9093421488918569282
/

              BASE10 BASE32
-------------------- -------------
 9093421488918569282 7wckkp93ya8a2

References:

• SQL_ID is just a fancy representation of hash value, Tanel Poder
• Something new about SQL Plan Directives and 12.2, Mauro Pagano