Re: matching sql_ids to the sql_ids of the recursive dynamic sampling

just a few ideas:
1) identify which SQLs use dynamic sampling (though I don't know what are the effects of running such a SQL on an already busy system) https://orastory.wordpress.com/2013/02/08/which-of-my-sql-statements-are-using-dynamic-sampling/ :
select p.sql_id, extractvalue(h.column_value,'/info') lvl from v$sql_plan p
, table(xmlsequence(extract(xmltype(p.other_xml),'/other_xml/info'))) h where p.other_xml is not null
and extractvalue(h.column_value,'/info/_at_type') = 'dynamic_sampling';

2) if you know which top SQL IDs to monitor, maybe get only a 10046 trace (instead of a larger 10053 trace) of the problematic SQL-s using the below commands on a session/system level (+ a hard parse): alter session set events 'sql_trace [sql:*cau55hbhz62k8*]'; *execute sql (with hard parse*)
alter session set events 'sql_trace [sql:*cau55hbhz62k8*] off';

That way you would get the recursive dynamic sampling SQL (with dep=n+1) and immediately after that the top level SQL (with dep=n) in the trace file.

3) if you don't want to hard parse the statements again, you could use the DBMS_SQLDIAG.DUMP_TRACE procedure (which triggers a hard parse of the statement) to get a 10053 trace which also contains the dynamic sampling SQL if it was used, e.g.
http://structureddata.org/2011/08/18/creating-optimizer-trace-files/: begin

  dbms_sqldiag.dump_trace(p_sql_id=>'6yf5xywktqsa7',
                          p_child_number=>0,
                          p_component=>'Compiler',
                          p_file_id=>'MY_TRACE_DUMP');

4) another possibility would be to generate a 10053 trace for a specific SQL upon its hard parse, e.g. using the blow SQL at the session/system level:
alter session set events 'trace[rdbms.SQL_Optimizer.*][sql:4ytxvn22dbyry]'; *execute sql (with hard parse*)
alter session set events 'trace [SQL_Optimizer.*] off';

It would be nice to know if Oracle tracks somewhere in the dynamic performance views the relationship between the SQL which triggered the dynamic sampling and the SQL which triggered it (but I can't imagine why it would do that).

Regards,
Jure Bratina

On Wed, Aug 20, 2014 at 4:15 PM, Mathias Zarick <Mathias.Zarick_at_trivadis.com
> wrote:

> Hi List,
>
> we need to troubleshoot a problem around excessive dynamic sampling and
> library cache locks at my client.
> Probably we are facing a bug 14542720.
> For the moment I’m looking for a way of how to match the sql_id’s that
> have used dynamic sampling during parse
> to the sql_ids of the dynamic sampling itself (those queries with the
> SELECT /* OPT_DYN_SAMP */ …)
>
> any idea?
> TIA Mathias
>
>

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