As so often, Tanel Poder has the answer:
https://blog.tanelpoder.com/2009/02/22/sql_id-is-just-a-fancy-representation-of-hash-value/
The "ordinary" hash value is the bottom 4 bytes of a number that can be
generated from the SQL_ID using Tanel's script.
The SQL id in the trace is the bottom 8 bytes (change Tanel's script to use
mod(X,power(2,64)).
select
lower(trim('7wckkp93ya8a2')) sql_id
,
trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',substr(lower(trim('7wckkp93ya8a2')),level,1))-1)
*power(32,length(trim('7wckkp93ya8a2'))-level)),power(2,64))) hash_value
from
dual
connect by
level <= length(trim('7wckkp93ya8a2'))
/
SQL_ID HASH_VALUE
------------- ----------------------------
7wckkp93ya8a2 9,093,421,488,918,569,282
1 row selected.
Reversing the logic of the code is left as an exercise.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Noveljic Nenad <nenad.noveljic@xxxxxxxxxxxx>
Sent: 28 June 2018 15:21:54
To: 'Stefan Koehler'; ORACLE-L (oracle-l@xxxxxxxxxxxxx)
Subject: RE: sql_id in ADS trace
Thank you, Stefan.
=> Just remove the hints in the external query block (from SQL trace) and then
you got the stored SQL_ID
This was helpful. I ran the DS sql and queried the v$sql.sql_id
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 ;
select sql_id from v$sql where sql_text like 'SELECT /* DS_SVC */ NVL(%' ;
SQL_ID
7wckkp93ya8a2
=> set fix_control #22760704 to 0 to get rid of this mix-up
As a result, the stored sql_id doesn't match any more, because the hints are
not removed after deactivating the bug fix.
I'm still wandering whether there's a simple function for converting the sql_id
encoded in the ADS trace to the conventional format.
Best regards,
Nenad
http://nenadnoveljic.com/blog/
-----Original Message-----
From: Stefan Koehler [mailto:contact@xxxxxxxx]
Sent: Donnerstag, 28. Juni 2018 15:52
To: ORACLE-L (oracle-l@xxxxxxxxxxxxx); Noveljic Nenad
Subject: Re: sql_id in ADS trace
Hello Nenad,
How can I convert the sql_id from the RDBMS.ADS into the conventional format?
Noveljic Nenad <nenad.noveljic@xxxxxxxxxxxx> hat am 28. Juni 2018 um 15:42____________________________________________________
geschrieben:
A DYNAMIC_SAMPLING_RESULT sql plan directive got created for a join
cardinality misestimate.
The dynamic sampling sql_id stored in notes is 7wckkp93ya8a2:
NEWNO{(V.T1, num_rows=1001000) - (V.T2, num_rows=1001) -
(SQL_ID:7wckkp93ya8a2, T.CARD=943807[-2 -2])}
The sql_id in the RDBMS.ADS trace is encoded differently:
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
How can I convert the sql_id from the RDBMS.ADS into the conventional format?
Many thanks in advance.
Nenad
http://nenadnoveljic.com/blog/