{"id":2125,"date":"2018-07-08T16:56:40","date_gmt":"2018-07-08T16:56:40","guid":{"rendered":"http:\/\/nenadnoveljic.com\/blog\/?p=2125"},"modified":"2018-07-08T17:00:01","modified_gmt":"2018-07-08T17:00:01","slug":"converting-hash_value-to-sql_id","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/converting-hash_value-to-sql_id\/","title":{"rendered":"Converting HASH_VALUE to SQL_ID"},"content":{"rendered":"

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:<\/p>\n

select hash_value from v$sql where sql_id='7wckkp93ya8a2<\/span>' ;\r\n\r\nHASH_VALUE\r\n----------\r\n1206198594<\/span><\/code><\/pre>\n
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<\/a>:<\/p>\n
define b = 32\r\ncolumn base&&b format a13\r\n\r\ndefine d = 1206198594<\/span>\r\ncolumn base10 format 9999999999999999999\r\n\r\nselect &&d base10,\r\n    listagg(\r\n        substr(\r\n          case &&b when 32 then \r\n            '0123456789abcdfghjkmnpqrstuvwxyz'\r\n          else\r\n            '0123456789abcdefghjkmnpqrstuvwxyz'\r\n          end,\r\n          mod(\r\n            trunc(&&d\/power(&&b,level-1)),\r\n            &&b\r\n          ) + 1 ,\r\n          1\r\n        ) \r\n    ) within group (order by level desc) base&&b \r\n  from dual \r\n  connect by level <= ceil(log(&&b,&&d+1))\r\n;\r\n\r\n              BASE10 BASE32\r\n-------------------- -------------\r\n          1206198594<\/span> 13ya8a2<\/span><\/code><\/pre>\n
Now take a look at the SQL_ID in the ADS trace excerpt below:<\/p>\n
kkoadsComputeSqlid: sql_id=9093421488918569282<\/span>: 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<\/code><\/pre>\n
At first glance, 9093421488918569282<\/span> looks entirely different from the V$SQL.SQL_ID and the V$SQL.HASH_VALUE, 7wckkp93ya8a2<\/span> and 1206198594<\/span>, respectively. <\/p>\n
Jonathan Lewis, however, kindly pointed out<\/a> 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:<\/p>\n
define d = 9093421488918569282<\/span>\r\n\/\r\n\r\n              BASE10 BASE32\r\n-------------------- -------------\r\n 9093421488918569282<\/span> 7wckkp93ya8a2<\/span><\/code><\/pre>\n
References:<\/p>\n