This content has been marked as final. Show 3 replies
XMLTABLE is a better way to do this as EXTRACTVALUE is deprecated but:
In 11.1 I think the tag might say "yes" rather than giving the level.
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';
If there's no dynamic sampling, then there should be no dynamic sampling tag.
And here is an xmltable version:1 person found this helpful
This works for me in 126.96.36.199 but crashes in 188.8.131.52 with ORA-00600: internal error code, arguments: [qctVCO:csform], , , , , , , , , , , 
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;
Both working as intended . Thanks Dom :).