Which of my sql statements are using dynamic sampling?
February 8, 2013 3 Comments
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 11.2.0.3, in 11.1.0.7 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], [0], [0], [0], [0], [112], [2], [224], [], [], [], []
Pingback: SQL utils using XML | OraStory
Dominic, you can try to avoid some bugs with query like that:
Regards,
Sayan Malakshinov
Pingback: BP and Patch 22652097: set optimizer_adaptive_statistics to FALSE explicitly or it might not work! | DBA survival BLOG