This discussion is archived
5 Replies Latest reply: Feb 8, 2013 4:51 AM by Dom Brooks RSS

dynamic sampling used for this statement (level=2) - where in system view ?

698658 Newbie
Currently Being Moderated
Hi,

When I did
select * from table(dbms_xplan.display_cursor('gc8n8s12yfnhs'));

there is info
Note
-----
   - dynamic sampling used for this statement (level=2)
What I want is info for every statement in shared pool if dynamic sampling was used during plan creation .
So, maybe there is some kind of view/column select * from SOMETHING where dynamic_sampling = 'Y'; :)
DB is 11.2.0.3.5 .
Regards
GregG
  • 1. Re: dynamic sampling used for this statement (level=2) - where in system view ?
    RobertGeier Oracle ACE
    Currently Being Moderated
    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.
  • 2. Re: dynamic sampling used for this statement (level=2) - where in system view ?
    P.Forstmann Guru
    Currently Being Moderated
    Yes but only in XML format in V$SQL_PLAN.OTHER_XML:

    >
    OTHER_XML      CLOB      

    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
  • 3. Re: dynamic sampling used for this statement (level=2) - where in system view ?
    698658 Newbie
    Currently Being Moderated
    Thanks, now I need to extract that from this sql
    select other_xml from v$sql_plan where sql_id=’2y32qkr40b82a’ and other_xml is not null
    
    <other_xml><info type="db_version">11.2.0.3</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('11.2.0.3')]]></hint>
    <hint><![CDATA[DB_VERSION('11.2.0.3')]]></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>
    But thats for XML forum question :).
    Regards
    GregG
  • 4. Re: dynamic sampling used for this statement (level=2) - where in system view ?
    karan Pro
    Currently Being Moderated
    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.htm
  • 5. Re: dynamic sampling used for this statement (level=2) - where in system view ?
    Dom Brooks Guru
    Currently Being Moderated
    XMLTABLE is a better way to do this as EXTRACTVALUE is deprecated but:
    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';
    In 11.1 I think the tag might say "yes" rather than giving the level.
    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 11.2.0.3 but crashes in 11.1.0.7 with ORA-00600: internal error code, arguments: [qctVCO:csform], [0], [0], [0], [0], [112], [2], [224], [], [], [], []
    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;
    Edited by: Dom Brooks on Feb 8, 2013 12:48 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points