RE: sql_id in ADS trace

Hello Nenad,

> As a result, the stored sql_id doesn't match any more, because the hints are not removed after deactivating the bug fix.

Yes, deactivating via fix_control only works right from the start (so that it is not stored as stripped version).  

> I'm still wandering whether there's a simple function for converting the sql_id encoded in the ADS trace to the conventional format.

I think that it is not a sql_id - the ADS trace identifier is just "misleading". Why? Here is an example: ------------8<-----------
SPD: qosdGetFObjKeyFromStmt: sqlText = SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "TAB1") */ 1 AS C1 FROM "TAB1" "TAB1" WHERE ("TAB1"."N1"=1) AND ("TAB1"."N2"=1) AND ("TAB1"."N3"=1)) innerQuery (objid = 8846687189427653554)

kkoadsComputeSqlid: sql_id=8846687189427653554: newText=SELECT /* DS_SVC */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "TAB1") */ 1 AS C1 FROM "TAB1" "TAB1" WHERE ("TAB1"."N1"=1) AND ("TAB1"."N2"=1) AND ("TAB1"."N3"=1)) innerQuery, startPos = 20, stopPos = 120, newTextLen = 203, sqlLen = 302 ------------8<-----------

As you can see - once it is labeled as objid and once it is labeled as sql_id. It seems like an object identifier (similar to directive id) to me.

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Noveljic Nenad hat am 28. Juni 2018 um 16:21 geschrieben:
>
> 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/

--
http://www.freelists.org/webpage/oracle-l