This content has been marked as final. Show 5 replies
You should be able to get what you want selecting from v$sql_plan ? I am not logged in to a DB at the moment, so can't test it.1 person found this helpful
Yes but only in XML format in V$SQL_PLAN.OTHER_XML:1 person found this helpful
Provides extra information specific to an execution step of the execution plan. The content of this column is structured using XML since multiple pieces of information can be stored there. This includes:
Name of the schema against which the query was parsed
Release number of the Oracle Database that produced the explain plan
Hash value associated with the execution plan
Name (if any) of the outline or the SQL profile used to build the execution plan
Indication of whether or not dynamic sampling was used to produce the plan
The outline data, a set of optimizer hints that can be used to regenerate the same plan
Thanks, now I need to extract that from this sql
But thats for XML forum question :).
select other_xml from v$sql_plan where sql_id=’2y32qkr40b82a’ and other_xml is not null <other_xml><info type="db_version">184.108.40.206</info> <info type="parse_schema"><![CDATA["LIU"]]></info><info type="dynamic_sampling">2</info> <info type="plan_hash">3882242470</info><info type="plan_hash_2">281948136</info> <peeked_binds><bind nam=":N1" pos="1" dty="2" pre="0" scl="0" mxl="22">c30d51</bind> <bind nam=":N2" pos="2" dty="2" pre="0" scl="0" mxl="22">c30d51</bind> <bind nam=":N3" pos="3" dty="2" pre="0" scl="0" mxl="22">c30d51</bind></peeked_binds> <outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint> <hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('220.127.116.11')]]></hint> <hint><![CDATA[DB_VERSION('18.104.22.168')]]></hint> <hint><![CDATA[FIRST_ROWS(100)]]></hint> <hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint> <hint><![CDATA[FULL(@"SEL$1" "LIU"@"SEL$1")]]></hint> </outline_data></other_xml>
Yes for sure as above mentioned , please visit and read about the CLOB column OTHER_XML http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2120.htm1 person found this helpful
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 an xmltable version - took longer as not so familiar.
This works for me in 22.214.171.124 but crashes in 126.96.36.199 with ORA-00600: internal error code, arguments: [qctVCO:csform], , , , , , , , , , , 
Edited by: Dom Brooks on Feb 8, 2013 12:48 PM
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;