Re: Plan Directives used for Execution Plan

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