Re: sql_id in ADS trace

Hello Nenad,

> How can I convert the sql_id from the RDBMS.ADS into the conventional format?

Short version: Just remove the hints in the external query block (from SQL trace) and then you got the stored SQL_ID or set fix_control #22760704 to 0 to get rid of this mix-up.

Long version: Mauro's blog post - https://mauro-pagano.com/2016/11/28/something-new-about-sql-plan-directives-and-12-2/

Best Regards
Stefan Koehler

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

> Noveljic Nenad <nenad.noveljic_at_vontobel.com> 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/

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