Which of my sql statements are using dynamic sampling?
February 8, 2013 Leave a comment
From a reply I gave on an OTN forum thread, how to see all queries currently in memory which use dynamic sampling?
Dynamic sampling is an attribute in V$SQL_PLAN.OTHER_XML.
In 11gR1, it says “yes”.
In 11gR2, it gives the level.
Using EXTRACTVALUE which is deprecated in 11.2:
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/@type') = 'dynamic_sampling';
Another approach using one of the prefered alternatives – XMLTABLE:
select p.sql_id, t.val from v$sql_plan p , xmltable('for $i in /other_xml/info where $i/@type eq "dynamic_sampling" return $i' passing xmltype(p.other_xml) columns attr varchar2(50) path '@type', val varchar2(50) path '/') t where p.other_xml is not null;
Warning: I tried a number of approaches with XMLTABLE and whilst they all worked in 188.8.131.52, in 184.108.40.206 they all ran into the error below. It might well just be a local issue but I did not investigate:
ORA-00600: internal error code, arguments: [qctVCO:csform], , , , , , , , , , ,