Re: Plan Directives used for Execution Plan

Hi Martin, If you can EXPLAIN PLAN the query you will get the list of directive ID used with dbms_xplan.display with +metrics in the format. Cheers, Franck.
Le ven. 7 oct. 2016 à 00:30, Stefan Koehler <contact_at_soocs.de> a écrit :

> Hey Martin,
>
> > Is there a repeatable way to find out a) what one directive does in
> detail? b) which one was used for this very child cursor's xplan? (most
> > important part of my question!)
>
> Yes and Yes, but only as long as this SQL is still in cursor cache. Please
> check out my blog post for the details: http://tinyurl.com/pc7oanp
>
> Please be aware that "dbms_sqldiag.dump_trace" has some (general) issues
> as well, but this is the best we can get so far.
>
> Best Regards
> Stefan Koehler
>
> Freelance Oracle performance consultant and researcher
> Homepage: http://www.soocs.de
> Twitter: _at_OracleSK
>
> > Martin Klier - Performing Databases GmbH <martin.klier_at_performing-db.com>
> hat am 7. Oktober 2016 um 00:08 geschrieben:
> >
> > Hi Listers,
> >
> > when extracting an execution plan from the cache with
> > SELECT * FROM
> table(DBMS_XPLAN.DISPLAY_CURSOR('xyz',null,'COST,IOSTATS,LAST,ADVANCED,ADAPTIVE'))
> >
> > I can see that we are using Plan Directives:
> >
> > Note
> > -----
> > - dynamic statistics used: dynamic sampling (level=2)
> > - statistics feedback used for this statement
> > - this is an adaptive plan (rows marked '-' are inactive)
> > - 9 Sql Plan Directives used for this statement
> >
> >
> > But for various reasons, the seven widely used objects joined by my
> select have more than 500 directives each. Is there a repeatable way to find
> > out
> > a) what one directive does in detail?
> > b) which one was used for this very child cursor's xplan? (most
> important part of my question!)
> >
> > The "joined force" of dba_sql_plan_directives and
> dba_sql_plan_dir_objects isn't very helpful for that... at least not for my
> weary eyes tonight.
> > Ah, it would be great to avoid tracing for obvious reasons, but if I
> have to, I will.
> >
> > Thank you very much in advance!
> > --
> > Martin Klier | Performing Databases GmbH
> > Managing Partner | Senior DB Consultant
> > Oracle ACE
> >
> > martin.klier_at_performing-db.com | http://www.performing-databases.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l