5 Replies Latest reply: Feb 8, 2013 6:51 AM by Dom Brooks RSS

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

    698658
      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 ?
          Robert Geier
          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
            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
              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
                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
                  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